Ghost: Foreign key constraint is incorrectly formed - upgrade 3.5.0 to 3.6.0

[johngoggs]

### Issue Summary

When upgrading my Ghost installation from 3.5.0 to 3.6.0, I receive the below error, causing my upgrade to fail. I am getting this error in both my production environment and development environment.

It is worth noting this database was created from version 2 and had content imported via the import process, but using the older import functionality.

Production Environment Details

My production environment runs in Cloud Run (GCP) and connects to a MySQL 5.7 database in Cloud SQL (GCP). With Cloud Run, you need to containerise Ghost for it to run correctly. To do this, I set up a Ghost installation using npm, not ghost-cli.

To Reproduce

I've tried to reproduce this error from a fresh installation using npm. However, I cannot replicate the problem. When I use a clean Ghost installation, I use my production database, from GCP, instead of the one from the clean installation. I then run Ghost 3.6.0 using npm start, but it fails to run due to the database error.

When replicating the issue I've done this on my PC using the Xampp stack. Xampp uses MariaDB 10.1.38.

1. Using npm, install Ghost 3.5.0
2. Run 3.5.0 and set up an admin account
3. Using npm, upgrade Ghost to 3.6.0
4. Run the upgrade which will create new tables

Because I thought it is linked to my database, I've tried to change the collation on the tables and database from utf8 to utf8mb4, to no avail.

Any other info, e.g. Why do you consider this to be a bug? What did you expect to happen instead?

I expect the Ghost 3.6.0 installation to create the necessary tables and to continue to run.

Was this an issue in 3.5.2 and was it supposed to be fixed in this version?

Technical details:

• Ghost Version: 3.6.0
• Node Version: 10.x.x
• Browser/OS: Chrome 80.0.3987.106
• Database: MariaDB 10.1.38/MySQL 5.7

> node index

[2020-02-16 15:03:04] INFO Blog is in maintenance mode.
[2020-02-16 15:03:04] INFO Ghost is running in development...
[2020-02-16 15:03:04] INFO Listening on: 0.0.0.0:8080
[2020-02-16 15:03:04] INFO Url configured as: http://localhost:8080/
[2020-02-16 15:03:04] INFO Ctrl+C to shut down
[2020-02-16 15:03:04] INFO Ghost boot 3.988s
[2020-02-16 15:03:04] INFO Creating database backup
[2020-02-16 15:03:05] INFO Database backup written to: <windows_file_path>\ghost.2020-02-16-15-03-04.json
[2020-02-16 15:03:05] INFO Adding table: labels
[2020-02-16 15:03:05] INFO Adding table: members_labels
[2020-02-16 15:03:05] INFO Dropping table: members_labels
[2020-02-16 15:03:05] INFO Dropping table: labels
[2020-02-16 15:03:05] ERROR alter table `members_labels` add constraint `members_labels_member_id_foreign` foreign key (`member_id`) references `posts` (`id`) - ER_CANT_CREATE_TABLE: Can't create table `ghost_350_to_360`.`#sql-1890_40` (errno: 150 "Foreign key constraint is incorrectly formed")

alter table `members_labels` add constraint `members_labels_member_id_foreign` foreign key (`member_id`) references `posts` (`id`) - ER_CANT_CREATE_TABLE: Can't create table `ghost_350_to_360`.`#sql-1890_40` (errno: 150 "Foreign key constraint is incorrectly formed")

{"name":"2-add-members-labels-table.js"}
"Error occurred while executing the following migration: 2-add-members-labels-table.js"

Error ID:
    300

Error Code: 
    ER_CANT_CREATE_TABLE

[kevinansfield]

@meapleio where is this SQL query coming from: ER_CANT_CREATE_TABLE: Can't create tableghost_350_to_360`?

To the best of my knowledge Ghost does not create any such tables during migration.

[johngoggs]

@kevinansfield The tables will be required in version 3.6.0, hence why it is creating them during server start. May be migration was the incorrect term here?

From what I understand, as part of version 3.6.0, two new tables are required, labels and members_labels. When starting the 3.6.0 server, Ghost will be checking the tables, and if it is missing any, it will run the migrations to create them and to insert the correct tables and permissions.

Under core/server/data/migrations/versions/3.6 there are three js file, one to add the label table, one to add the member_label table and one to apply permissions.

Below is a screenshot of the tables for version 3.5.0.

The following is the terminal output of the new tables when Ghost 3.6.0 starts.

> node index

[2020-02-17 20:55:38] INFO Blog is in maintenance mode.
[2020-02-17 20:55:38] INFO Ghost is running in development...
[2020-02-17 20:55:38] INFO Listening on: 0.0.0.0:8080
[2020-02-17 20:55:38] INFO Url configured as: http://localhost:8080/
[2020-02-17 20:55:38] INFO Ctrl+C to shut down
[2020-02-17 20:55:38] INFO Ghost boot 4.279s
[2020-02-17 20:55:38] INFO Creating database backup
[2020-02-17 20:55:38] INFO Database backup written to: <windows-file-path>\content\data\.ghost.2020-02-17-20-55-38.json
[2020-02-17 20:55:38] INFO Adding table: labels
[2020-02-17 20:55:38] INFO Adding table: members_labels
[2020-02-17 20:55:39] INFO Adding permissions fixtures for labels
[2020-02-17 20:55:39] INFO Adding permissions_roles fixtures for labels
[2020-02-17 20:55:39] INFO Blog is out of maintenance mode.

Below is a screenshot confirming the tables in the database on version 3.6.0.

The information in this post is gathered from clean installations of Ghost.

[rishabhgrg]

Hey @meapleio , version 3.6 did have a schema bug causing the foreign key constraint error, we have released 3.7 which should fix this. Please upgrade directly to latest version and this should be resolved.

[johngoggs]

@rishabhgrg Thanks. I can confirm I have upgraded to 3.7.0 successfully.

[vanquang9387]

Hi @rishabhgrg @meapleio I'm facing this issue. Can you please tell me how to "upgrade directly"?
I haven't used ghost for a while.

$ ghost update 3.7.0
.
.
.
✔ Linking latest Ghost and recording versions
✖ Restarting Ghost
A GhostError occurred.

Message: alter table `members_labels` add constraint `members_labels_member_id_foreign` foreign key (`member_id`) references `members` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_labels_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 2-add-members-labels-table.js
Suggestion: journalctl -u ghost_blog-quangnv-com -n 50

Debug Information:
    OS: Ubuntu, v20.04 LTS
    Node Version: v12.16.2
    Ghost Version: 3.7.0
    Ghost-CLI Version: 1.13.1
    Environment: production
    Command: 'ghost update 3.7.0'

Additional log info available in: /home/.../.ghost/logs/ghost-cli-debug-2020-04-24T09_13_00_768Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/api/ghost-cli/ for troubleshooting.



? Unable to upgrade Ghost from v3.5.0 to v3.7.0. Would you like to revert back to v3.5.0? Yes

[scientistnik]

I have same problem:

Debug Information:
    OS: Ubuntu, v20.04 LTS
    Node Version: v12.16.2
    Ghost Version: 3.13.4
    Ghost-CLI Version: 1.13.1
    Environment: production
    Command: 'ghost start'
Message: alter table `members_labels` add constraint `members_labels_member_id_foreign` foreign key (`member_id`) references `members` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_labels_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 2-add-members-labels-table.js

[nik0sc]

I had this problem while upgrading from 3.1 directly to 3.13. I used the official Docker images for 3.1 and 3.13 as well as MySQL 8.0 for the backing database. After poking around in the database I realised that the collations for members_labels.member_id and members.id were different. Both columns are VARCHAR(24) but one of them had collation utf8mb4_0900_ai_ci and the other had utf8mb4_unicode_ci. Changing the collations for both to utf8mb4_0900_ai_ci fixed the migration error and I was able to upgrade to 3.13 normally.

I've been running Ghost since the 1.x days with MySQL 5.7. So I suspect (can't confirm) that what happened is when upgrading from MySQL 5.7 to 8.0, the database collation got changed from utf8mb4_unicode_ci to utf8mb4_0900_ai_ci. Older versions of Ghost used the default utf8mb4_unicode_ci when creating members.id, but the newer versions use utf8mb4_0900_ai_ci when creating members_labels.member_id. Fast forward to Ghost 3.6, when the foreign key relation is set up, which fails because of this.

I tried to change all text columns in the database to use utf8mb4_0900_ai_ci, but this fails due to constraints in the database. So I suspect again that in a future Ghost migration, this house of cards will fall and I'll be forced to export all my content, drop the database, import and hope for the best...

[huuquynh]

I tried to upgrade from 3.28.0 to 3.29.0, and got the same error. Could not upgrade direct to latest version.

[2020-08-28 16:49:18] WARN Skipping ON DELETE CASCADE for "members_labels_member_id_foreign" constraint - already set
[2020-08-28 16:49:18] WARN Skipping ON DELETE CASCADE for "members_labels_label_id_foreign" constraint - already set
[2020-08-28 16:49:18] WARN Skipping "members_stripe_customers_member_id_foreign" foreign key constraint creation - already exists
[2020-08-28 16:49:18] WARN Skipping "members_stripe_customers_customer_id_unique" index creation - already exists
[2020-08-28 16:49:18] WARN Skipping "members_stripe_customers_subscriptions_subscription_id_unique" index creation - already exists
[2020-08-28 16:49:18] INFO Adding "members_stripe_customers_subscriptions_customer_id_foreign" foreign key constraint
[2020-08-28 16:49:18] WARN Removing permission(Read member signin urls) from role(Administrator) - already removed
[2020-08-28 16:49:18] ERROR

NAME: MigrationScriptError
CODE: ER_CANT_CREATE_TABLE
MESSAGE: alter table `members_stripe_customers_subscriptions` add constraint `members_stripe_customers_subscriptions_customer_id_foreign` foreign key (`customer_id`) references `members_stripe_customers` (`customer_id`) on delete CASCADE - ER_CANT_CREATE_TABLE: Can't create table `blog`.`#sql-532_1b7999` (errno: 150 "Foreign key constraint is incorrectly formed")