Upgrading from MariaDB can't upgrade past 6.26.0

Developer help Self-hosting
1

Fill out the following bug report template with as much detail as possible!

Are you sure this is a bug? If you just need help, post in the developer help category. If it’s a feature request, head to the ideas category.

Issue Summary

  • The column type that is inherited when migrating from MariaDB to MySQL8 for table ids is not compatible with foreign key constraints during upgrade.
  • Went from v6.0.6 to 6.37.1 and ran into this error. Was able to upgrade to 6.26.0 without issues, 6.27.0 contains the first migration where this shows up.

Steps to Reproduce

  1. Install a pre-v6.27.0 with MariaDB as the backend
  2. Follow mysqldump instructions to upgrade to MySQL8
  3. Use the ghost-cli to upgrade to anything v6.27.0 or higher

Setup information

Ghost Version
Started with 6.0.6, have been able to upgrade to 6.26.0 but any step beyond runs into the issue.

Node.js Version
v22.22.2

How did you install Ghost?
Running on a Raspberry Pi5 connecting to a Linux server running MySQL8. Installed using ghost-cli originally connected to MariaDB running the on the same Pi.

Provide details of your host & operating system
RPi5, Raspberry Pi OS (Debian 12 based)

Database type
MySQL8

Browser & OS version
Include if reporting frontend bugs.

Relevant log / error output
{“name”:“Log”,“hostname”:“raspberrypi”,“pid”:25206,“level”:50,“version”:“6.27.0”,“err”:{“id”:300,“domain”:“https://magicmagswashere.com/",“code”:“ER_FK_INCOMPATIBLE_COLUMNS”,“name”:“MigrationScriptError”,“statusCode”:500,“level”:“critical”,“message”:"alter table gifts add constraint gifts_buyer_member_id_foreign foreign key (buyer_member_id) references members (id) on delete SET NULL - Referencing column ‘buyer_member_id’ and referenced column ‘id’ in foreign key constraint ‘gifts_buyer_member_id_foreign’ are incompatible.”,“context”:“{“config”:{“transaction”:false},“name”:“2026-04-06-07-48-06-add-gifts-table.js”}”,“help”:““Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-table.js””,“stack”:“Error: alter table gifts add constraint gifts_buyer_member_id_foreign foreign key (buyer_member_id) references members (id) on delete SET NULL - Referencing column ‘buyer_member_id’ and referenced column ‘id’ in foreign key constraint ‘gifts_buyer_member_id_foreign’ are incompatible.\n at /home/ghost/versions/6.27.0/node_modules/knex-migrator/lib/index.js:1032:19\n at Packet.asError (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/packets/packet.js:739:17)\n at Query.execute (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/commands/command.js:29:26)\n at Connection.handlePacket (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:508:34)\n at PacketParser.onPacket (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:93:12)\n at PacketParser.executeStart (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/packet_parser.js:75:16)\n at Socket. (/home/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:100:25)\n at Socket.emit (node:events:519:28)\n at addChunk (node:internal/streams/readable:561:12)\n at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)\n at Readable.push (node:internal/streams/readable:392:5)\n at TCP.onStreamRead (node:internal/stream_base_commons:189:23)”,“hideStack”:false},“msg”:“alter table gifts add constraint gifts_buyer_member_id_foreign foreign key (buyer_member_id) references members (id) on delete SET NULL - Referencing column ‘buyer_member_id’ and referenced column ‘id’ in foreign key constraint ‘gifts_buyer_member_id_foreign’ are incompatible.”,“time”:“2026-05-12T11:12:48.336Z”,“v”:0}Copy and paste any relevant log output. Use backticks to format this into code.

2

Foreign keys in MySQL require both tables to have the same character set and collation. Looks like that isn’t the case here.

I assume that most tables have the old MariaDB collation, while newly created tables have a different one they inherited from the MySQL server.

You’ll need to align these.

First, check what you’re dealing with:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND COLUMN_NAME = ‘id’;

SELECT @@collation_database, @@collation_server;

Then pick one collation and convert everything to it. Make sure you have a backup, of course.

3

Thank you, it’s been so long since I did much with MySQL that I didn’t even think the collation would cause that mismatch. Had to figure out how to turn off the foreign key checks but after getting all the tables to use the same collation and character set as the database was the upgrade to 6.27.0 went smooth. Could even jump all the way to current (6.38.0)

I think the collation note should be added to the threads about migrating to MySQL from MariaDB.

4

Which you should, given that with the rise of AI security research, there have been multiple security fixes in recent releases. Now that the database is clean, keep your Ghost site up to date :flexed_biceps:

You can post a link to this thread there :slight_smile: