Ghost + MySQL Upgrade

"Never do two changes at a time, especially on the production system," which I always tell anyone listening. But do and tell do not always come together. So if you consider upgrading your standalone Ghost to the latest releases, spend a few minutes on this post and save a day of struggle and pain.

I have pushed our little blog engine upgrade back as much as possible because the new major Ghost release does not support MySQL 5.x. Every system upgrade, even small as this one, is a project, but any database upgrade excites and dreads me equally. I had my decent share of database upgrades in the past, and I'm not happy to get back into this boat. The thing is, we do not always have a choice, don't we?

I started by googling up the MySQL 5.7 to 8.0 upgrade process. turns out it is just a "stop old - run new" situation when you have no compatibility issues. Better be safe than sorry. I downloaded and unpacked the latest available MySQL Community Edition Server and tried to run recommended upgrade validation procedure.  

# ./mysqlsh root:@localhost:3307 \ 
-e "util.checkForServerUpgrade();"

After a few seconds report gave me a few minor warnings, none of which impacted my ghost database.  I was good to go, and I went.  

The serve instance started as MySQL version 8, and after a few seconds, my database was available. I was rather excited about the success and running the Ghost upgrade.  It warned me that my theme had some compatibility issues, and upgraded the system. Naturally,  at the startup, it threw an infamous incompatible collation error, and the site went down. For a good part of Saturday, I tried to fix the situation and complete the schema upgrade.  I changed the collation and the default character set on the database and all Ghost tables. I upgrade the tables afterward.   I found a note from the Ghost team and decided to redo the database completely, then import the corrected MySQL dump file. Nothing worked. Finally, I gave up, rolled back the database (don't forget my full backup!), and downgraded Ghost to the last worked version.

I start my proper siege the next morning. Looking through compatibility and requirements, I started with small steps:

• Upgrade NodeJS from 14 LTS to 16 LTS. Upgrade node, Force Ghost to upgrade to the current version. You want this to force packages to refresh and re-download.
• The second MySQL upgrade to version 8.0. Success, the database is upgraded, and Ghost 4.x is up and available.  

Since all recommendations failed already (Most of them - upgrade to the MySQL 8 default character set utf8mb4 and utf8mb4_0900_ai_ci for collation),  I decided to go where the flow led me. My database and tables were configured with utf8mb4 and utf8mb4_0900_ai_ci, while Ghost upgrade tried to create new objects with a different pair  - utf8mb4 and utf8mb4_general_ci. There are steps to fix the issue:

1. Connect to your Ghost database with the MySQL client and run the query

    SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;\n ", "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;") AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA = database();
   

2. Save SQL commands from the output to the file, i.e., ghost-cnv.sql

3. Connect to the database again and convert your schema.

    -- To avoid foreign key errors 
    SET FOREIGN_KEY_CHECKS = 0;
    source ghost-cnv.sql
    -- We still need them 
    SET FOREIGN_KEY_CHECKS = 1;
   

4. To make sure that the incoming session matches your configuration, add the into my.cnf under sqld section as below:

    [sqld]
    init_connect='SET collation_connection = utf8mb4_general_ci'
   

5. Restart the database server to pick up the new configuration.

Finally, the Ghost engine 5.22.10 gave up and completed the database upgrade.