Ghost upgrade from 4.44.0 to 4.46.0 breaks ghost

Developer help Self-hosting
1

See error below… Message: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’

This happens after tried “ghost upgrade” from 4.44.0 to 4.46.0

Broke two of my Ghost installs not just a one off.

[root@darkice ~]# mysql -V
mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for Linux (x86_64) using readline 5.1

[root@darkice ~]# uname -arn
Linux darkice 3.10.0-1160.62.1.el7.x86_64 #1 SMP Tue Apr 5 16:57:59 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux



[91hockey@darkice ~]$ ghost start
┌──────────────────────────────────────────────────────────────────────────────────┐
│Warning: MySQL 8 will be the required database in the next major release of Ghost.│
│     Make sure your database is up to date to ensure forwards compatibility.      │
└──────────────────────────────────────────────────────────────────────────────────┘

Love open source? We’re hiring Node.js Engineers to work on Ghost full-time.
https://careers.ghost.org/product-engineer-node-js

+ sudo systemctl is-active ghost_91hockey
✔ Checking system Node.js version - found v14.19.1
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
+ sudo systemctl is-active ghost_91hockey
✔ Validating config
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking binary dependencies
✔ Checking systemd unit file
✔ Checking systemd node version - found v14.19.1
+ sudo systemctl start ghost_91hockey
+ sudo systemctl stop ghost_91hockey
✖ Starting Ghost: 91hockey
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table `newsletters` modify  `created_at` datetime null default 'NULL' - Invalid default value for 'created_at'
Suggestion: journalctl -u ghost_91hockey -n 50

Debug Information:
    OS: CentOS Linux, v7
    Node Version: v14.19.1
    Ghost Version: 4.46.0
    Ghost-CLI Version: 1.19.3
    Environment: production
    Command: 'ghost start'

Additional log info available in: /home/91hockey/.ghost/logs/ghost-cli-debug-2022-04-30T02_39_03_539Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.
[91hockey@darkice ~]$

When doing the upgrade, ghost detected this problem and suggested to “rollback”… but the rollback also is broken and I’m stuck with 4.46.0 in broken state

Any idea how to fix?

Thanks so much for any advice

Best Regards,

-Avery

2

Execute show variables like 'sql_mode' ; . What’s the output?

I wonder if this is an incompatibility between MariaDB and Oracle MySQL. See NULL Values - MariaDB Knowledge Base.

3

Hello @mjw

Here is the output:

MariaDB [(none)]> show variables like ‘sql_mode’ ;
±--------------±------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Thx for the help

4

Same error message - using MariaDB under Debian 10.

5

Ghost 4.46 introduced changes to newsletters, which may have introduced an incompatibility with MariaDB (Ghost officially supports MySQL.)

I think there are instances where MariaDB handles date differently. For example, returning NULL, whereas MySQL returns a 0. This is just a hunch. I suggest you take a look at the tables, i.e., take a dump of the database first before making any changes, and look for the table newsletters where created_at is NULL. See if changing to ‘0’ or the current date helps.

If you’ve snapshots on your server, it may be easier to roll back that way.

Once you resolve this, make sure you migrate from MariaDB to MySQL.

6

PLease share output from select * from newsletters;. This is what I see:

mysql> select * from newsletters;
+--------------------------+-----------------+------------------------------------------------------------------+--------------------+--------------+-----------------+--------+------------+---------------------+------------+--------------+------------------+-------------------+---------------------+-----------------+--------------------+--------------------+----------------+------------+-------------+---------------------+------------+------------------+--------------------------------------+
| id                       | name            | description                                                      | slug               | sender_email | sender_reply_to | status | visibility | subscribe_on_signup | sort_order | header_image | show_header_icon | show_header_title | title_font_category | title_alignment | show_feature_image | body_font_category | footer_content | show_badge | sender_name | created_at          | updated_at | show_header_name | uuid                                 |
+--------------------------+-----------------+------------------------------------------------------------------+--------------------+--------------+-----------------+--------+------------+---------------------+------------+--------------+------------------+-------------------+---------------------+-----------------+--------------------+--------------------+----------------+------------+-------------+---------------------+------------+------------------+--------------------------------------+
| xxxxxxxxxxxxxxxxxxxxxxxx | The Lazy Leader | Your guide to leadership and the pursuit of productive laziness. | default-newsletter | NULL         | newsletter      | active | members    |                   1 |          0 | NULL         |                1 |                 1 | sans_serif          | center          |                  1 | sans_serif         | NULL           |          1 | NULL        | 2022-05-01 11:29:13 | NULL       |                0 | xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx |
+--------------------------+-----------------+------------------------------------------------------------------+--------------------+--------------+-----------------+--------+------------+---------------------+------------+--------------+------------------+-------------------+---------------------+-----------------+--------------------+--------------------+----------------+------------+-------------+---------------------+------------+------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql>
7

Yikes lol my result set is empty=)

MariaDB [91hockey]> select * from newsletters;
Empty set (0.001 sec)

Do you think it is possible to fix this without rolling back entire server to VM snapshot? I have other stuff on the server besides ghost and rolling back will be very annoying. Also I should mention the “ghost rollback” internal feature didn’t work here, another bug – it detected the problem during upgrade and offered a “rollback” but failed.

Migrating to MySQL isn’t a fun idea either – Ghost isn’t the only thing on the server which uses this DB

And regarding this MariaDB nonsense, sorry just have to bring up the subject lol…

Out of pure curiosity, why does MariaDB even exist? Seems like a copycat of MySQL, if you are going to design a DB system either make it completely compatible with other stuff for backwards compatibility, or “something unique and completely different”

Also, if CentOS is shipping with MariaDB (I never chose to install this, it was just the default SQL) many people could be using it and not even knowing they don’t have “MySQL”, therefore not supporting MariaDB seems risky – guessing this update broke other people too not just me

So yeah in summary a big thumbs down for MariaDB, shouldn’t even exist, and CentOS should come with MySQL not MariaDB… what was ever wrong “so wrong” with MySQL that gave them idea to create MariaDB?

8

Also, this question is more for the developers, I am not expert of the design of Ghost but need to offer these thoughts:

How critical was it to update newsletters table here? Is it really worth changing just a few minor things and introducing incompatibility with separate DB system that was working before? Maybe the changes really were “critical” but I’m just saying if it was something minor maybe it wasn’t worth doing that change and living with something 99% perfect not 100%. So many people use MariaDB (and don’t even know it like me) that keeping Ghost and MariaDB working seems like important way to keep more people using Ghost

1 Like
9

In 4.44.0 the table was empty on my instance; the upgrade populated the table. I don’t know how you get around this. Hopefully, the devs will see this thread and intervene, as it may affect quite a few self-hosted blogs.

MariaDB was intended to be a drop-in replacement for MySQL (following the acquisition by Oracle) but it has gradually diverged from this position. Regarding Ghost, they have always stated that the supported database is MySQL, and sooner or later it would break. There is a warning every time you perform an upgrade now, which is why I made the changes a month or so ago. Ghost 5 will definitely introduce breaking changes if you’re not running MySQL 8, but maybe 4.46.0 has too for MariaDB users.

BTW, it is possible to run MySQL and MariaDB together. I’ve not done this, and wouldn’t want to try. :slight_smile:

10

Thanks for the help… the thing which really upsets me is that MariaDB even uses the command-line tool name “mysql” – are you kidding me? This is confusing because you would never know you are using MariaDB (garbage) and not the official MySQL. Do things originally don’t be a copy cat and change 1-2 minor things… very lame, MariaDB… wow

11

Off-topic, but note, one of the founders of MySQL developed MariaDB. The fork occurred after Sun Microsystems, and later Oracle, acquired the Open Source project. Personally, I would rather use MariaDB, but if you’re using Ghost, MySQL is now a prerequisite.

12

I see, interesting… annoying nevertheless though I can’t support such a duplication of effort for what exact benefits I doubt are significant

Even if I did migrate to MySQL could the problem even be fixed with the broken upgrade?

If the fix for it is very complicated I’ll give someone access to my server and snapshot it before and pay to have it fixed

Otherwise I guess will not be able to use Ghost anymore

13

As a reminder, the Ghost core team normally doesn’t work on weekends; before doing anything you might end up regretting, you might want to give them a few business days to respond :blush:

Database migrations (in general) tend to be very problematic, even when using the same exact database! While MariaDB support is not guaranteed, there’s a chance that something slipped through the cracks and can be fixed.

1 Like
14

@Darkice2, @Peter_Sauer please note:

# 4.46.1

* 🐛 Fixed null values in settings default newsletter migration (#14639)

Does this help you?

15

I just tried upgrading from 4.41.3 to 4.46.1 and have the same issue.
I’m on Ubuntu with MariaDB. I’m not using newsletters (table is empty)

More info:
Debug Information:
OS: Ubuntu, v20.04.4 LTS
Node Version: v14.19.1
Ghost Version: 4.46.1
Ghost-CLI Version: 1.19.3
Environment: production
Command: ‘ghost start’
Message: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
Suggestion: journalctl -u ghost_192-168-5-1 -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
at Server. (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
at Object.onceWrapper (events.js:519:28)
at Server.emit (events.js:400:28)
at emitCloseNT (net.js:1677:8)
at processTicksAndRejections (internal/process/task_queues.js:81:21)

1 Like
16

I’m having the same issue.

17

Hey all - this issue was reported on GitHub and I’ve responded on there: https://github.com/TryGhost/Ghost/issues/14634#issuecomment-1114989262

In short, this is a bug with Knex and MariaDB, and I’ve opened an issue for them to fix it.

@mjw is correct though, we officially only support MySQL 5 & MySQL 8 (and SQLite) and MariaDB always just “happened to work” because of the similarities. Unfortunately in this scenario, the deviation has caused an issue.

1 Like
18

I upgraded to Ghost 4.46.2 but receive the same error after trying to start Ghost:

[91hockey@darkice ~]$ cat /home/91hockey/.ghost/logs/ghost-cli-debug-2022-05-02T20_01_51_900Z.log
Debug Information:
OS: CentOS Linux, v7
Node Version: v14.19.1
Ghost Version: 4.46.2
Ghost-CLI Version: 1.19.3
Environment: production
Command: ‘ghost start’
Message: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
Suggestion: journalctl -u ghost_91hockey -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
at Server. (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
at Object.onceWrapper (events.js:519:28)
at Server.emit (events.js:400:28)
at emitCloseNT (net.js:1677:8)
at processTicksAndRejections (internal/process/task_queues.js:81:21)

19

Today I tried to upgrade Ghost from v4.42.0 to v4.46.2 which failed. Rollback worked for me though.

:heavy_multiplication_x: Restarting Ghost
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
Suggestion: journalctl -u ghost -n 50

Debug Information:
OS: Raspbian GNU/Linux, v11
Node Version: v14.19.1
Ghost Version: 4.46.2
Ghost-CLI Version: 1.19.3
Environment: production
Command: ‘ghost update’

Additional log info available in: /home/www-ghost/.ghost/logs/ghost-cli-debug-2022-05-04T00_27_34_701Z.log

Try running ghost doctor to check your system for known issues.
You can always refer to Ghost-CLI - A fully loaded tool for installation and configuration for troubleshooting.

? Unable to upgrade Ghost from v4.42.0 to v4.46.2. Would you like to revert back to v4.42.0? (Y/n)

$ cat ghost-cli-debug-2022-05-04T00_27_34_701Z.log

Debug Information:
OS: Raspbian GNU/Linux, v11
Node Version: v14.19.1
Ghost Version: 4.46.2
Ghost-CLI Version: 1.19.3
Environment: production
Command: ‘ghost update’
Message: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
Suggestion: journalctl -u ghost -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table newsletters modify created_at datetime null default ‘NULL’ - Invalid default value for ‘created_at’
at Server. (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
at Object.onceWrapper (events.js:519:28)
at Server.emit (events.js:400:28)
at emitCloseNT (net.js:1677:8)
at processTicksAndRejections (internal/process/task_queues.js:81:21)

20

Upgrading to 4.47.0 I still get the same error.