Overcoming failed DB migration when updating from Ghost 4.7.0 to 4.22.4

Photo by Diana Polekhina / Unsplash

A slight hiccup came up while I was updating my blog to the latest Ghost version. Generally, updating to a new version is smooth and fairly straightforward.

While the error shown in the CLI, as higlighted below, is descriptive enough. It is also quite new to me, a first in fact.

Message: Ghost was able to start, but errored during boot with: alter table 
`products_benefits` add constraint `products_benefits_product_id_foreign` 
foreign key (`product_id`) references `products` (`id`) on delete CASCADE - 

UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'product_id' and referenced 
column 'id' in foreign key constraint 'products_benefits_product_id_foreign' 
are incompatible.

Help: Error occurred while executing the following migration: 03-add-
products-benefits-table.js

Thankfully, a quick search in the Ghost forums yielded a relatively simple solution. Accdg. to user jebarjonet, the error was caused by the change in the default collate for character set utf8mb4, from utf8mb4_general_ci to utf8mb4_0900_ai_ci. This change was because of a recent server OS upgrade that I have done, a few weeks ago.

The fix is to explicitly set the default_collation_for_utf8mb4 global option to utf8mb4_general_ci in the mysql.cnf file. Once done, the mysql service needs to be restarted.

The ghost update can then be resumed with no more problems!

Lhar Gil
Kuala Lumpur