Unable to upgrade Ghost from v4.2.0 to v4.3.0 (CASCADE - UNKNOWN_CODE_PLEASE_REPORT)

Hi,

Everything used to work like a butter. I think we are running Ghost since 3.0 and had zero problems with updates in the past. Any idea how to fix it? Thanks for the help!

Debug Information:
OS: Ubuntu, v20.04.2 LTS
Node Version: v14.16.1
Ghost Version: 4.3.0
Ghost-CLI Version: 1.16.3
mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
Environment: production
Command: ‘ghost update’

Message: Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_products_member_id_foreign’ are incompatible.

Help: Error occurred while executing the following migration: 02-add-members-products-table.js
Suggestion: journalctl -u ghost_blog-wavy -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_products_member_id_foreign' are incompatible.
    at Server.<anonymous> (/usr/local/lib/node_modules/ghost-cli/lib/utils/port-polling.js:53:28)
    at Object.onceWrapper (events.js:421:28)
    at Server.emit (events.js:315:20)
    at emitCloseNT (net.js:1659:8)
    at processTicksAndRejections (internal/process/task_queues.js:79:21)

Running ghost-doctor does not help/show any problems. Reboot does not help either.

2 Likes

UNKNOWN_CODE_PLEASE_REPORT suggests collation errors with your database. Did you recently upgrade MySQL?

Hi, Hannah!

I don’t think so. We only thing we did update recently was Ubuntu version (not sure if MySQL got updated as well), which forced us to reinstall node and npm. After that we updated from latest 3.x.x. to new 4 version without problems. Today I tried to update ghost-cli which worked fine and then ghost update to newest version.

Currently we are running mysql Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Which MySQL version should be used, should we roll back to old version? Thanks!

I am experiencing this issue as well after upgrading Ghost from v4.2.2 to v4.3.0. I’ve been running Ghost since v3.31 and encountered a similar error during upgrades before, but the usual fix (temporarily turning off foreign key checks using SET GLOBAL FOREIGN_KEY_CHECKS = 0;) did not work this time, or rather, it resulted in a different error.

Debug Information
OS: Debian Buster v10.9.0
Node: v14.16.1
Ghost: v4.3.0
Ghost-CLI: v1.16.3
Database: mysql Ver 15.1 Distrib 10.5.9-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Environment: production
Command: ghost run

Debug logs

Apr 21 11:53:59 pdh systemd[1]: Started Ghost.
Apr 21 11:54:00 pdh node[8958]: - Inspecting operating system
Apr 21 11:54:02 pdh node[8958]: [2021-04-21 09:54:02] INFO Ghost is running in production...
Apr 21 11:54:02 pdh node[8958]: [2021-04-21 09:54:02] INFO Your site is now available on <redacted>
Apr 21 11:54:02 pdh node[8958]: [2021-04-21 09:54:02] INFO Ctrl+C to shut down
Apr 21 11:54:02 pdh node[8958]: [2021-04-21 09:54:02] INFO Ghost server started in 1.136s
Apr 21 11:54:02 pdh node[8958]: [2021-04-21 09:54:02] WARN Database state requires migration.
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Creating database backup
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Database backup written to: <redacted>
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Adding table: products
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Adding table: members_products
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Dropping table: members_products
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] INFO Dropping table: products
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] ERROR alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 11:54:03 pdh node[8958]: 
Apr 21 11:54:03 pdh node[8958]: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 11:54:03 pdh node[8958]: {"config":{"transaction":false},"name":"02-add-members-products-table.js"}
Apr 21 11:54:03 pdh node[8958]: "Error occurred while executing the following migration: 02-add-members-products-table.js"
Apr 21 11:54:03 pdh node[8958]: Error ID:
Apr 21 11:54:03 pdh node[8958]:     300
Apr 21 11:54:03 pdh node[8958]: Error Code: 
Apr 21 11:54:03 pdh node[8958]:     ER_FK_INCORRECT_OPTION
Apr 21 11:54:03 pdh node[8958]: ----------------------------------------
Apr 21 11:54:03 pdh node[8958]: MigrationScriptError: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 11:54:03 pdh node[8958]:     at MigrationScriptError.KnexMigrateError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:7:26)
Apr 21 11:54:03 pdh node[8958]:     at new MigrationScriptError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:25:26)
Apr 21 11:54:03 pdh node[8958]:     at /opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/index.js:1055:19
Apr 21 11:54:03 pdh node[8958]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 11:54:03 pdh node[8958]: Error: ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 11:54:03 pdh node[8958]:     at Query.Sequence._packetToError (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
Apr 21 11:54:03 pdh node[8958]:     at Query.ErrorPacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
Apr 21 11:54:03 pdh node[8958]:     at Protocol._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
Apr 21 11:54:03 pdh node[8958]:     at Parser._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
Apr 21 11:54:03 pdh node[8958]:     at Parser.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
Apr 21 11:54:03 pdh node[8958]:     at Protocol.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
Apr 21 11:54:03 pdh node[8958]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:88:28)
Apr 21 11:54:03 pdh node[8958]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:526:10)
Apr 21 11:54:03 pdh node[8958]:     at Socket.emit (events.js:315:20)
Apr 21 11:54:03 pdh node[8958]:     at addChunk (internal/streams/readable.js:309:12)
Apr 21 11:54:03 pdh node[8958]:     at readableAddChunk (internal/streams/readable.js:284:9)
Apr 21 11:54:03 pdh node[8958]:     at Socket.Readable.push (internal/streams/readable.js:223:10)
Apr 21 11:54:03 pdh node[8958]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
Apr 21 11:54:03 pdh node[8958]:     --------------------
Apr 21 11:54:03 pdh node[8958]:     at Protocol._enqueue (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
Apr 21 11:54:03 pdh node[8958]:     at Connection.query (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:198:25)
Apr 21 11:54:03 pdh node[8958]:     at /opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:134:18
Apr 21 11:54:03 pdh node[8958]:     at new Promise (<anonymous>)
Apr 21 11:54:03 pdh node[8958]:     at Client_MySQL._query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:128:12)
Apr 21 11:54:03 pdh node[8958]:     at Client_MySQL.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/client.js:168:17)
Apr 21 11:54:03 pdh node[8958]:     at Runner.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:151:36)
Apr 21 11:54:03 pdh node[8958]:     at Runner.queryArray (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:248:31)
Apr 21 11:54:03 pdh node[8958]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 11:54:03 pdh node[8958]:     at async /opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:277:18
Apr 21 11:54:03 pdh node[8958]:     at async Object.up (/opt/ghost/versions/4.3.0/core/server/data/migrations/utils.js:239:13)
Apr 21 11:54:03 pdh node[8958]: 
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] WARN Ghost is shutting down
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] WARN Ghost has shut down
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] WARN Your site is now offline
Apr 21 11:54:03 pdh node[8958]: [2021-04-21 09:54:03] WARN Ghost was running for a few seconds
Apr 21 11:54:04 pdh node[8958]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
Apr 21 11:54:04 pdh node[8958]:         throw error;
Apr 21 11:54:04 pdh node[8958]:         ^
Apr 21 11:54:04 pdh node[8958]: {
Apr 21 11:54:04 pdh node[8958]:   message: "Ghost was able to start, but errored during boot with: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'"
Apr 21 11:54:04 pdh node[8958]: }
Apr 21 11:54:04 pdh systemd[1]: ghost.service: Main process exited, code=exited, status=1/FAILURE
Apr 21 11:54:04 pdh systemd[1]: ghost.service: Failed with result 'exit-code'.
Apr 21 11:59:58 pdh systemd[1]: Started Ghost.
Apr 21 11:59:59 pdh node[20371]: - Inspecting operating system
Apr 21 12:00:00 pdh node[20371]: [2021-04-21 10:00:00] INFO Ghost is running in production...
Apr 21 12:00:00 pdh node[20371]: [2021-04-21 10:00:00] INFO Your site is now available on https://blog.datahoarder.dev/
Apr 21 12:00:00 pdh node[20371]: [2021-04-21 10:00:00] INFO Ctrl+C to shut down
Apr 21 12:00:00 pdh node[20371]: [2021-04-21 10:00:00] INFO Ghost server started in 1.095s
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] WARN Database state requires migration.
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Creating database backup
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Database backup written to: /opt/ghost/content/data/self-hosting-for-dummies.ghost.2021-04-21-10-00-01.json
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Adding table: products
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Adding table: members_products
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Dropping table: members_products
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] INFO Dropping table: products
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] ERROR alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:00:01 pdh node[20371]: 
Apr 21 12:00:01 pdh node[20371]: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:00:01 pdh node[20371]: {"config":{"transaction":false},"name":"02-add-members-products-table.js"}
Apr 21 12:00:01 pdh node[20371]: "Error occurred while executing the following migration: 02-add-members-products-table.js"
Apr 21 12:00:01 pdh node[20371]: Error ID:
Apr 21 12:00:01 pdh node[20371]:     300
Apr 21 12:00:01 pdh node[20371]: Error Code: 
Apr 21 12:00:01 pdh node[20371]:     ER_FK_INCORRECT_OPTION
Apr 21 12:00:01 pdh node[20371]: ----------------------------------------
Apr 21 12:00:01 pdh node[20371]: MigrationScriptError: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:00:01 pdh node[20371]:     at MigrationScriptError.KnexMigrateError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:7:26)
Apr 21 12:00:01 pdh node[20371]:     at new MigrationScriptError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:25:26)
Apr 21 12:00:01 pdh node[20371]:     at /opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/index.js:1055:19
Apr 21 12:00:01 pdh node[20371]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:00:01 pdh node[20371]: Error: ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:00:01 pdh node[20371]:     at Query.Sequence._packetToError (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
Apr 21 12:00:01 pdh node[20371]:     at Query.ErrorPacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
Apr 21 12:00:01 pdh node[20371]:     at Protocol._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
Apr 21 12:00:01 pdh node[20371]:     at Parser._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
Apr 21 12:00:01 pdh node[20371]:     at Parser.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
Apr 21 12:00:01 pdh node[20371]:     at Protocol.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
Apr 21 12:00:01 pdh node[20371]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:88:28)
Apr 21 12:00:01 pdh node[20371]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:526:10)
Apr 21 12:00:01 pdh node[20371]:     at Socket.emit (events.js:315:20)
Apr 21 12:00:01 pdh node[20371]:     at addChunk (internal/streams/readable.js:309:12)
Apr 21 12:00:01 pdh node[20371]:     at readableAddChunk (internal/streams/readable.js:284:9)
Apr 21 12:00:01 pdh node[20371]:     at Socket.Readable.push (internal/streams/readable.js:223:10)
Apr 21 12:00:01 pdh node[20371]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
Apr 21 12:00:01 pdh node[20371]:     --------------------
Apr 21 12:00:01 pdh node[20371]:     at Protocol._enqueue (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
Apr 21 12:00:01 pdh node[20371]:     at Connection.query (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:198:25)
Apr 21 12:00:01 pdh node[20371]:     at /opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:134:18
Apr 21 12:00:01 pdh node[20371]:     at new Promise (<anonymous>)
Apr 21 12:00:01 pdh node[20371]:     at Client_MySQL._query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:128:12)
Apr 21 12:00:01 pdh node[20371]:     at Client_MySQL.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/client.js:168:17)
Apr 21 12:00:01 pdh node[20371]:     at Runner.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:151:36)
Apr 21 12:00:01 pdh node[20371]:     at Runner.queryArray (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:248:31)
Apr 21 12:00:01 pdh node[20371]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:00:01 pdh node[20371]:     at async /opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:277:18
Apr 21 12:00:01 pdh node[20371]:     at async Object.up (/opt/ghost/versions/4.3.0/core/server/data/migrations/utils.js:239:13)
Apr 21 12:00:01 pdh node[20371]: 
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] WARN Ghost is shutting down
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] WARN Ghost has shut down
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] WARN Your site is now offline
Apr 21 12:00:01 pdh node[20371]: [2021-04-21 10:00:01] WARN Ghost was running for a few seconds
Apr 21 12:00:02 pdh node[20371]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
Apr 21 12:00:02 pdh node[20371]:         throw error;
Apr 21 12:00:02 pdh node[20371]:         ^
Apr 21 12:00:02 pdh node[20371]: {
Apr 21 12:00:02 pdh node[20371]:   message: "Ghost was able to start, but errored during boot with: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'"
Apr 21 12:00:02 pdh node[20371]: }
Apr 21 12:00:02 pdh systemd[1]: ghost.service: Main process exited, code=exited, status=1/FAILURE
Apr 21 12:00:02 pdh systemd[1]: ghost.service: Failed with result 'exit-code'.

Debug logs with foreign key checks disabled

Apr 21 12:14:59 pdh systemd[1]: Started Ghost.
Apr 21 12:15:00 pdh node[14092]: - Inspecting operating system
Apr 21 12:15:01 pdh node[14092]: [2021-04-21 10:15:01] INFO Ghost is running in production...
Apr 21 12:15:01 pdh node[14092]: [2021-04-21 10:15:01] INFO Your site is now available on <redacted>
Apr 21 12:15:01 pdh node[14092]: [2021-04-21 10:15:01] INFO Ctrl+C to shut down
Apr 21 12:15:01 pdh node[14092]: [2021-04-21 10:15:01] INFO Ghost server started in 1.062s
Apr 21 12:15:02 pdh node[14092]: [2021-04-21 10:15:02] WARN Database state requires migration.
Apr 21 12:15:02 pdh node[14092]: [2021-04-21 10:15:02] INFO Creating database backup
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] INFO Database backup written to: <redacted>
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] INFO Adding table: products
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] INFO Adding table: members_products
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] INFO Dropping table: members_products
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] INFO Dropping table: products
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] ERROR alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `app_db_ghost`.`members_products` (errno: 150 "Foreign key constraint is incorrectly formed")
Apr 21 12:15:03 pdh node[14092]: 
Apr 21 12:15:03 pdh node[14092]: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `app_db_ghost`.`members_products` (errno: 150 "Foreign key constraint is incorrectly formed")
Apr 21 12:15:03 pdh node[14092]: {"config":{"transaction":false},"name":"02-add-members-products-table.js"}
Apr 21 12:15:03 pdh node[14092]: "Error occurred while executing the following migration: 02-add-members-products-table.js"
Apr 21 12:15:03 pdh node[14092]: Error ID:
Apr 21 12:15:03 pdh node[14092]:     300
Apr 21 12:15:03 pdh node[14092]: Error Code: 
Apr 21 12:15:03 pdh node[14092]:     ER_CANT_CREATE_TABLE
Apr 21 12:15:03 pdh node[14092]: ----------------------------------------
Apr 21 12:15:03 pdh node[14092]: MigrationScriptError: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `app_db_ghost`.`members_products` (errno: 150 "Foreign key constraint is incorrectly formed")
Apr 21 12:15:03 pdh node[14092]:     at MigrationScriptError.KnexMigrateError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:7:26)
Apr 21 12:15:03 pdh node[14092]:     at new MigrationScriptError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:25:26)
Apr 21 12:15:03 pdh node[14092]:     at /opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/index.js:1055:19
Apr 21 12:15:03 pdh node[14092]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:15:03 pdh node[14092]: Error: ER_CANT_CREATE_TABLE: Can't create table `app_db_ghost`.`members_products` (errno: 150 "Foreign key constraint is incorrectly formed")
Apr 21 12:15:03 pdh node[14092]:     at Query.Sequence._packetToError (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
Apr 21 12:15:03 pdh node[14092]:     at Query.ErrorPacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
Apr 21 12:15:03 pdh node[14092]:     at Protocol._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
Apr 21 12:15:03 pdh node[14092]:     at Parser._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
Apr 21 12:15:03 pdh node[14092]:     at Parser.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
Apr 21 12:15:03 pdh node[14092]:     at Protocol.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
Apr 21 12:15:03 pdh node[14092]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:88:28)
Apr 21 12:15:03 pdh node[14092]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:526:10)
Apr 21 12:15:03 pdh node[14092]:     at Socket.emit (events.js:315:20)
Apr 21 12:15:03 pdh node[14092]:     at addChunk (internal/streams/readable.js:309:12)
Apr 21 12:15:03 pdh node[14092]:     at readableAddChunk (internal/streams/readable.js:284:9)
Apr 21 12:15:03 pdh node[14092]:     at Socket.Readable.push (internal/streams/readable.js:223:10)
Apr 21 12:15:03 pdh node[14092]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
Apr 21 12:15:03 pdh node[14092]:     --------------------
Apr 21 12:15:03 pdh node[14092]:     at Protocol._enqueue (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
Apr 21 12:15:03 pdh node[14092]:     at Connection.query (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:198:25)
Apr 21 12:15:03 pdh node[14092]:     at /opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:134:18
Apr 21 12:15:03 pdh node[14092]:     at new Promise (<anonymous>)
Apr 21 12:15:03 pdh node[14092]:     at Client_MySQL._query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:128:12)
Apr 21 12:15:03 pdh node[14092]:     at Client_MySQL.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/client.js:168:17)
Apr 21 12:15:03 pdh node[14092]:     at Runner.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:151:36)
Apr 21 12:15:03 pdh node[14092]:     at Runner.queryArray (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:248:31)
Apr 21 12:15:03 pdh node[14092]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:15:03 pdh node[14092]:     at async /opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:277:18
Apr 21 12:15:03 pdh node[14092]:     at async Object.up (/opt/ghost/versions/4.3.0/core/server/data/migrations/utils.js:239:13)
Apr 21 12:15:03 pdh node[14092]: 
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] WARN Ghost is shutting down
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] WARN Ghost has shut down
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] WARN Your site is now offline
Apr 21 12:15:03 pdh node[14092]: [2021-04-21 10:15:03] WARN Ghost was running for a few seconds
Apr 21 12:15:04 pdh node[14092]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
Apr 21 12:15:04 pdh node[14092]:         throw error;
Apr 21 12:15:04 pdh node[14092]:         ^
Apr 21 12:15:04 pdh node[14092]: {
Apr 21 12:15:04 pdh node[14092]:   message: 'Ghost was able to start, but errored during boot with: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can\'t create table `app_db_ghost`.`members_products` (errno: 150 "Foreign key constraint is incorrectly formed")'
Apr 21 12:15:04 pdh node[14092]: }
Apr 21 12:15:04 pdh systemd[1]: ghost.service: Main process exited, code=exited, status=1/FAILURE
Apr 21 12:15:04 pdh systemd[1]: ghost.service: Failed with result 'exit-code'.
Apr 21 12:54:06 pdh systemd[1]: Started Ghost.
Apr 21 12:54:07 pdh node[2400]: - Inspecting operating system
Apr 21 12:54:09 pdh node[2400]: [2021-04-21 10:54:09] INFO Ghost is running in production...
Apr 21 12:54:09 pdh node[2400]: [2021-04-21 10:54:09] INFO Your site is now available on https://blog.datahoarder.dev/
Apr 21 12:54:09 pdh node[2400]: [2021-04-21 10:54:09] INFO Ctrl+C to shut down
Apr 21 12:54:09 pdh node[2400]: [2021-04-21 10:54:09] INFO Ghost server started in 1.114s
Apr 21 12:54:09 pdh node[2400]: [2021-04-21 10:54:09] WARN Database state requires migration.
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Creating database backup
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Database backup written to: /opt/ghost/content/data/self-hosting-for-dummies.ghost.2021-04-21-10-54-10.json
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Adding table: products
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Adding table: members_products
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Dropping table: members_products
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] INFO Dropping table: products
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] ERROR alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:54:10 pdh node[2400]: 
Apr 21 12:54:10 pdh node[2400]: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:54:10 pdh node[2400]: {"config":{"transaction":false},"name":"02-add-members-products-table.js"}
Apr 21 12:54:10 pdh node[2400]: "Error occurred while executing the following migration: 02-add-members-products-table.js"
Apr 21 12:54:10 pdh node[2400]: Error ID:
Apr 21 12:54:10 pdh node[2400]:     300
Apr 21 12:54:10 pdh node[2400]: Error Code: 
Apr 21 12:54:10 pdh node[2400]:     ER_FK_INCORRECT_OPTION
Apr 21 12:54:10 pdh node[2400]: ----------------------------------------
Apr 21 12:54:10 pdh node[2400]: MigrationScriptError: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:54:10 pdh node[2400]:     at MigrationScriptError.KnexMigrateError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:7:26)
Apr 21 12:54:10 pdh node[2400]:     at new MigrationScriptError (/opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/errors.js:25:26)
Apr 21 12:54:10 pdh node[2400]:     at /opt/ghost/versions/4.3.0/node_modules/knex-migrator/lib/index.js:1055:19
Apr 21 12:54:10 pdh node[2400]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:54:10 pdh node[2400]: Error: ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'
Apr 21 12:54:10 pdh node[2400]:     at Query.Sequence._packetToError (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
Apr 21 12:54:10 pdh node[2400]:     at Query.ErrorPacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
Apr 21 12:54:10 pdh node[2400]:     at Protocol._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
Apr 21 12:54:10 pdh node[2400]:     at Parser._parsePacket (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
Apr 21 12:54:10 pdh node[2400]:     at Parser.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
Apr 21 12:54:10 pdh node[2400]:     at Protocol.write (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
Apr 21 12:54:10 pdh node[2400]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:88:28)
Apr 21 12:54:10 pdh node[2400]:     at Socket.<anonymous> (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:526:10)
Apr 21 12:54:10 pdh node[2400]:     at Socket.emit (events.js:315:20)
Apr 21 12:54:10 pdh node[2400]:     at addChunk (internal/streams/readable.js:309:12)
Apr 21 12:54:10 pdh node[2400]:     at readableAddChunk (internal/streams/readable.js:284:9)
Apr 21 12:54:10 pdh node[2400]:     at Socket.Readable.push (internal/streams/readable.js:223:10)
Apr 21 12:54:10 pdh node[2400]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
Apr 21 12:54:10 pdh node[2400]:     --------------------
Apr 21 12:54:10 pdh node[2400]:     at Protocol._enqueue (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
Apr 21 12:54:10 pdh node[2400]:     at Connection.query (/opt/ghost/versions/4.3.0/node_modules/mysql/lib/Connection.js:198:25)
Apr 21 12:54:10 pdh node[2400]:     at /opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:134:18
Apr 21 12:54:10 pdh node[2400]:     at new Promise (<anonymous>)
Apr 21 12:54:10 pdh node[2400]:     at Client_MySQL._query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/dialects/mysql/index.js:128:12)
Apr 21 12:54:10 pdh node[2400]:     at Client_MySQL.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/client.js:168:17)
Apr 21 12:54:10 pdh node[2400]:     at Runner.query (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:151:36)
Apr 21 12:54:10 pdh node[2400]:     at Runner.queryArray (/opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:248:31)
Apr 21 12:54:10 pdh node[2400]:     at processTicksAndRejections (internal/process/task_queues.js:93:5)
Apr 21 12:54:10 pdh node[2400]:     at async /opt/ghost/versions/4.3.0/node_modules/knex/lib/runner.js:277:18
Apr 21 12:54:10 pdh node[2400]:     at async Object.up (/opt/ghost/versions/4.3.0/core/server/data/migrations/utils.js:239:13)
Apr 21 12:54:10 pdh node[2400]: 
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] WARN Ghost is shutting down
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] WARN Ghost has shut down
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] WARN Your site is now offline
Apr 21 12:54:10 pdh node[2400]: [2021-04-21 10:54:10] WARN Ghost was running for a few seconds
Apr 21 12:54:11 pdh node[2400]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
Apr 21 12:54:11 pdh node[2400]:         throw error;
Apr 21 12:54:11 pdh node[2400]:         ^
Apr 21 12:54:11 pdh node[2400]: {
Apr 21 12:54:11 pdh node[2400]:   message: "Ghost was able to start, but errored during boot with: alter table `members_products` add constraint `members_products_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table 'members_products'. Incorrect options in FOREIGN KEY constraint 'app_db_ghost/members_products_member_id_foreign'"
Apr 21 12:54:11 pdh node[2400]: }
Apr 21 12:54:11 pdh systemd[1]: ghost.service: Main process exited, code=exited, status=1/FAILURE
Apr 21 12:54:11 pdh systemd[1]: ghost.service: Failed with result 'exit-code'.
1 Like

This might be the possible solution, but I haven’t tried. Ghost 4.1.0 errored during boot - #5 by acburdine

That person had to do fresh reinstall after all and it didn’t help. Would be nice to get official answer and how to fix this issue.

I did check my collation for ghost database and it seems to be correct. I did alter it just in case and rebooted server, but no change. Same error then trying to update ghost.

mysql> SELECT SCHEMA_NAME
    -> DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
    -> INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME='ghostdb';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| ghostdb                    | utf8mb4_general_ci     |
+----------------------------+------------------------+
1 row in set (0.00 sec)
1 Like

If you have the “are incompatible” type errors you need to check the collation on all of your tables because when the default collation changes new tables will be created with a different collation to the old ones. There are multiple similar reports on the forum when searching, here’s one example Migration error after upgrading from 3.32.2 to 3.33.0

@whalehub that’s looks like a different error, please don’t hijack existing topics because it makes helping a lot harder when responses are split across multiple different problems.

Collation seems to be fine and I did alter it just in case…

mysql> show full columns from members_subscribe_events;
+------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field      | Type        | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| id         | varchar(24) | utf8mb4_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| member_id  | varchar(24) | utf8mb4_general_ci | NO   | MUL | NULL    |       | select,insert,update,references |         |
| subscribed | tinyint(1)  | NULL               | NO   |     | 1       |       | select,insert,update,references |         |
| created_at | datetime    | NULL               | NO   |     | NULL    |       | select,insert,update,references |         |
| source     | varchar(50) | utf8mb4_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+------------+-------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
5 rows in set (0.01 sec)

And it’s the same collation for members and members_products tables referenced in the original error?

mysql> show full columns from members;
±-------------------±--------------±-------------------±-----±----±--------±------±--------------------------------± --------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
±-------------------±--------------±-------------------±-----±----±--------±------±--------------------------------± --------+
| id | varchar(24) | utf8mb4_general_ci | NO | PRI | NULL | | select,insert,update,references | |
| email | varchar(191) | utf8mb4_general_ci | NO | UNI | NULL | | select,insert,update,references | |
| created_at | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| created_by | varchar(24) | utf8mb4_general_ci | NO | | NULL | | select,insert,update,references | |
| updated_at | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| updated_by | varchar(24) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| name | varchar(191) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| note | varchar(2000) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| subscribed | tinyint(1) | NULL | YES | | 1 | | select,insert,update,references | |
| uuid | varchar(36) | utf8mb4_general_ci | YES | UNI | NULL | | select,insert,update,references | |
| geolocation | varchar(2000) | utf8mb4_general_ci | YES | | NULL | | select,insert,update,references | |
| email_open_rate | int unsigned | NULL | YES | MUL | NULL | | select,insert,update,references | |
| email_count | int unsigned | NULL | NO | | 0 | | select,insert,update,references | |
| email_opened_count | int unsigned | NULL | NO | | 0 | | select,insert,update,references | |
| status | varchar(50) | utf8mb4_general_ci | NO | | free | | select,insert,update,references | |
±-------------------±--------------±-------------------±-----±----±--------±------±--------------------------------± --------+
15 rows in set (0.00 sec)

Seems like I don’t have members_products at all. Otherwise everything looks fine.

mysql> show full columns from members_products;
ERROR 1146 (42S02): Table ‘ghostdb.members_products’ doesn’t exist

I’m getting the same error when trying to update 4.1.2 to 4.3.0. I’m on a DigitalOcean droplet using the 1-click app.

Debug Information:
OS: Ubuntu, v20.04.2 LTS
Node Version: v12.22.1
Ghost Version: 4.3.0
Ghost-CLI Version: 1.16.3
Environment: production
Command: ‘ghost update’

The droplet was created within the last month, but I did run do-release-upgrade to upgrade to Ubuntu 20.04 from 18.04. (Don’t know if that’s relevant, but the OP did mention upgrading Ubuntu recently too.)

2 Likes

Same here on two different instances…

1 Like

What makes you think that it’s a different issue? The error messages are identical. The different error codes at the end are due to @Jack_Oakley using MySQL whereas I’m using MariaDB.

Jack_Oakley’s logs

Message: Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key ( member_id ) references members ( id ) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_products_member_id_foreign’ are incompatible.

My logs

message: “Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - ER_FK_INCORRECT_OPTION: Failed to add the foreign key constraint on table ‘members_products’. Incorrect options in FOREIGN KEY constraint ‘app_db_ghost/members_products_member_id_foreign’”

1 Like

Can you provide logs and check collation? Maybe we will find out out something new.

@Jack_Oakley can you query for charset rather than collation for your members table? Plus your earlier default charset/collation query had a bug where it showed the schema name rather than character set

mysql> SELECT SCHEMA_NAME
    -> DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
    -> INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME='ghostdb';

should have been:

mysql> SELECT
    -> DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
    -> INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME='ghostdb';
1 Like

@Kevin Default charset seems to be fine?

mysql> SELECT
    -> DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
    -> INFORMATION_SCHEMA.SCHEMATA WHERE
    -> SCHEMA_NAME='ghostdb';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+
1 row in set (0.01 sec)

And charset query for members looks like this:

| members | CREATE TABLE `members` (
  `id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(191) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `note` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `subscribed` tinyint(1) DEFAULT '1',
  `uuid` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `geolocation` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email_open_rate` int unsigned DEFAULT NULL,
  `email_count` int unsigned NOT NULL DEFAULT '0',
  `email_opened_count` int unsigned NOT NULL DEFAULT '0',
  `status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'free',
  PRIMARY KEY (`id`),
  UNIQUE KEY `members_email_unique` (`email`),
  UNIQUE KEY `members_uuid_unique` (`uuid`),
  KEY `members_email_open_rate_index` (`email_open_rate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

Added some more stuff:

mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)
1 Like

Sadly not. It’s my production server so when I saw others were having the same issue I nuked it and restored from the backup I took immediately beforehand.

Can you share your logs and charset/collation of you database?

Hi, everyone! I received the same error. Here is my log output, in case it helps:

Debug Information:
OS: Ubuntu, v20.04.2 LTS
Node Version: v12.22.1
Ghost Version: 4.3.2
Ghost-CLI Version: 1.16.3
Environment: production
Command: ‘ghost update’
Message: Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CODE_PL>Help: Error occurred while executing the following migration: 02-add-members-products-table.js
Suggestion: journalctl -u ghost_104-236-56-158 -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CO> at Server. (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:53:28)
at Object.onceWrapper (events.js:420:28)
at Server.emit (events.js:314:20)
at emitCloseNT (net.js:1657:8)
at processTicksAndRejections (internal/process/task_queues.js:83:21)

I’m on the ghost digitalocean marketplace image. Didn’t change much besides upgrading to the latest Ubuntu LTS version.
So I assume many people using the Digital Ocean marketplace image will run into this.

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'ghost_production';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+
1 Like