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

Developer help
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