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

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 😃

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.

But I think it’s stupid dropping mariaDB.

Even more stupid was dropping PostgreSQL some time ago. It seems Ghost team is trying to get less and less people using their project. Now all of us should drop MariaDB just because Ghost is using some MySQL specific dialect.

Solely relying on Oracle’s solution (the same that thrashed OpenOffice, bribed african government officials in order to win business contracts, or made dangerous false claims of security…) despite a community-driven one is beyond me.

Update: mysql-8.0 is neither available in Debian stable repositories, nor testing repos (but in sid for now). Installing mysql-8.0 from Oracle (who I trust less than Debian packagers) break dependencies with packages relying on MariaDB 10, the default-mysql-server in Debian. So, because of a single migration, one of the biggest distros cannot be used to host a Ghost blog. This is madness.

same here !!!

Instead of pushing on solving the knex bug “default ‘NULL’” for datetime type, which is obviously wrong, ‘NULL’ is string and not the datetime type, Ghost team wants to drop MariaDB. Great guys, stick with enforcing bugs. And instead of switching to type strict TypeScript, let’s keep JS and no types in database, who cares, same sh*t. It’s probably the right time to write another blog post for a falling open source project.

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.

Thanks, I also found the post on their forum too. Astoundingly stupid decision. I don’t even use the “newsletters” stuff. 😞

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?

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.

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)

My comment in the announcement of Ghost 5 has been marked off-topic, why? Do you really want to intentionally make BC just to drop MariaDB? And only keep shi**y MySQL with the wrong behavior? Datetime can’t have default value of ‘NULL’, that’s string guys.

I’d just like to say…you made a comment about “non-commercial” users but…Don’t THEY know those of us TINKERING with it are who will sell it for them if it’s something we like? LOL sigh…

My comment in the announcement of Ghost 5 has been marked off-topic, why? Do you really want to intentionally make BC just to drop MariaDB? And only keep shi**y MySQL with the wrong behavior? Datetime can’t have default value of ‘NULL’, that’s string guys.

The upstream issue hasn’t been fixed yet - details in this comment: https://github.com/TryGhost/Ghost/issues/14634#issuecomment-1114989262

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