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

Developer help
42

Yes sir, I did upgrade to 20.04 LTS. Sounds like I am not alone in doing that upgrade not fully understanding the implications of that upgrade process on database compatibility for Ghost. It would be great to get a fix to make whatever database changes are necessary and make the upgrade possible for those of us that find ourselves in this predicament.

1 Like
43

I appreciate the Ghost staff so much. Thank you!

1 Like
44

Debian / Ubuntu upgrades can have issues with databases, they sometimes show up in Ghost for me, esp. the switch from mysql to MariaDB as the default provider for MySQL.

There was an issue for me in a previous Ghost upgrade, July 2020, where the problem was actually the innodb_default_row_format which previously defaulted to compact and had to be set to dynamic for Ghost to be a happy-chappy.

In that case, my recipie was to:

  1. create a CNF file to set the MariaDB required config: e.g. /etc/mysql/conf.d/mysql_custom.cnf containing innodb_default_row_format = dynamic
  2. sudo systemctl restart mariadb
  3. echo 'use my-ghost-database;show table status' | sudo mysql - this showed that many tables had Row_format compact. (FYI it also shows Collation for all tables)
  4. sudo mysqlcheck -o my-ghost-database
  5. echo 'use my-ghost-database;show table status' | sudo mysql - this showed tables now have Row_format dynamic. (FYI it also shows Collation for all tables)
  6. ghost upgrade

The key points are:

  1. show table status is great for checking ALL tables in a database
  2. mysqlcheck -o database-name is useful to repair and fix general database upgrade issues
  3. You should have a backup, just in case!
45

oh - and once I had verified it worked, I used sudo mysqlcheck -o –all-databases to do all the other Ghost blog databases on the same server.

46

One more questions. For the future. I don’t want this happen again, so should I do next:

  1. Make backup before major updates
  2. Never update to new major Ubuntu version immediately and wait (check this forum)
  3. If Ubuntu or MySQL is updated, verify that charset and collation is correct by running:

ALTER SCHEMA ghostdb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

  1. Now update CLI and Ghost.

Is this enough? Aaand thanks a lot for the help.

1 Like
47

IMHO:

  1. Yes to backups! You should be taking backups of your databases regularly, and SQL formats are best because they can be loaded into a newer version of MySQL/MariaDB. Here’s an old post on this: Best practice for backup database and all content - #2 by jeff. There may be a more modern way to do this.
  2. Ubuntu and Debian upgrades are usually pretty safe when they hit production - there are always notes to look at, and MySQL/MariaDB always need a check afterwards. I run the upgrades early on test servers, and about a month after release dates in production. I am already running the next version on my development boxes, which is Debian pre-release Alpha3 aka bullseye, expected out this June.
  3. Once you have issued your ALTER SCHEMA you have made the change permanent so you won’t need to do that next time.

The only additional thing is to run mysqlcheck -o from time to time - maybe every quarter, when there are no updates being performed, as part of your database health maintenance.

:slight_smile:

4 Likes
48

I have shell scripts to automate database backups, but I guess the downside of SQL format backups in a case like this is that they will also back up the table collation settings. If you want your database to use the default collation settings for MySQL 8 it might be better to export all content in JSON format from the Ghost Settings > Labs page and reimport those into a fresh install.

1 Like
49

The export is not a backup and won’t contain all data from your Ghost site, notably members stats and email history among other “sensitive” settings will be missing. It’s useful for migrating content between instances but isn’t the right tool for a full backup.

If you have an SQL file you can always do a find+replace on it to replace the collation if needed before pulling into a fresh database.

1 Like
50

Aha, good to know.

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.