Unable to update Ghost 3.x becase of a a schema bug in 3.6

Hi there,
I’m trying to update Ghost 3.5 to 3.7 (or later) but there is an migration error.

$ ghost update 3.7.0 --force
✔ Checking for latest Ghost version
✔ Downloading and updating Ghost to v3.7.0
+ sudo systemctl stop ghost_blog-quangnv-com
✔ Stopping Ghost
✔ Linking latest Ghost and recording versions
+ sudo systemctl start ghost_blog-quangnv-com
+ sudo systemctl stop ghost_blog-quangnv-com
✖ 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 -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 --force'

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

I found a related issue on GitHub

@rishabhgrg said:

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.

But I don’t know how to do a “direct upgrade”. Please help.
(I haven’t used Ghost for a long time).

I’ve had same error on migrating.

The last comment of the issue resolved that in my environment.


In my case, that might occur because I also upgraded MySQL version to 8.0 just before I upgraded Ghost. This might change default collation of the server.

So I changed the default collation and default character set of the server by setting collation_server and character_set_server of my.cnf to values used on previous MySQL version, then Ghost migration script worked.

1 Like

Thank @mokjpn
I want to keep default Mysql config.
So I changed members.id collation to utf8mb4_0900_ai_ci then Ghost migration script worked.

I know next to nothing about SQL. Can someone write instructions for how to solve the issue? My blog is kaput until this issue is solved. Thanks.

Did you get the exact error that I posted?

Yes, that’s the error that I received.

Anyone have the incantation to fix this?

If your MySQLversion is 8.0 then continue.

There are 2 solutions:

  1. As @mokjpn said (which seems better)

changed the default collation and default character set of the server by setting collation_server and character_set_server of my.cnf to values used on previous MySQL version

  1. What I did

changed members.id collation to utf8mb4_0900_ai_ci

And this is my script:

  1. Login to your mysql server
mysql -u <your db user>  -p <your db name>
Enter your db password
  1. Change collation and character set of members.id column
ALTER TABLE `members` CHANGE `id` `id` VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

But I must say this is a temporary fix, similar problem may occur again later.

Thank you! Using this I was able to get back online.