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_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.
ghost config database.clientto verify your Ghost database is MySQL.
ghost config database.connection.databaseto get the name of your Ghost database in MySQL.
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 TABLEcommands 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.
- Copy the
ALTER TABLEcommands 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.
ghost start. There should be no errors when Ghost starts up again.