Fixing your old Ghost Blog 's database migrations

Introduction

I admit I haven't upgraded my Ghost Blog's to the next major version until today. Most of the reason was because I kept running into the following error:

Ghost was able to start, but errored during boot with: alter table members_login_events add constraint members_login_events_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_login_events_member_id_foreign’ are incompatible.

and was mostly too lazy to fix it. However, a friend of mine also ran into the same issue when upgrading their ghost installation from v4 to another v4 instance. Another friend expressed encountering a similar problem in the past.

It should be noted that there is already a forum post here https://forum.ghost.org/t/ghost-4-1-0-errored-during-boot/21006 describing the same issue and potential fixes, however as my friend was a bit less technically inclined, I thought it would be best to write a more beginner friendly post on how to fix this issue.

Audience

As I mentioned before, I wrote this mostly for my friend who sort of dabbles in things like SSH and runs their ghost blog from a DigitalOcean VM. They aren't super comfortable in much else beyond that. To that extent, I do assume familiarly with connecting to SSH and basis linux commands like ls, cd, and nano/vim.

Retrieving your Ghost installation's MySQL database details

If you already know the SQL database details for your ghost installation, you can skip this section.

You can find the login details by looking for the config.production.json file in your ghost's installation directory.

sudo find / -name config.production.json

Note that if you see multiple files, it's the one in the root-most directory. There are sometimes other copies in versions folders, but those might not have the information you're looking for. Here's mine:

Inspection of the file with tail, vim, nano, etc reveals the connection details in a format similar to below. These are your MySQL connection details for ghost.

Logging into MySQL

Given our MySQL database details, we can log into MySQL through command line and check our current database collation.

mysql -u <user> -p

It should also prompt for the MySQL password. If all goes well, you should be dropped into the MySQL prompt like below

The rest of this guide will take place inside the mysql> prompt.

Checking the current collation

Now to check the current collation of the tables in the database, we'll use the following query. Note that if your database is named something other than ghost_production, make sure to change that or you won't see any results.

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME
ROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='ghost_production' 
AND COLLATION_NAME IS NOT NULL 
AND COLLATION_NAME <> 'utf8mb4_general_ci';

For ghost, it's important that everything is set to utf8mb4_unicode_ci. If you get an empty set like below, then that means either all of your current database tables are the correct collation, or the database name is incorrect and you didn't change it in the query above.

However, if you get results like I did below, then that means some of the tables are not the correct collation. Here, we can see that member_subscribe_events is utf8mb4_0900_ai_ci and not utf8mb4_unicode_ci like it should be. We'll need to fix that.

Fixing the table collation

If you got an empty set when running the collation command above, then skip this section. Your tables are already the correct collation.

Fixing the tables is relatively straightforward. While still in the mysql prompt, we'll execute the following code to change to the proper collation. Note that member_subscribe_events was the table that was incorrect for me earlier. Yours might be different depending on the output from the previous step. Also note again that your database name may also not be ghost_production depending on your database details from earlier. Make changes accordingly.

use ghost_production;
ALTER TABLE members_subscribe_events 
CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

If we rerun our command from the previous step to check collation, we should see that this table has dropped off the list. As that was the only table I needed to fix, I now am just left with the empty set. You may need to repeat if you have more than one table that needs changing.

Setting the default collation

Now that the current collation is dealt with, the only thing left to do is set the default collation for the entire database. First, we'll check the current collation with the following code:

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME='ghost_production';

Mine is already showing the correct values. If yours is showing anything different, run the following commands to set it to the correct collation:

ALTER DATABASE 'ghost_production'
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;

And recheck with the previous query. Once everything is good, you can type the following to exit the MySQL prompt.

quit

Congratulations! Your database should be all fixed and you should be free to upgrade your ghost installation within ghost-cli without any pesky database errors.