Ghost migration fails - How to migrate the default collation from MySQL 5 to MySQL 8
How to migrate the Ghost MySQL database collation from MySQL 5.7 default to MySQL 8.0 default.
I am running this blog with Ghost on Docker.
Normally the update is done automatically when a new image is available and it works flawlessly all the time. Except for that one time.
For Google and people who search for this here is the error message I got:
root@webspace:~# docker logs -f blog_ghost_1
[2022-02-08 05:47:09] INFO Ghost is running in production...
[2022-02-08 05:47:09] INFO Your site is now available on https://www.ajfriesen.com/
[2022-02-08 05:47:09] INFO Ctrl+C to shut down
[2022-02-08 05:47:09] INFO Ghost server started in 0.751s
[2022-02-08 05:47:09] WARN Database state requires migration.
[2022-02-08 05:47:10] INFO Creating database backup
[2022-02-08 05:47:10] INFO Database backup written to: /var/lib/ghost/content/data/ajfriesen.ghost.2022-02-08-05-47-10.json
[2022-02-08 05:47:10] INFO Running migrations.
[2022-02-08 05:47:10] INFO Adding table: posts_products
[2022-02-08 05:47:10] INFO Rolling back: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible..
[2022-02-08 05:47:10] INFO Dropping table: posts_products
[2022-02-08 05:47:10] INFO Rollback was successful.
[2022-02-08 05:47:10] ERROR alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
{"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
"Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"
Error ID:
300
Error Code:
UNKNOWN_CODE_PLEASE_REPORT
----------------------------------------
MigrationScriptError: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.35.0/core/server/data/db/state-manager.js:95:32)
at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:7:26)
at new MigrationScriptError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:25:26)
at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/index.js:1032:19
at processTicksAndRejections (internal/process/task_queues.js:95:5)
Error: UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
at Query.Sequence._packetToError (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:88:28)
at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (events.js:400:28)
at addChunk (internal/streams/readable.js:293:12)
at readableAddChunk (internal/streams/readable.js:267:9)
at Socket.Readable.push (internal/streams/readable.js:206:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
--------------------
at Protocol._enqueue (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:198:25)
at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:134:18
at new Promise (<anonymous>)
at Client_MySQL._query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:128:12)
at Client_MySQL.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:168:17)
at Runner.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
at Runner.queryArray (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
at processTicksAndRejections (internal/process/task_queues.js:95:5)
at async /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:18
at async Object.up (/var/lib/ghost/versions/4.35.0/core/server/data/migrations/utils.js:257:13)
[2022-02-08 05:47:10] WARN Ghost is shutting down
[2022-02-08 05:47:10] WARN Ghost has shut down
[2022-02-08 05:47:10] WARN Your site is now offline
[2022-02-08 05:47:10] WARN Ghost was running for a few seconds
[2022-02-08 05:47:12] INFO Ghost is running in production...
[2022-02-08 05:47:12] INFO Your site is now available on https://www.ajfriesen.com/
[2022-02-08 05:47:12] INFO Ctrl+C to shut down
[2022-02-08 05:47:12] INFO Ghost server started in 0.635s
[2022-02-08 05:47:12] WARN Database state requires migration.
[2022-02-08 05:47:13] INFO Creating database backup
[2022-02-08 05:47:13] INFO Database backup written to: /var/lib/ghost/content/data/ajfriesen.ghost.2022-02-08-05-47-13.json
[2022-02-08 05:47:13] INFO Running migrations.
[2022-02-08 05:47:13] INFO Adding table: posts_products
[2022-02-08 05:47:13] INFO Rolling back: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible..
[2022-02-08 05:47:13] INFO Dropping table: posts_products
[2022-02-08 05:47:13] INFO Rollback was successful.
[2022-02-08 05:47:13] ERROR alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
{"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
"Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"
Error ID:
300
Error Code:
UNKNOWN_CODE_PLEASE_REPORT
----------------------------------------
MigrationScriptError: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.35.0/core/server/data/db/state-manager.js:95:32)
at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:7:26)
at new MigrationScriptError (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/errors.js:25:26)
at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/lib/index.js:1032:19
at processTicksAndRejections (internal/process/task_queues.js:95:5)
Error: UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
at Query.Sequence._packetToError (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:88:28)
at Socket.<anonymous> (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (events.js:400:28)
at addChunk (internal/streams/readable.js:293:12)
at readableAddChunk (internal/streams/readable.js:267:9)
at Socket.Readable.push (internal/streams/readable.js:206:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
--------------------
at Protocol._enqueue (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/var/lib/ghost/versions/4.35.0/node_modules/mysql/lib/Connection.js:198:25)
at /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:134:18
at new Promise (<anonymous>)
at Client_MySQL._query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:128:12)
at Client_MySQL.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:168:17)
at Runner.query (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
at Runner.queryArray (/var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
at processTicksAndRejections (internal/process/task_queues.js:95:5)
at async /var/lib/ghost/versions/4.35.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:18
at async Object.up (/var/lib/ghost/versions/4.35.0/core/server/data/migrations/utils.js:257:13)
[2022-02-08 05:47:13] WARN Ghost is shutting down
[2022-02-08 05:47:13] WARN Ghost has shut down
[2022-02-08 05:47:13] WARN Your site is now offline
[2022-02-08 05:47:13] WARN Ghost was running for a few seconds
I had no clue and time at that evening and just ran with the old version for a few days. After a few days, a few people in GitHub and the ghost forum were adding some more light in the dark.
Apparently, this is NOT an issue with Ghost. This is an issue with MySQL. So the inner database admin has to take care of this.
Actual Problem
The problem only occurs when you started your Ghost journey with MySQL version 5.x.
The default collation for charsetutf8mb4in MySQL 5.7 isutf8mb4_general_ci, but has been changed toutf8mb4_0900_ai_ciwith MySQL 8.0. See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_collation_for_utf8mb4.
For more details, see this GitHub issue.
People suggest changing the default collation in MySQL 8 to the old default of MySQL 5.7. It is a solution but I do want to use the sane default from MySQL 8. Especially because Ghost 5 will drop support for MySQL 5.7, that means all installations from then on should use MySQL 8 with the new default collation.
Instead I "migrated" my database from utf8mb4_general_ci to utf8mb4_0900_ai_ci and here is my process.
Solution
For docker users like me here are the steps from my shell history and memory. If you are running Ghost in a different way this should be almost the same, since we only interact with the MySQL container and not Ghost itself. You would just adjust the MySQL command to your database setup without the docker complexity.
High level:
- Create a SQL dump of your ghost database
- create a copy of that dump file
- Replace occurrences of
utf8mb4_general_ciwithutf8mb4_0900_ai_cised -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' new-db.sql - Drop the ghost database
- Create a new ghost database
- Import the new-db.sql dump
Actual solution for ghost docker setups
Create a folder where you put in your backups
mkdir /backupsCheck which docker network you have and your containers are running in:
docker network ls
docker inspect <mysql-container-name>Create a MySQL dump (backup)
docker run --rm --name mysql-dump --network blog_docker-network mysql:8 mysqldump -h db -u root -p<your-password> --databases ghost > /backup/backup-ghost-db-dump.sqlCreate a copy and replace utf8mb4_general_ci with utf8mb4_0900_ai_ci:
cp /backup/backup-ghost-db-dump.sql /backup/new-backup-ghost-db-dump.sql
sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' /backup/new-backup-ghost-db-dump.sqlYou can run a diff between those files to make sure what has been changed:
diff /backup/backup-ghost-db-dump.sql /backup/new-backup-ghost-db-dump.sqlRun a MySQL container to interact with the database and mount the /backup directory to have access to your dumps
docker run -it --rm --name mysql-restore -v /backup:/restore --network blog_docker-network mysql:8 /bin/bashConnect to your database. db is the hostname of the default docker-compose setup. Adjust to if you did changes there.
mysql -h db -u root -p<your-password>Now you are in a MySQL shell and drop and create the database
DROP DATABASE ghost;
CREATE DATABASE ghost;
Now get out of the MySQL shell but stay in the mysql docker container and make a restore from the new database:
mysql -h db -u root -p<your-password> ghost < /restore/new-backup-ghost-db-dump.sqlYour site should be up and running again.
Now you can start the migration and everything works fine again. That is at least what I did.
I hope that helps!
