This blog runs on Ghost. Version 4.17 since a couple of hours ago. Version 2.16 a couple of days ago.
I expected the upgrade over two major versions to be difficult, but not exactly in this way. The big problem was not Ghost itself but a bunch of database settings that prevented the migrations to run properly.
I tried upgrading in steps (2.16 -> 2.36 -> 3.0 -> 4.0 -> 4.17) but it wound up being a confused path over multiple versions of both 3 and 4. However, choosing different versions or steps wouldn't have helped. Fixing the database will.
Always start by making sure there are backups before you start upgrading. I always make sure there's both a database backup and a json export of the contents (that's under Labs for some reason). Also, don't forget to backup your uploads.
Problems - and their solutions
Wrong character set and collation
Ghost uses utf8mb4 charset and utf8mb4_general_ci collation.
I don't know if this is a problem that only affects Swedes who are running on a MySql or MariaDB installed separately, but the ghost db in my MariaDB was charset latin1 and collation latin1_swedish_ci.
Fix
ALTER DATABASE ghost CHARACTER SET='utf8mb4' COLLATE='utf8mb4_general_ci';
Index column size too large
Another quite ubiquitous error when upgrading Ghost is this:
"OuterError: alter table `members_stripe_customers` add unique `members_stripe_customers_customer_id_unique`(`customer_id`) - ER_INDEX_COLUMN_TOO_LONG: Index column size too large. The maximum column size is 767 bytes"Fix
Make sure your DB has the correct settings for tables. In my.cnf:
[mysqld]
innodb_file_per_table=ON
innodb_file_format=Barracuda
innodb_large_prefix=ON
# The next setting is optional, it ensures future tables will support indexes of a larger size
innodb_default_row_format = 'DYNAMIC'If you run MariaDB in docker, most of these can be fixed with env variables.
However, changing these won't fix existing databases. Check if your ghost database has problems (change db name if needed):
SELECT TABLE_NAME, ROW_FORMAT FROM information_schema.TABLES WHERE TABLE_SCHEMA="$DB_NAME" AND ROW_FORMAT != 'DYNAMIC' AND ROW_FORMAT != 'COMPRESSED';If you get any results, those tables need fixing:
USE information_schema;
SELECT CONCAT("ALTER TABLE `", TABLE_SCHEMA, "`.`", TABLE_NAME, "` ROW_FORMAT=DYNAMIC;") AS MySQLCMD FROM TABLES WHERE ENGINE='innodb' AND ROW_FORMAT != 'DYNAMIC' AND ROW_FORMAT != 'COMPRESSED' AND TABLE_SCHEMA="$DB_NAME";This generates a list of commands, one for each table, that you need to manually copy and paste and run.
When you're done, the migrations will run and your Ghost installation will be upgraded automatically.