Ghost Upgrade: A Bit of MySQL

Michael Mikhailidi -

This morning I accidentally brought down this site. The Ghost CLI told me that there is a minor upgrade to version 4.11.0. Unfortunately, even though the start version was 4.10.0, schema update has broken the server.

We use MySQL as a Ghost blog database, and it's turned out, scripts wouldn't create a table and foreign key at the startup time. The error message points to a foreign key issue with no further explanation. The blog server fails to start because the database has no trace of the requested table. A quick search for the error has returned generic recommendations but nothing related to my problem.

Well, if you can't fix the error - prevent it. So I decided to create the missing table and references. After some search on GitHub, I found the schema migration code for that release. The next step is to translate the JS definition into MySQL commands. Short story short, in case if you run into the same problem, here is DDL to create the missing pieces.  Connect to your MySQL database with ghost credentials and run commands as below 

## Create missing table 
create table `oauth` (
 `id` varchar(24) not null primary key,
 `provider` varchar(50) not null,
 `provider_id` varchar(191) not null,
 `access_token` text,
 `refresh_token` varchar(2000) default null,
 `created_at` datetime not null,
 `updated_at` datetime default null,
 `user_id` varchar(24) not null
  ) engine=InnoDB default charset=utf8;
## Define a new index
alter table `oauth` add index `oauth_user_id`(`user_id`);
## Build a new foreign key
alter table `oauth` add constraint `oauth_user_id_foreign` 
    foreign key (`user_id`) REFERENCES `users`(`id`) on delete no action;
Create a new table in the MySQL database

Two key factors to success:

  • Make sure that you use the same engine and character set as table USERS. In my case - they are InnoDB and utlf8mb4.
  • Create an index on the user_id field before the foreign key. It's not that obvious for people from the Oracle side.  Normally, an index on the source column improves query performance in the Oracle database, but it's not required for the constraint itself.