How to Upgrade from Ghost 3 to 4 on Digital Ocean

In this post, I'll show all steps to upgrade Ghost 3.x to 4.x on self hosted Ghost Blog. This guide includes all required steps like:

• updating ghost-cli
• mysql update from 5 to 8

During the upgrade you may face errors like:

Ghost was able to start, but errored during boot with: Cannot remove permission(Reset all passwords) with role(Administrator) - permission does not exist

or

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) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'oauth_user_id_foreign' are incompatible.

or

Message: Ghost v4.16.0 is not compatible with the current Node version.

or

System checks failed with message: 'Linux version is not Ubuntu 16 or 18'

And other Ghost collation and Node errors with your MySQL database.

You will find a solution which works for all of them during the upgrade to Ghost 4.

I decided to upgrade from 3 to Ghost 4 because of this message:

Critical security update available — please update Ghost as soon as possible

Member account takeover

Prior starting the upgrade you can get familiar with:

• How to update Ghost
• Ghost open issues
• Security update available for Ghost 3.x and 4.x
• Ghost Forum

Step 1: Back up your Ghost installation

The best way to have all the Ghost content backed up is by export.

For more info please check:

• Ghost backup best practices
• Ghost imports and exports

Export Ghost content:

Ghost 3

• Settings - from the left pane menu
• Labs
• Press the Export your content. button

Ghost 4

• Settings - from the gear wheel icon - bottom left
• Labs
• Press the Export your content. button

Note: You may download your ghost theme and the media content from /var/www/your_ghost/content/images/

Use full server backup

In my case I'm using Digital Ocean which offers frequent snapshots. This is an extremely useful feature for such cases.

In any case be sure that you can restore your previous version of the site.

Note: Use cloudflare in order to minimize downtime on your side.

Step 2: Update to the latest 3.x version

It's required first to update to the latest Ghost 3 version before upgrading to Ghost 4.

This can be done by:

ghost update v3

In order to check which is your current version use:

ghost -v

Ghost-CLI version: 1.17.3
Ghost version: 3.42.6

Step 3: Update Node version and reinstall latest Ghost 3.x

You can check the official ghost page for Node updates: Upgrade Node.js.

The steps which you may need to follow depend on your installation of Node - i.e. npm. For more details check Bonus Step 1 in this post.

curl -sL https://deb.nodesource.com/setup_14.x | sudo -E bash
sudo apt-get install -y nodejs

To trigger a re-install of dependencies and to reinstall your current version of Ghost you need to use:

ghost update v3 --force

Step 4: Update to Ghost 4

Finally you can update to Ghost 4 by:

ghost update

or by

ghost update v4 --force

Check if your ghost is running! If not you need to check the MySQL problems related to MySQL 5 vs MySQL 8 collation.

If you face errors related to MySQL check Bonus Step 2

Bonus Step 1: Update the Node and Ghost CLI

Depending on your version, OS and environment this step may differ a bit.

In order to avoid errors like:

Message: Ghost v4.16.0 is not compatible with the current Node version.

and

System checks failed with message: 'Linux version is not Ubuntu 16 or 18'

and

Message: The version of Node.js you are using is not supported.
Supported: ^10.13.0 || ^12.10.0
Installed: 14.17.6

Check next steps:

Update Ghost CLI

The official docs regarding Ghost CLI Update can be found on this link: Ghost-CLI install & update

sudo npm install -g ghost-cli@latest

Check Node & NPM version

First you can check your npm and node versions by:

node -v && npm -v

result will be something like:

v14.17.6
6.14.15

List available Node versions

To list all available Node versions:

nvm ls-remote

result will be long list like:

       v14.17.5   (LTS: Fermium)
->     v14.17.6   (LTS: Fermium)
       v14.18.0   (Latest LTS: Fermium)
        v15.0.0
        v15.0.1

Install stable Node

In order to install the Long-term support (LTS) version of the Node you can use:

nvm install lts/fermium

or

nvm install 14.18.0

Change default Node

In order to change the Node version used by Ghost and your system use:

nvm use 14.18.0

or

nvm alias default 14.18.0
nvm use default

Bonus Step 2: Upgrade to Ghost 4: MySQL 5 vs MySQL 8

Several MySQL issues can be faced during the upgrade from 3.x to 4.x.

The problems are mainly related to the collation of the:

• Ghost 3 / MySQL 5 - utf8mb4_general_ci
• Ghost 4 / MySQL 8 - utf8mb4_0900_ai_ci

It's recommended to use the new one utf8mb4_0900_ai_ci which is better and has faster performance. For more info read:

• What's the difference between utf8_general_ci and utf8_unicode_ci?
• What is the utf8mb4_0900_ai_ci collation?

Other possible solutions

I was trying different ways to solve the errors like:

Recreate all MySQL tables

sudo mysqlcheck -o my_ghost_database

which resulted in:

my_ghost_database.posts_meta
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

and nothing changed.

Change DataBase collation by:

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

Change default /etc/mysql/conf.d/mysql.cnf

• edit /etc/mysql/conf.d/mysql.cnf
• add this line

[mysqld]
init-connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'

• save
• run service mysql restart
• ghost update

This didn't work either. All solutions were taken from the resources shared at the end of this article.

Fix: UNKNOWN_CODE_PLEASE_REPORT MySQL Errors

What worked for me was to update all tables' collation.

So I was investigating the collation per:

• database
• table
• columns

show full columns from members;

and:

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

result

I've notice that collation was mixed:

• utf8mb4_general_ci
• utf8mb4_0900_ai_ci
• Null

So I generated SQL to update collation per table and for all columns:

Generate SQL to update collation for all tables and columns

SET sql_mode='PIPES_AS_CONCAT';

SELECT DIstinct
'ALTER TABLE ' || TABLE_SCHEMA || '.' || TABLE_NAME || '  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'

    FROM INFORMATION_SCHEMA.COLUMNS
    where TABLE_SCHEMA='my_ghost_database'

Where SET sql_mode='PIPES_AS_CONCAT'; enables the double pipe concatenation in MySQL. You will get SQL alter syntax for all your tables like:

ALTER TABLE my_ghost_database.actions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.api_keys  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

You need to prepend the code by: SET FOREIGN_KEY_CHECKS=0; which is going to silence the foreign key errors for the current session only.

Otherwise you will get errors for all problematic foreing keys.

The full code can be found below:

SET FOREIGN_KEY_CHECKS=0;


ALTER TABLE my_ghost_database.actions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.api_keys  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.brute  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.email_batches  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.email_recipients  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.emails  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.integrations  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.invites  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.labels  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_email_change_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_labels  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_login_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_paid_subscription_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_payment_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_products  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_status_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_stripe_customers  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_stripe_customers_subscriptions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.members_subscribe_events  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.migrations  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.migrations_lock  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.mobiledoc_revisions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.permissions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.permissions_roles  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.permissions_users  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.posts  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.posts_authors  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.posts_meta  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.posts_tags  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.products  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.roles  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.roles_users  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.sessions  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.settings  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.snippets  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.tags  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.tokens  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.users  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE my_ghost_database.webhooks  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

After this step Ghost 4 was running successfully.

Resources

• Unable to upgrade Ghost from v4.2.0 to v4.3.0 CASCADE - UNKNOWN_CODE_PLEASE_REPORT
• Ghost 4.1.0 errored during boot
• Does MySQL foreign_key_checks affect the entire database?
• How to change collation of database, table, column?
• Query to show all tables and their collation