Migration error on newsletter when upgrading past v4.46.0 [MariaDB]

[Kerumen]

Issue Summary

When I tried to upgrade my Ghost from v4.44.0 to v4.46.0 I got this error message upon launch:

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'

I must add, my newsletters table has no data:

Steps to Reproduce

1. Upgrade Ghost to v4.46.0
2. Launch Ghost

Ghost Version

v4.44.0

Node.js Version

v14.18

How did you install Ghost?

Local install on Linux server

Database type

Other

Browser & OS version

No response

Relevant log / error output

No response

Code of Conduct

• I agree to be friendly and polite to people in this repository

[yannickcof]

same here !!!!

[thatfleminggent]

I can confirm this issue , exact same issue as Kerumen with the following differences in environment:

• Running on CentOS 7 (7,9.2009)
• Nodejs 16.15.0
• Installed locally
• Upgraded from 4,42,1
• Database is MariaDB 1.4.22 (AWS RDS)
• Browser is Chrome 101.0.4951.41 on Windows 11

Hope that info helps

[SimonBackx]

Hi! Thank you for reporting this error 😊

• Could you post the full log output?
• Post the result of this SQL query: SHOW VARIABLES LIKE 'sql_mode'

[Kerumen]

Here is the full log output:

May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] INFO Updated 0 newsletters with created_at = now
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] INFO Dropping nullable: newsletters.created_at
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] INFO Rolling back: alter table `newsletters` modify  `created_at` datetime not null default 'NULL' - Invalid default value f>
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] INFO Setting nullable: newsletters.created_at
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] ERROR alter table `newsletters` modify  `created_at` datetime null default 'NULL' - Invalid default value for 'created_at'
May 02 12:15:06 ns328387 node[2945016]:
May 02 12:15:06 ns328387 node[2945016]: alter table `newsletters` modify  `created_at` datetime null default 'NULL' - Invalid default value for 'created_at'
May 02 12:15:06 ns328387 node[2945016]: "OuterError: alter table `newsletters` modify  `created_at` datetime not null default 'NULL' - Invalid default value for 'created_at'"
May 02 12:15:06 ns328387 node[2945016]: Error ID:
May 02 12:15:06 ns328387 node[2945016]:     400
May 02 12:15:06 ns328387 node[2945016]: Error Code:
May 02 12:15:06 ns328387 node[2945016]:     ER_INVALID_DEFAULT
May 02 12:15:06 ns328387 node[2945016]: ----------------------------------------
May 02 12:15:06 ns328387 node[2945016]: RollbackError: alter table `newsletters` modify  `created_at` datetime null default 'NULL' - Invalid default value for 'created_at'
May 02 12:15:06 ns328387 node[2945016]:     at DatabaseStateManager.makeReady (/www/lolita/versions/4.46.0/core/server/data/db/state-manager.js:95:32)
May 02 12:15:06 ns328387 node[2945016]:     at RollbackError.KnexMigrateError (/www/lolita/versions/4.46.0/node_modules/knex-migrator/lib/errors.js:7:26)
May 02 12:15:06 ns328387 node[2945016]:     at new RollbackError (/www/lolita/versions/4.46.0/node_modules/knex-migrator/lib/errors.js:31:26)
May 02 12:15:06 ns328387 node[2945016]:     at KnexMigrator.migrate (/www/lolita/versions/4.46.0/node_modules/knex-migrator/lib/index.js:418:31)
May 02 12:15:06 ns328387 node[2945016]:     at async DatabaseStateManager.makeReady (/www/lolita/versions/4.46.0/core/server/data/db/state-manager.js:86:17)
May 02 12:15:06 ns328387 node[2945016]:     at async initDatabase (/www/lolita/versions/4.46.0/core/boot.js:69:5)
May 02 12:15:06 ns328387 node[2945016]:     at async bootGhost (/www/lolita/versions/4.46.0/core/boot.js:414:9)
May 02 12:15:06 ns328387 node[2945016]: Error: alter table `newsletters` modify  `created_at` datetime null default 'NULL' - Invalid default value for 'created_at'
May 02 12:15:06 ns328387 node[2945016]:     at Packet.asError (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/packets/packet.js:728:17)
May 02 12:15:06 ns328387 node[2945016]:     at Query.execute (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/commands/command.js:29:26)
May 02 12:15:06 ns328387 node[2945016]:     at Connection.handlePacket (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/connection.js:456:32)
May 02 12:15:06 ns328387 node[2945016]:     at PacketParser.onPacket (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/connection.js:85:12)
May 02 12:15:06 ns328387 node[2945016]:     at PacketParser.executeStart (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/packet_parser.js:75:16)
May 02 12:15:06 ns328387 node[2945016]:     at Socket.<anonymous> (/www/lolita/versions/4.46.0/node_modules/mysql2/lib/connection.js:92:25)
May 02 12:15:06 ns328387 node[2945016]:     at Socket.emit (events.js:400:28)
May 02 12:15:06 ns328387 node[2945016]:     at addChunk (internal/streams/readable.js:293:12)
May 02 12:15:06 ns328387 node[2945016]:     at readableAddChunk (internal/streams/readable.js:267:9)
May 02 12:15:06 ns328387 node[2945016]:     at Socket.Readable.push (internal/streams/readable.js:206:10)
May 02 12:15:06 ns328387 node[2945016]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
May 02 12:15:06 ns328387 node[2945016]:
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] WARN Ghost is shutting down
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] WARN Ghost has shut down
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] WARN Your site is now offline
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] WARN Ghost was running for a few seconds
May 02 12:15:06 ns328387 node[2945016]: [2022-05-02 10:15:06] INFO Bootstrap client was closed.
May 02 12:15:06 ns328387 systemd[1]: Stopping Ghost systemd service for blog: lolitaontheroad-keru-io...
May 02 12:15:07 ns328387 node[2944983]: /home/lolita/.nvm/versions/node/v14.16.1/lib/node_modules/ghost-cli/lib/process-manager.js:46
May 02 12:15:07 ns328387 node[2944983]:         throw error;
May 02 12:15:07 ns328387 node[2944983]:         ^
May 02 12:15:07 ns328387 node[2944983]: {
May 02 12:15:07 ns328387 node[2944983]:   message: "Ghost was able to start, but errored during boot with: alter table `newsletters` modify  `created_at` datetime null default 'NULL' - I>
May 02 12:15:07 ns328387 node[2944983]: }
May 02 12:15:07 ns328387 systemd[1]: ghost_lolitaontheroad-keru-io.service: Main process exited, code=exited, status=1/FAILURE
May 02 12:15:07 ns328387 systemd[1]: ghost_lolitaontheroad-keru-io.service: Failed with result 'exit-code'.
May 02 12:15:07 ns328387 systemd[1]: Stopped Ghost systemd service for blog: lolitaontheroad-keru-io.

And here is the query result:

MariaDB [lolita_prod]> 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.026 sec)

[ptpittman]

Still getting this issue with 4.46.1, sadly! Running MariaDB on Debian, 10.5.15-MariaDB-0+deb11u1 Debian 11, also have not ever actually sent a newsletter so this table will be empty.

Here's output of above requested items:
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+

Debug Information:
OS: Debian GNU/Linux, v11
Node Version: v16.15.0
Ghost Version: 4.46.1
Ghost-CLI Version: 1.19.3
Environment: production
Command: 'ghost upgrade -d snip'
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_nor-no-media-co -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 (node:events:641:28)
at Server.emit (node:events:527:28)
at emitCloseNT (node:net:1728:8)
at processTicksAndRejections (node:internal/process/task_queues:82:21)

[Elrondo46]

Same problem here, rollbacked with a save to 4.45.0. I'm in mariadb

[daniellockyer]

Hi all - this seems to be an issue with Knex and MariaDB. I've opened an issue, so hopefully it can be fixed soon 🙂

Just to point out, MariaDB is not an officially supported database for Ghost. It just happened to work given the similarities with MySQL, but we optimize and test for MySQL 5 and 8.

Ghost v5 will only support MySQL 8 in production, so I'd strongly suggest switching to MySQL 8 to ensure you're running on the recommended setup.

[Kerumen]

Thanks @daniellockyer for the explanation.

Unfortunately there are some environments where MySQL is not supported (Archlinux for example) and we have to use MariaDB. MariaDB is supposed to be a drop-in replacement of MySQL so everything should work as expected (and was working until v4.46).

I know this is not totally in your control, I just wanted to note this :)

[blaine07]

Is this issue fixed with 4.47?

[ErisDS]

The upstream issue hasn't been fixed yet - details in this comment: #14634 (comment)

In the meantime, a friendly forum user has shared how he updated from MariaDB to MySQL8 on Ubuntu here: https://forum.ghost.org/t/how-to-migrate-from-mariadb-10-to-mysql-8/29575

[chaddyc]

Experiencing the same issue since updating to the latest MariaDB and ghost docker images.

[Elrondo46]

Migrated to MySQL. Fortunately I'm in containers mode with Docker.
Just created a temporary container with a new docker volume... Imported my saved database.
Remanaged the old container (using now mysql8) to use the new docker volume. Restarted all.
All goods
There is now no more problems to use the last version of Ghost.

But I think it's stupid dropping mariaDB.

[blaine07]

Elondro, is MariaDB actually being dropped? I thought issue itself was with MariaDB not Ghost?

[Elrondo46]

Elondro, is MariaDB actually being dropped? I thought issue itself was with MariaDB not Ghost?

#14446 > MySQL 8 is supported in all environments & the only supported DB for production.
and
Note :MariaDB is not an officially supported database for Ghost. It just happened to work given the similarities with MySQL, but we optimize and test for MySQL 5 and 8. As of Ghost 5.0 we are only officially supporting MySQL8 in production so that we can double down on DB optimizations. We strongly recommend changing to MySQL8 and a helpful guide can be found here.

[chaddyc]

@Elrondo46 can we perhaps jump into a Discord chat or chat channel. Would you be able to give me a hand? I also run my blog in docker containers using MariaDB...

[Elrondo46]

yeah why not I don't want to send my personal tag in public

[Kerumen]

Please, can we keep the discussion on this thread related to the migration bug and not about the MariaDB support.

@daniellockyer could you explain why is this migration required? Isn't it possible to rewrite it without the Knex bug? I've ran dozen of migrations successfully on my Ghost instance. I don't understand why this one, which seems fairly simple, has a bug and that is the first time we see it on MariaDB. Thanks!

[blaine07]

Please, can we keep the discussion on this thread related to the migration bug and not about the MariaDB support.

@daniellockyer could you explain why is this migration required? Isn't it possible to rewrite it without the Knex bug? I've ran dozen of migrations successfully on my Ghost instance. I don't understand why this one, which seems fairly simple, has a bug and that is the first time we see it on MariaDB. Thanks!

Sure seems we have a hard time keeping it related to migration bug because all the lights point in that dropping MariaDB is intentional?

[ErisDS]

Hey all 👋, I've locked this bug to contributor comments only. It's caused by an upstream bug in knex. When a fix is pushed into knex, Ghost's renovate setup will automatically pull it into Ghost, meaning it will be included in the subsequent release.

If anyone needs or wants some support with migrating from MariaDB to MySQL, please use the forum.

I understand there's a lot of frustration around the clarification that MariaDB is not an officially supported database. As a small team we've always provided a very narrow set of environments that are officially supported to keep our maintenance overhead manageable: Ghost is intended for use with Ubuntu, MySQL, nginx & Node.js LTS.

We're not able to support every flavour of environment without differences being handled correctly in the upstream packages. The way to change what's supported would be to contribute the necessary fixes & improvements - we'd absolutely love to see more contributions around database interoperability in knex.

[daniellockyer]

Hey all 👋🏻 @iBotPeaches sent a PR with the fix to Knex and it's been merged + released: knex/knex#5181

We've just released Ghost v4.48.1 with the updated Knex, so this issue should now be fixed.

Please update to v4.48.1 using Ghost-CLI 🙂