Fixing MySQL errors when upgrading Ghost

I was updating my Ghost install from 4.7.0 to the last v4 version (4.48.9) in order to update to Ghost 5 (which came out over 6 months ago 😅) and I ran into this error when updating.

✖ Restarting Ghost
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table `oauth` add constraint `oauth_user_id_foreign` foreign key (`user_id`) references `users` (`id`) - Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'oauth_user_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 01-add-oauth-user-data.js
Suggestion: journalctl -u ghost_blog-golf1052-com -n 50

Debug Information:
    OS: Ubuntu, v20.04.4 LTS
    Node Version: v16.16.0
    Ghost Version: 4.48.9
    Ghost-CLI Version: 1.23.1
    Environment: production
    Command: 'ghost update v4'

Additional log info available in: /home/golf1052/.ghost/logs/ghost-cli-debug-2022-12-27T23_10_49_913Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.



? Unable to upgrade Ghost from v4.7.0 to v4.48.9. Would you like to revert back to v4.7.0?

I decided not to rollback but the suggestion to run ghost doctor didn't help, no errors were found. After searching about the error I saw that my issue was most likely due to MySQL 8. If MySQL gets upgraded from v5 to v8, which apparently happens when upgrading to Ubuntu 20.04, the default collation for the character set gets changed from utf8mb4_general_ci to utf8mb4_0900_ai_ci. Ghost can't seem to fix this automatically and the instructions to fix this were annoying to find so here's what to do with reference links.

Instructions

1. Run ghost config database.client to verify your Ghost database is MySQL.
2. Run ghost config database.connection.database to get the name of your Ghost database in MySQL.
3. Run mysql <database> -u <username> -p -B --disable-column-names -h localhost -e 'SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ", "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA = database();' to get all the ALTER TABLE commands you'll need to fix your Ghost database. Replace <database> with the name of your Ghost database that you got from step 2. Replace <username> with a MySQL user that has permissions on your database. You can always use root.
4. Copy the ALTER TABLE commands that were listed in your terminal and update this command to include them. mysql <database> -u <username> -p 'set foreign_key_checks=0; <ALTER TABLE COMMANDS> set foreign_key_checks=1;'. Use the same database and username as before. Make sure to replace <ALTER TABLE COMMANDS> with the commands you copied.
5. Run ghost start. There should be no errors when Ghost starts up again.

Reference

How to update MySQL 5 to MySQL 8