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 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'
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:
[mysqld] init-connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'
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.
Works for me as well. Thank you @jebarjonet
Hi, now I am getting the following error:
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)
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'
Thanks a lot @jebarjonet !
Shouldn’t ghost be made compatible with the new MySQL 8.0 default collation utf8mb4_0900_ai_ci ?
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
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!!!