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

Developer help
51

Yes to 0 and 1, but running 2 does not solve the problem of updating from 4.2.2 to 4.3.2. At least it didn’t for me.

52

Did you run a table optimise to rebuild each table with the updated collation?
e.g. sudo mysqlcheck -o my_ghost_database

This recreates each table, copies data from the original, fixes minor corruption issues, switches to the new table and finally removes the old original.

53

Yes, I did. I re-ran that again to confirm. Ghost fails to upgrade, with the same error.

Row_format is Dynamic
Collation utf8mb4_general_ci

54

I have the same problem. Self-hosting on DigitalOcean, using the Ghost marketplace image. What led to the problem was

  1. upgrading from Ubuntu 18.04 to Ubuntu 20.04 (which also upgrades MySQL from 5 to 8) a couple of weeks ago,
  2. running ghost update today from 4.1.2 to 4.3.3, which was showing the error in the original post above, but then NOT downgrading when prompted, as I should have. I tried various fixes above and just caused further mess.

I have tried, in order:

  • Restoring my droplet to a backup a couple of days ago (Ubuntu already upgraded, Ghost wasn’t).
  • Updating packages and ghost-cli.
  • In MySQL, I checked with SELECT @@character_set_database, @@collation_database; and the default character set was indeed latin1 and default collation latin1_swedish_ci.
  • I changed the default collation with ALTER SCHEMA ghost_production DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
  • I have tried ghost update, and ghost failed to start with the same issue, copied below. I have tried ghost update 4.1.2 --force to reinstall the same version, reinstall binary dependencies… that was fine (Ghost still working) but upgrading to 4.3.3 didn’t work.

The problem seems to be that the table members_products doesn’t exist and therefore can’t be altered?

Full error message:

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

Debug Information:
    OS: Ubuntu, v20.04.2 LTS
    Node Version: v14.16.1
    Ghost Version: 4.3.3
    Ghost-CLI Version: 1.16.3
    Environment: production
    Command: 'ghost update'
55

The tables are still incompatible. You’ve changed the default, which should fix newly created tables, but not fixed the existing tables as far as I can tell?

Try running:

sudo mysqlcheck -o my_ghost_database

56

OK I think I got it ! It drove me crazy for a couple of hours. Like many people here I was using a Digital Ocean droplet, it updated to Ubuntu 20, MySQL updated to 8 without asking.

I changed the schema default character set and default collate to utf8mb4 and utf8mb4_general_ci like suggested but it was still failing the update. I was almost going to give up and stay sad forever. Today I managed to fix this without tricks.

What is happening

MySQL changed to version 8. One of the changes is that the default collate for character set utf8mb4 changed from utf8mb4_general_ci to utf8mb4_0900_ai_ci, so when you run a Ghost migration that adds a new table (like products) it is added with that charset, which is not compatible with utf8mb4_general_ci that was used previously on other tables.

As the documentation says here if only CHARACTER SET is specified then MySQL guesses COLLATE from its default value based on the CHARACTER SET default relation. And guess what Ghost is doing when initializing its db connection:

Screen Shot 2021-05-01 at 18.11.25
Screen Shot 2021-05-01 at 18.11.251404×372 49.2 KB

It sets a charset. So MySQL automatically picks utf8mb4_0900_ai_ci as collate value (it does not care about the database default one, it just uses the default COLLATE definition)

Fortunately, the MySQL team saw this coming and added a default_collation_for_utf8mb4 variable to mysql options that can only take 2 values ; utf8mb4_0900_ai_ci (the new default) or utf8mb4_general_ci (the old default, pre version 8)

I guess that if you check this variable on your server, it still says utf8mb4_0900_ai_ci.

select @@default_collation_for_utf8mb4;

How to fix

You just need to set this variable to utf8mb4_general_ci. Based on the MySQL config file options this is what I did:

  • edit /etc/mysql/conf.d/mysql.cnf which is your MySQL options file
  • add this code:
[mysqld]
init-connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'
  • save
  • run service mysql restart (it should restart MySQL with new config without warning)
  • go to your Ghost folder and run ghost update, it should go well

Just so you know :warning:

init-connect is ran for all users except admin users (for debugging purpose) so if you change the config and open (as root) your mysql terminal and run

select @@default_collation_for_utf8mb4;

it will still be utf8mb4_0900_ai_ci ! because you are an admin user. But at least for the Ghost script it will be OK. I hope this will help some of you.

11 Likes
57

I tried to follow this but without any luck… I am using official docker image. When I exec into mariadb docker container I find only /etc/mysql/conf.d/docker.cnf. I added there the required line but it had no effect and still encountering the same error message (I persisted the config and restarted the container). Any suggestions for the docker users please?
Thanks, Michal

58

Unfortunately I don’t know much more than my current configuration which is the official DigitalOcean Ghost app.

It seamed to me that the official Ghost Docker image uses SQLite, not MySQL?

In any cases if you have this error it means something updated compared to a couple of weeks ago, most probably your MySQL version (but if it was frozen in a Docker image it should not have been changed?). So maybe the fix I suggested did not work/get passed as expected to the container (maybe the documentation can help understand why) or maybe it’s easier to revert your MySQL version in your Docker image to previous version? Sorry I am not a great help on this one !

59

Well for me this means that I am down… I cant make current version work with my existing database and I cannot rollback to the previous version 4.2.2. All earlier 4.x versions went missing from the dockerhub… Anybody from @staff can help?

60

Merci @jebarjonet, it worked perfectly!

61

Thank you. this worked for me.

62

Works for me as well. Thank you @jebarjonet

63

Hi, now I am getting the following error:

Ghost container:
ERROR SELECT 1+1 as RESULT; - ER_NEW_ABORTING_CONNECTION: Aborted connection 3 to db: 'ghost' user: 'ghost' host: 'ghost.522.ghost.522' (init_connect command failed)

Mysql container:

ghost.522.mariadb | 2021-05-08 23:27:32 3 [Warning] Aborted connection 3 to db: 'ghost' user: 'ghost' host: 'ghost.522.ghost.522' (init_connect command failed)
ghost.522.mariadb | 2021-05-08 23:27:32 3 [Warning] Unknown system variable 'default_collation_for_utf8mb4'
ghost.522.mariadb | 2021-05-08 23:27:34 4 [Warning] Aborted connection 4 to db: 'ghost' user: 'ghost' host: 'ghost.522.ghost.522' (init_connect command failed)
ghost.522.mariadb | 2021-05-08 23:27:34 4 [Warning] Unknown system variable 'default_collation_for_utf8mb4'
ghost.522.mariadb | 2021-05-08 23:27:36 5 [Warning] Aborted connection 5 to db: 'ghost' user: 'ghost' host: 'ghost.522.ghost.522' (init_connect command failed)
ghost.522.mariadb | 2021-05-08 23:27:36 5 [Warning] Unknown system variable 'default_collation_for_utf8mb4'

Anybody? Thanks

64

Thanks a lot @jebarjonet !

Shouldn’t ghost be made compatible with the new MySQL 8.0 default collation utf8mb4_0900_ai_ci ?

65

It is compatible with utf8mb4_0900_ai_ci. This issue is a database administration issue, Ghost is not involved. Incompatibility comes from newly created tables (from Ghost or not) with Ubuntu 20, with the previous ones before the DO update, with Ubuntu 18. If you create a new droplet, update it immediately then run Ghost for the first time, everything will be utf8mb4_0900_ai_ci and run smoothly

66

Cool

67

Thank you!! This was exactly the tip that I needed! I was nearing reinstall territory and this saved me the trouble. Funny how simple it turned out to be in the end. Kudos @jebarjonet!!!

68

I have the same issue upgrading from 4.12.1 to 4.16.0.
I Use cloud server from Hetzner.de

Message: Ghost was able to start, but errored during boot with: alter table members_product_events add constraint members_product_events_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_product_events_member_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 02-add-members-products-events-table.js

Debug Information:
OS: Ubuntu, v20.04.3 LTS
Node Version: v14.17.6
Ghost Version: 4.16.0
Ghost-CLI Version: 1.17.3
Environment: production
Command: ‘ghost start’

I am really frustrated because I haven’t done any backup and my website hasn’t been working for several days already.

Any help is appreciated.

Thank you.

69

Incredible work. Thanks!

70

You saved my day. Thanks.