Database Error When Upgrading Ghost 5.0 With Docker Image

Part of my job as CTO at Leapjuice is to ensure we're aggressively testing new releases of Ghost CMS. We want our customers to have the benefit of always being up-to-date and running the latest version.

As issues arise with upgrades, we seek to highlight and provide solutions for folks running and hosting their own Ghost server and publications. One such issue is the database configuration in upgrading to Ghost 5.0.

The issue can affect those running both standalone versions of Ghost installs as well as Docker containers. Most of the issues I've seen arise from folks running Ghost Docker images, as this method didn't require the user to address the database.

Ghost supports MySQL 8 as the primary database. However, prior to Ghost version 5.8.3 (the latest is v5.17.0 as of the date of this post), Ghost Docker images configured themselves to use SQLite by default. Standalone installs would be configured the same unless the user manually made the change to the configuration file and set up a database server connection.

This made things easy. The Docker installation would create a standalone database file to utilize within the environment itself. It could then set its own configuration and access the database without any direction or interaction from the user. This is called SQLite and Docker images used it by default.

The configuration looked as follows:

"database": {
  "client": "sqlite3",
  "connection": {
    "filename": "content/data/ghost-test.db"
  },
  "useNullAsDefault": true,
  "debug": false
}

Ghost made this breaking change with the release of version 5.8.4. Officially, however, the change was set to be implemented with the release of Ghost 5.0 and was expected at any time.

If one were to perform a Ghost CMS upgrade following version 5.8.3, either by upgrading the Docker container or manual install, the result would be a database error. You'd have no access to your data, and the container would be unable to run since there's no active database connection.

While the fix itself isn't challenging (we just need to configure a database, right?), the work of maintaining your data during migration from SQLite to MySQL can be more intricate. This article will focus on the database fix to get back up and running as soon as possible. The method you choose to migrate your data will be up to you. However, I will make a recommendation for the easiest, more technology-friendly course of action.  

Let's set up the database.

First, we need to ensure a MySQL 8 database server is running that we can access. Note that your Ghost setup can access both local database servers as well as remote. You'll then need to create a new database on the server. We will connect to this database using our login credentials.

For users manually installing Ghost using the configuration file, you'll want your database configuration to look as follows:

"database": {
  "client": "mysql",
  "connection": {
    "host": "127.0.0.1",
    "port": 3306,
    "user": "your_database_user",
    "password": "your_database_password",
    "database": "your_database_name"
  }
}

You'll replace "host," "port," "user," "password", and "database" with the respective variables of the database you created.

Ghost Docker image users seem to have more difficulty since previously there was no requirement to address the database. The container set it up (using SQLite) by default.

Add the following variables to your Docker compose file prior to launching or recreating your container:

database__client = mysql
database__connection__host = xxx.xxx.xxx.xxx
database__connection__port = 3306
database__connection__user = "your_database_user"
database__connection__password = "your_database_password"
database__connection__database - "your_database_name"

Make sure there are two (2) underscores between the variable names. I'd recommend using an IP address vs "localhost" to connect to the local database server. While 127.0.0.1 (localhost) may work, the external machine IP address would be the better choice and prevent potential errors.

Ghost will now obtain a database connection and run smoothly. However, all your data is gone and you're essentially left with an empty, brand new blog or publication. You need your data.

Theoretically, it should be possible to perform a data dump on the previous SQLite database, and then import the data into your new MySQL 8 database. In practice, this can be a lot more difficult and would be recommended for more advanced users.

An easier method would be to export your data, content, and configuration directly from the Ghost platform itself. Login to your Ghost dashboard and go to "Settings."  Under the "Advanced" section at the bottom click on "Labs." This will open the page below:

From here we want to save 3 items:

1) Export and download your content;
2) Download current redirects; and
3) Download current routes.

Once you have these files saved you can import them into your new publication with MySQL database connected. Then make any necessary changes to your theme.

It's not a seamless transition but it's better than losing your data and content.

For folks that merely want to upgrade without creating a new database (temporary fix), add the following variables to your Docker Compose file:

database__client = sqlite3
database__connection__filename = content/data/ghost-test.db
database__useNullAsDefault = true
database__debug = false

Note that this might only work for a fixed period of time. Since Ghost CMS only supports MySQL 8 it's likely not a long-term, perpetual fix. However, this will get you up and running again.

My overall recommendation would be to begin using MySQL 8 and upgrade seamlessly going forward. Even better, sign up for Leapjuice and let us manage your Ghost server hosting altogether.

Helpful links relating to this article:

Official Docker Ghost Image
Ghost Configuration
Power your Ghost CMS publication with Leapjuice