ER_FK_INCOMPATIBLE_COLUMNS` on migration `2026-04-06-07-48-06-add-gifts-table.js` in Ghost 6.27.0

[nomad-geek]

Issue Summary

ER_FK_INCOMPATIBLE_COLUMNS on migration 2026-04-06-07-48-06-add-gifts-table.js in Ghost 6.27.0

Issue Summary

After upgrading to Ghost 6.27.0, the server fails to start due to a failed database migration when attempting to add a foreign key constraint on the new gifts table. The migration rolls back cleanly, but Ghost shuts down and cannot start.

Ghost Version: 6.27.0
Environment: Production
Database: MySQL 8.x (self-hosted)
Error Code: ER_FK_INCOMPATIBLE_COLUMNS (Error ID: 300)
Failing migration: 2026-04-06-07-48-06-add-gifts-table.js

---

Behavior

Ghost starts, detects a pending migration, creates a backup, begins adding the gifts table — then fails when attempting to add a foreign key from gifts.buyer_member_id → members.id. The table is dropped during rollback and Ghost shuts down.

This happens reproducibly on every start attempt.

---

Root Cause (suspected)

This appears to be a MySQL 8 collation mismatch. MySQL 8 defaults new tables to utf8mb4_0900_ai_ci, while older Ghost tables (including members) were created under utf8mb4_general_ci. The new gifts table is created with the MySQL 8 default collation, making the FK constraint between gifts.buyer_member_id and members.id incompatible even though both columns are varchar(24).

This same class of issue has affected prior migrations (e.g. donation_payment_events in #17584, comments in #15040).

---

Expected Behavior

The migration should either:

• Explicitly set the collation on the gifts table to match members, or
• Handle the collation mismatch gracefully

---

Workaround

Convert all existing tables to utf8mb4_0900_ai_ci (or pin the DB default collation to utf8mb4_general_ci in my.cnf) before upgrading. See Ghost's MySQL troubleshooting docs for the conversion approach.

Steps to Reproduce

1. Run a self-hosted Ghost instance on MySQL 8 where the database was originally created on MySQL 5.7 (or otherwise has utf8mb4_general_ci tables)
2. Upgrade to Ghost 6.27.0
3. Start Ghost — migration fails immediately

Ghost Version

6.27

Node.js Version

x

How did you install Ghost?

docker

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
[2026-04-09 00:29:45] WARN Ghost is shutting down
[2026-04-09 00:29:45] WARN Ghost has shut down
[2026-04-09 00:29:45] WARN Your site is now offline
[2026-04-09 00:29:45] WARN Ghost was running for a few seconds
[2026-04-09 00:30:37] INFO Ghost is running in production...
[2026-04-09 00:30:37] INFO Your site is now available on https://info.dftba.club/
[2026-04-09 00:30:37] INFO Ctrl+C to shut down
[2026-04-09 00:30:37] INFO Ghost server started in 0.711s
[2026-04-09 00:30:37] WARN Database state requires migration.
[2026-04-09 00:30:38] INFO Creating database backup
[2026-04-09 00:30:38] INFO Database backup written to /var/lib/ghost/content/data/dftba-club.ghost.2026-04-09-00-30-38.json
[2026-04-09 00:30:38] INFO Running migrations.
[2026-04-09 00:30:38] INFO Adding table: gifts
[2026-04-09 00:30:38] INFO Rolling back: 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..
[2026-04-09 00:30:38] INFO Dropping table: gifts
[2026-04-09 00:30:38] INFO Rollback was successful.
[2026-04-09 00:30:38] 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.
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.
config={"transaction":false} config={"transaction":false} name=2026-04-06-07-48-06-add-gifts-table.js
"Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-table.js"
Error ID:
    300
Error Code: 
    ER_FK_INCOMPATIBLE_COLUMNS
----------------------------------------
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.
    at /var/lib/ghost/versions/6.27.0/node_modules/knex-migrator/lib/index.js:1032:19
    at Packet.asError (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/packets/packet.js:739:17)
    at Query.execute (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:508:34)
    at PacketParser.onPacket (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:93:12)
    at PacketParser.executeStart (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:100:25)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
[2026-04-09 00:30:38] WARN Ghost is shutting down
[2026-04-09 00:30:38] WARN Ghost has shut down
[2026-04-09 00:30:38] WARN Your site is now offline
[2026-04-09 00:30:38] WARN Ghost was running for a few seconds
[2026-04-09 00:31:39] INFO Ghost is running in production...
[2026-04-09 00:31:39] INFO Your site is now available on https://info.dftba.club/
[2026-04-09 00:31:39] INFO Ctrl+C to shut down
[2026-04-09 00:31:39] INFO Ghost server started in 0.653s
[2026-04-09 00:31:39] WARN Database state requires migration.
[2026-04-09 00:31:40] INFO Creating database backup
[2026-04-09 00:31:40] INFO Database backup written to /var/lib/ghost/content/data/dftba-club.ghost.2026-04-09-00-31-40.json
[2026-04-09 00:31:40] INFO Running migrations.
[2026-04-09 00:31:40] INFO Adding table: gifts
[2026-04-09 00:31:40] INFO Rolling back: 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..
[2026-04-09 00:31:40] INFO Dropping table: gifts
[2026-04-09 00:31:40] INFO Rollback was successful.
[2026-04-09 00:31:40] 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.
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.
config={"transaction":false} config={"transaction":false} name=2026-04-06-07-48-06-add-gifts-table.js
"Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-table.js"
Error ID:
    300
Error Code: 
    ER_FK_INCOMPATIBLE_COLUMNS
----------------------------------------
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.
    at /var/lib/ghost/versions/6.27.0/node_modules/knex-migrator/lib/index.js:1032:19
    at Packet.asError (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/packets/packet.js:739:17)
    at Query.execute (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:508:34)
    at PacketParser.onPacket (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:93:12)
    at PacketParser.executeStart (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/6.27.0/node_modules/mysql2/lib/base/connection.js:100:25)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
[2026-04-09 00:31:40] WARN Ghost is shutting down
[2026-04-09 00:31:40] WARN Ghost has shut down
[2026-04-09 00:31:40] WARN Your site is now offline
[2026-04-09 00:31:40] WARN Ghost was running for a few seconds

Code of Conduct

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

[HighPriest]

No matter what I do, I am always getting the alter table giftsadd constraintgifts_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.
No matter what collation I set in the database.
I've had the database dropped and recreated with both utf8mb4_general_ci & utf8mb4_0900_ai_ci but still, no dice.

I can't believe this is a thing.