The Tale of a Battle Upgrading Ghost

So, you likely already know that I use the Ghost blogging system. I absolutely love it. For a while, I hosted my blog with Midnight. I migrated away and now run it on my own VPS. There's nothing wrong with Midnight, they're an amazing service and I will always recommend them over the official Ghost hosting. However, I like to have full control of what I run, so here we are.

Now, the thing that's great about managed hosting like Midnight? I don't have to worry about upgrading anything. My most recent escapade of upgrading Ghost reminded me why I switched to Midnight in the first place, and made me consider switching back. Ghost can sometimes be so smooth and easy to upgrade, in fact it usually is. Then sometimes... it's terrible. My most recent upgrade was one of those experiences.

I ran the normal upgrade process, and went to start my Ghost blog back up, and was greeted with a beautiful error message.

❗

Error: alter table donation_payment_events add constraint donation_payment_events_member_id_foreign foreign key (member_id) references members (id) on delete SET NULL - Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'donation_payment_events_member_id_foreign' are incompatible.

Wonderful. I love SQL errors. They're my favorite.

via GIPHY

Or perhaps they are not.

What now?

It was time to do some research. I came across this wonderful post by Michael Freidrich and this ended up being the solution, although I needed to tweak it. I don't use Docker for my Ghost installation, so the commands won't work as he wrote them. So let's walk through the modifications I needed to do.

First, I needed to change the directory to my ghost installation. Note, the following is not the actual directory, it's just a representation.

cd /etc/ghost/

Once I was in my directory, I needed to create my backups directory:

mkdir backups

Then I needed to get the username and password for my ghost user.

cat config.production.json

Once I grabbed the username and password from this file, I had to dump the database. So I first confirmed my database name.

mysql -u USERNAME -p
show databases;
quit;

Once I had that, it was time to back it up.

mysqldump -h localhost -u USERNAME -pPASSWORD --databases DATABASENAME > backup-ghost-db.sql

Now, if I've learned anything in my career in IT, it's never directly modify the backup file. Make changes to a copy of it. So I moved it to the backups folder, and made a copy

mv backup-ghost-db.sql backups
cp backup-ghost-db.sql backup-ghost-db-new.sql

Run the SED command to change the collation of the database on the copy of the backup:

sed -i 's/utf8mb4_general_ci/utf8mb4_0900_ai_ci/g' backup-ghost-db-new.sql

You can run the diff he recommends to confirm that the changes were properly made, and I did... and I'd recommend you do:

diff -ur backup-ghost-db.sql backup-ghost-db-new.sql

If all looks good, go back in to your MySQL server as the root account:

mysql -u root -p

Drop the database and create it again:

DROP DATABASE databasename;
CREATE DATABASE databasename;
quit;

Then restore the copied/modified database backup:

mysql -h localhost -u root -pPASSWORD databasename < backups/backup-ghost-db-new.sql

Once it finishes, start your Ghost blog:

ghost start

And I was golden! Back online. But good lord, I wish Ghost was always a smooth upgrade...