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

Jack_Oakley · April 21, 2021, 9:31am

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.

Hannah · April 21, 2021, 9:56am

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

Jack_Oakley · April 21, 2021, 10:30am

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!

whalehub · April 21, 2021, 11:01am

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'.

Jack_Oakley · April 21, 2021, 4:20pm

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.

Jack_Oakley · April 21, 2021, 4:48pm

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)

Kevin · April 21, 2021, 4:59pm

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.

Jack_Oakley · April 21, 2021, 5:02pm

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)

Kevin · April 21, 2021, 5:15pm

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

Jack_Oakley · April 21, 2021, 5:44pm

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

stuwest · April 21, 2021, 5:54pm

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.)

binaryanomaly · April 21, 2021, 6:06pm

Same here on two different instances…

whalehub · April 21, 2021, 6:47pm

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’”

Jack_Oakley · April 22, 2021, 8:20am

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

Kevin · April 22, 2021, 8:58am

@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';

Jack_Oakley · April 22, 2021, 9:41am

@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)

stuwest · April 22, 2021, 4:42pm

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.

Jack_Oakley · April 23, 2021, 10:50am

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

Hal · April 23, 2021, 12:07pm

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)

binaryanomaly · April 23, 2021, 7:00pm

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      |
+----------------------------+------------------------+

Topic		Replies	Views
Unable to update from v4.2.1 to 4.3.1 or 4.3.2 Developer help	6	1201	April 23, 2021
SOLVED - Ghost boot error - Error occurred while executing the following migration: 02-add-members-products-table.js Installation	2	1081	June 10, 2022
Unable to upgrade from 3.42.4 to 4.1.2 Installation	1	571	April 15, 2021
Trouble upgrading to 4.3.3 Installation	5	1039	May 10, 2021
Upgrading v4.10.2 to v4.11.0 error Developer help	8	965	September 20, 2021

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

Related topics