ghost

Ghost v5 upgrade with MySQL 8 collation migration, in Docker compose

Michael Friedrich

Jun 1, 2022 • 8 min read

Ghost v5 has been released, the underlying stack for this website, running in containers. In this blog post, I'll look into the upgrade to the latest v4, MySQL8 collation changes, database migrations, and the final v5 upgrade.

There are a few required steps for v5:

Upgrade to the latest v4 version
Upgrade to MySQL 8 (already done in my setup)
Upgrade to v5

Looks straightforward, but unfortunately ...

Upgrade to latest v4 failed

In order to upgrade to the latest v4 version, I’ve edited the docker-compose.yml file and changed the ghost server image to use the :4 tag.

$ vim docker-compose.yml

  ghost-server:
    image: ghost:4

$ docker-compose down
$ docker-compose up -d

The website did not come up, so I checked with docker ps seeing the ghost-server container restarted all the time. During the v4 upgrade, I had learned that Ghost thankfully runs DB migrations (and rollbacks!) using the CLI on startup, which can be triggered and visualized by running docker-compose up in the foreground on the terminal. If there are errors in daemon mode, causing the container to restart, it is also a good way to debug in production.

Stopped and started the server again, in the foreground.

docker-compose down
docker-compose up

Greeted me with DB migrations errors.

ghost-server_1  | [2022-06-01 11:49:45] ERROR alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost-server_1  |
ghost-server_1  | alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost-server_1  |
ghost-server_1  | {"config":{"transaction":false},"name":"2022-01-20-05-55-add-post-products-table.js"}
ghost-server_1  | "Error occurred while executing the following migration: 2022-01-20-05-55-add-post-products-table.js"
ghost-server_1  |
ghost-server_1  | Error ID:
ghost-server_1  |     300
ghost-server_1  |
ghost-server_1  | ----------------------------------------
ghost-server_1  |
ghost-server_1  | MigrationScriptError: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost-server_1  |     at DatabaseStateManager.makeReady (/var/lib/ghost/versions/4.48.1/core/server/data/db/state-manager.js:95:32)
ghost-server_1  |     at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.48.1/node_modules/knex-migrator/lib/errors.js:7:26)
ghost-server_1  |     at new MigrationScriptError (/var/lib/ghost/versions/4.48.1/node_modules/knex-migrator/lib/errors.js:25:26)
ghost-server_1  |     at /var/lib/ghost/versions/4.48.1/node_modules/knex-migrator/lib/index.js:1032:19
ghost-server_1  |     at processTicksAndRejections (internal/process/task_queues.js:95:5)
ghost-server_1  |
ghost-server_1  | Error: alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.
ghost-server_1  |     at Packet.asError (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/packets/packet.js:728:17)
ghost-server_1  |     at Query.execute (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/commands/command.js:29:26)
ghost-server_1  |     at Connection.handlePacket (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/connection.js:456:32)
ghost-server_1  |     at PacketParser.onPacket (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/connection.js:85:12)
ghost-server_1  |     at PacketParser.executeStart (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/packet_parser.js:75:16)
ghost-server_1  |     at Socket.<anonymous> (/var/lib/ghost/versions/4.48.1/node_modules/mysql2/lib/connection.js:92:25)
ghost-server_1  |     at Socket.emit (events.js:400:28)
ghost-server_1  |     at addChunk (internal/streams/readable.js:293:12)
ghost-server_1  |     at readableAddChunk (internal/streams/readable.js:267:9)
ghost-server_1  |     at Socket.Readable.push (internal/streams/readable.js:206:10)
ghost-server_1  |     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
ghost-server_1  |
ghost-server_1  | [2022-06-01 11:49:45] WARN Ghost is shutting down
ghost-server_1  | [2022-06-01 11:49:45] WARN Ghost has shut down
ghost-server_1  | [2022-06-01 11:49:45] WARN Your site is now offline
ghost-server_1  | [2022-06-01 11:49:45] WARN Ghost was running for a few seconds
ghost_ghost-server_1 exited with code 2

Copied the error message prefixed with ghost into Google search

ghost ERROR alter table `posts_products` add constraint `posts_products_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_products_post_id_foreign' are incompatible.

which led to this forum post, saying that Ghost 4.35 causes the problem. The comments pointed to a workaround too.

Before diving into it, I stopped fixing the production blog by pinning the image version at 4.34.4 and restarting the docker-compose stack.

$ vim docker-compose.yml

  ghost-server:
    image: ghost:4.34.3

$ docker-compose down && docker-compose up -d

Problem Analysis: Schema Collation in MySQL 8

The upgrade to MySQL 8 worked from a schema perspective, but the charset collation does not use the new default for utf8mb4, utf8mb4_0900_ai_ci instead of utf8mb4_general_ci. The general upgrade procedures for MySQL 8 suggest wide-range changes with deleting (foreign) keys and altering the table columns, this blog post dives deep into the adventures.

The SQL error only happens when you started with a MySQL 5.7 or earlier version and created the Ghost database.

Migrating the database to the new collation is required to stay safe in the future when Ghost developers want to take full advantage of MySQL 8, as has been announced in the v5 release post.

Ghost's official production stack is now Ubuntu 20, Node 16, and MySQL 8. The big change here is the database layer. We used to support both SQLite and MySQL 5 in production, now we're all-in on MySQL 8 so we can double down on performance optimizations. Ghost stores a hell of a lot more data than it used to, and this change is really important to keep the architecture stable.

MySQL 8 and Ghost 5

MySQL will become the new standard with Ghost, which means that migrating the database collection is the only path forward, compared to using old 5.7 compatible settings which might be dropped in future versions.

The migration requires an SQL DB dump, a copy where collations are replaced, and a manual review and drop/import in production.

MySQL Backup on the server

The directories follow the path for my blog setup, recommend adjusting as needed to create the backups directory.

$ cd /docker/ghost
$ mkdir backups
$ cd backups

Inspect the running database container and extract the network name on the CLI.

$ docker ps
$ docker network ls 
$ docker inspect ghost_ghost-db_1

Replace ghost-db with the database container name (defaults to db - I have modified the default docker-compose example to increase readability), and MYSQLROOTPASSWORDHERE in the command below. Run the database backup and download it locally.

$ docker run --rm --name mysql-dump --network ghost_default mysql:8 mysqldump -h ghost-db -u root -pMYSQLROOTPASSWORDHERE --databases ghost > backup-ghost-db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

The MySQL password warning is correct - make sure to clear the shell history after the upgrade.

Migration Steps

Copy the backup SQL file backup-ghost-db.sql into a new file backup-ghost-db-new.sql where all edits will be made. In case things go wrong, you can still re-import the backup, and revert back to 4.34.4.

$ cp backup-ghost-db.sql backup-ghost-db-new.sql 

$ vim backup-ghost-db-new.sql

Replace the collation with the following sed command, inline replacing the string patterns.

$ sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' backup-ghost-db-new.sql

Verify the changes between the original backup file and the newly updated collation by using the diff command.

$ diff -ur backup-ghost-db.sql backup-ghost-db-new.sql

Restore updated data from the backup

The database re-creation from the backup dump works as follows:

Use the default client/server collation utf8mb4_0900_ai_ci in MySQL 8
Create tables and columns, using the defined charset collation
Insert the datasets which inherit the new collation based on the database defaults, and table/column definition.

Ensure that the DB server is running and that you have a backup of the database dump (better do it again if not sure).

Run a separate container to load the DB dump, using the same credentials for the network ghost_default and MYSQLROOTPASSWORDHERE as before.

$ docker run -it --rm --name mysql-restore -v `pwd`:/restore --network ghost_default  mysql:8 /bin/bash 

root@9c6264142816:/# mysql -h ghost-db -u root -pMYSQLROOTPASSWORDHERE

Applying the changed schema won’t work because of foreign keys, only a full drop/create and import will work.

Drop, create the ghost database, then use ctrl+d to exit the MySQL shell. Connect to the database host ghost-db, replace MYSQLROOTPASSWORDHERE and import the modified backup SQL file.

mysql> DROP DATABASE ghost;
mysql> CREATE DATABASE ghost;

ctrl+d

$ mysql -h ghost-db -u root -pMYSQLROOTPASSWORDHERE ghost < /restore/backup-ghost-db-new.sql

Log into the database again to verify.

$ mysql -h ghost-db -u root -pMYSQLROOTPASSWORDHERE

mysql> show create database ghost\G
*************************** 1. row ***************************
       Database: ghost
Create Database: CREATE DATABASE `ghost` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)

mysql> use ghost;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create table products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `slug` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `description` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `monthly_price_id` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `yearly_price_id` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'paid',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `welcome_page_url` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Next, open the browser to verify that the website is running.

Ghost Upgrades

With the database migration being complete, the Ghost upgrade can actually begin.

Upgrade to latest Ghost v4

First, upgrade to the latest v4 Ghost version and let all pending database migrations run through.

$ vim docker-compose.yml

  ghost-server:
    image: ghost:4

$ docker-compose pull ghost-server
$ docker-compose down && docker-compose up -d

$ docker ps

CONTAINER ID   IMAGE     COMMAND                  CREATED          STATUS          PORTS                                       NAMES
562c01ff4295   ghost:4   "docker-entrypoint.s…"   57 seconds ago   Up 55 seconds   0.0.0.0:2368->2368/tcp, :::2368->2368/tcp   ghost_ghost-server_1
83c66d1cf003   mysql:8   "docker-entrypoint.s…"   57 seconds ago   Up 57 seconds   3306/tcp, 33060/tcp                         ghost_ghost-db_1

Upgrade to Ghost v5

Edit the docker-compose.yml file and change the image tag to :5. Pull the latest image, and start the container stack in foreground to verify the database migrations.

$ vim docker-compose.yml

  ghost-server:
    image: ghost:5

$ docker-compose pull ghost-server
$ docker-compose down && docker-compose up

Finally, daemonize the setup as service again.

docker-compose pull ghost-server
docker-compose down && docker-compose up -d

Attach to the running container, and check the version by printing the current symlink.

$ docker exec -ti ghost_ghost-server_1 bash

root@cf6e91ac93ea:/var/lib/ghost# ls -la current
lrwxrwxrwx 1 node node 29 Jun  1 00:25 current -> /var/lib/ghost/versions/5.1.1

Conclusion

Major version upgrades usually come with breaking changes, although the collation change is a breaking change in Ghost 4.5 already. I do hope that the detailed write-up helps everyone out there, running Ghost in containers and considering the v5 upgrade.

Kudos to Andrej Friesen for his detailed write-up which this blog post is based on for the MySQL8 migration parts.

I'm still considering getting a Ghost SaaS Pro plan and support the OSS project better; although the small container self-hosted setup and upgrades keep me busy and trained in the Ops area :-)

Resources: