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

Developer help Installation
1

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

3

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
4

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.

5

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.

6

Did you get the exact error that I posted?

7

Yes, that’s the error that I received.

8

Anyone have the incantation to fix this?

9

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.

10

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