Upgrade from 6.25.0 to 6.30.0, migration fails

[castaway]

Issue Summary

✖ Restarting Ghost
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: 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.
Help: Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-tabl
e.js

Debug Information:
    OS: Debian GNU/Linux, v11
    Node Version: v22.21.1
    Ghost Version: 6.30.0
    Ghost-CLI Version: 1.29.1
    Environment: production
    Command: 'ghost update'

Accepted solution to revert back to 6.25.0 - any ideas?

Steps to Reproduce

Ran: npx ghost update, expected latest version to be installed

Ghost Version

6.30.0

Node.js Version

22.21.1

How did you install Ghost?

manually (back on initial release, pre v2 !)

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

Debug Information:
    OS: Debian GNU/Linux, v11
    Node Version: v22.21.1
    Ghost Version: 6.30.0
    Ghost-CLI Version: 1.29.1
    Environment: production
    Command: 'ghost update'
Message: The database migration in Ghost encountered an error.
Help: https://ghost.org/docs/faq/
Suggestion: ghost update --rollback
--------------- stderr ---------------
/usr/src/ghost/versions/6.30.0/node_modules/.pnpm/@tryghost+errors@1.3.13/node_modules/@tryghost/errors/cjs/wrap-stack.js:25
  const extraLine = (err.stack?.split(/\n/g) || [])[1];
                               ^

SyntaxError: Unexpected token '.'
    at wrapSafe (internal/modules/cjs/loader.js:915:16)
    at Module._compile (internal/modules/cjs/loader.js:963:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1027:10)
    at Module.load (internal/modules/cjs/loader.js:863:32)
    at Function.Module._load (internal/modules/cjs/loader.js:708:14)
    at Module.require (internal/modules/cjs/loader.js:887:19)
    at require (internal/modules/cjs/helpers.js:74:18)

Code of Conduct

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

[ngeorger]

Try this solution please: TryGhost/Ghost-CLI#2126 (comment)

[visualblind]

I'm experiencing this same exact error (2026-04-06-07-48-06-add-gifts-table.js) when I tried to upgrade my docker image from 6.24.0 to 6.30.0.

https___websitename_production.error.log:

{"name":"Log","hostname":"23fed46048b7","pid":1,"level":50,"version":"6.30.0","err":{"id":300,"domain":"https://blog.travisflix.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 /var/lib/ghost/versions/6.30.0/node_modules/.pnpm/knex-migrator@5.3.2/node_modules/knex-migrator/lib/index.js:1032:19\n    at Packet.asError (/var/      lib/ghost/versions/6.30.0/node_modules/.pnpm/mysql2@3.14.1/node_modules/mysql2/lib/packets/packet.js:740:17)\n    at Query.execute (/var/lib/ghost/versions/6.30.0/node_modules/.pnpm/mysql2@3.14.1/               node_modules/mysql2/lib/commands/command.js:29:26)\n    at Connection.handlePacket (/var/lib/ghost/versions/6.30.0/node_modules/.pnpm/mysql2@3.14.1/node_modules/mysql2/lib/base/connection.js:475:34)\n    at     PacketParser.onPacket (/var/lib/ghost/versions/6.30.0/node_modules/.pnpm/mysql2@3.14.1/node_modules/mysql2/lib/base/connection.js:93:12)\n    at PacketParser.executeStart (/var/lib/ghost/versions/6.30.0/        node_modules/.pnpm/mysql2@3.14.1/node_modules/mysql2/lib/packet_parser.js:75:16)\n    at Socket.<anonymous> (/var/lib/ghost/versions/6.30.0/node_modules/.pnpm/mysql2@3.14.1/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-04-22T11:47:16.200Z","v":0}

The non-error log is available here https://pastebin.com/bb6mhFZ3 if that helps. I was able to get back to the version I came from (6.24.0) by specifying the version in the docker image tag. Sorry I don't have any suggestions for you, however you can be sure that this is happening to more people than just yourself.

[castaway]

Try this solution please: TryGhost/Ghost-CLI#2126 (comment)

Is this "install pnpm"? (somehow the link is not indicating which comment) - I had done this, before getting the migration error.

[castaway]

I just retried "ghost update" today, and it's happily fetched and migrated to v6.35.0, so I suspect we can close this one,

[castaway]

Hm spoke too soon: The upgrade went fine, but it would not start.

• I had to edit config.production.json and change database.client from "mysql2" to "mysql" (how did it update/migrate if that was wrong?)
• On start it now tries some more/other migration:

Message: Ghost was able to start, but errored during boot with: 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.
Help: Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-table.js

[visualblind]

Hm spoke too soon: The upgrade went fine, but it would not start.

* I had to edit config.production.json and change database.client from "mysql2" to "mysql" (how did it update/migrate if that was wrong?)

* On start it now tries some more/other migration:

Message: Ghost was able to start, but errored during boot with: 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.
Help: Error occurred while executing the following migration: 2026-04-06-07-48-06-add-gifts-table.js

That's essentially the same error that happened with the upgrade/migration to 6.30. I'm assuming it wasn't able to "start" like it said?

[castaway]

Nope, but I solved mine by removing the "collate" line from the config, see over here: #27263

[visualblind]

I finally resolved my issue as well, finally. Although when searching for the solution to this type of error message you will most likely see references to invalid database charset or collation.
What they don't tell you is that no matter whether or not you have set the database charset/collation correctly, or if you have defined it correctly in your production config file, if you come from an earlier version such as 5.87 then most of the database's schema (DDL) was created with utf8mb3 charset and utf8mb3_general_ci collation.

For me, the easiest fix would have been to preemptively drop the table called automated_email_recipients, since when performing an upgrade from any version earlier than 6.27 to 6.27 or higher it attempts to drop the table automated emails but fails because it has a foreign key that references a column in the automated_email_recipients table.

if I were to do this all over again instead of scatter-braining it with 10,000 open tabs about the intricacies of MySQL and foreign keys and constraints and indexes and not being able to delete FK's until the former(s) were deleted, then I would just run this and be fucking done with it:

DROP TABLE ghost.automated_email_recipients;
DROP TABLE ghost.automated_emails;

Going from version 6.24 to 6.29 the word email was mentioned at least 16 times. That probably doesn't matter, however it would have been nice to have better error handling in the migration scripts since everything is somehow related to email for some damn reason.