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.
I appreciate the Ghost staff so much. Thank you!
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:
- create a CNF file to set the MariaDB required config: e.g.
innodb_default_row_format = dynamic
sudo systemctl restart mariadb
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)
sudo mysqlcheck -o my-ghost-database
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)
The key points are:
show table statusis great for checking ALL tables in a database
mysqlcheck -o database-nameis useful to repair and fix general database upgrade issues
- You should have a backup, just in case!
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.
One more questions. For the future. I don’t want this happen again, so should I do next:
- Make backup before major updates
- Never update to new major Ubuntu version immediately and wait (check this forum)
- If Ubuntu or MySQL is updated, verify that charset and collation is correct by running:
ghostdbDEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
- Now update CLI and Ghost.
Is this enough? Aaand thanks a lot for the help.
- 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.
- 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.
- 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.
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.
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.
Aha, good to know.
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.
Did you run a table optimise to rebuild each table with the updated collation?
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.
Yes, I did. I re-ran that again to confirm. Ghost fails to upgrade, with the same error.
Row_format is Dynamic
I have the same problem. Self-hosting on DigitalOcean, using the Ghost marketplace image. What led to the problem was
- upgrading from Ubuntu 18.04 to Ubuntu 20.04 (which also upgrades MySQL from 5 to 8) a couple of weeks ago,
- 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
latin1and default collation
- 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 --forceto 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
OS: Ubuntu, v20.04.2 LTS
Node Version: v14.16.1
Ghost Version: 4.3.3
Ghost-CLI Version: 1.16.3
Command: 'ghost update'
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?
sudo mysqlcheck -o my_ghost_database
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_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.
MySQL changed to version 8. One of the changes is that the default collate for character set
utf8mb4 changed from
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:
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
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
You just need to set this variable to
utf8mb4_general_ci. Based on the MySQL config file options this is what I did:
/etc/mysql/conf.d/mysql.cnfwhich is your MySQL options file
- add this code:
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
init-connect is ran for all users except admin users (for debugging purpose) so if you change the config and open (as
mysql terminal and run
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.
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?
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 !
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?
Merci @jebarjonet, it worked perfectly!
Thank you. this worked for me.