Ghost Blog - ER_FK_INCOMPATIBLE_COLUMNS

https://www.youtube.com/watch?v=JSAuFqOC5Jw

I was getting error : ER_FK_INCOMPATIBLE_COLUMNS when updating ghost to :latest, 5.6.0, or :5.74 , but it worked fine on 5.59.1  .

According to the Ghost FAQ:

Resolving a misconfigured MySQL database with Ghost - Ghost Developers

Find out how to resolve issues with a misconfigured MySQL database for developers maintaining a self-hosted instance of Ghost.

Ghost - The Professional Publishing Platform

This error "ER_FK_INCOMPATIBLE_COLUMNS" is due to "The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 (utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0)".

However, the file their solution references, "my.cnf", I could not find and the rest of their instruction's didn't actually fix the issue. It just helped identify collation settings in MySQL. I am not the most comfortable with MySQL, especially when just getting a docker container to work so I was quite stuck when presented this information.

I typically use the Unraid Community App called Adminer to navigate and use my Databases, and the rest of my instructions occur inside this container, but I think any MySQL client and connection to your Ghost MySQL8 DB would work.

Execute the following SQL Statement: 

SELECT CONCAT('ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') AS sql_statement FROM information_schema.tables WHERE table_schema = 'ghost';

(Where table_Schema is the name of your ghost DB)

Using the output from above, execute the following sql statement:

-- Disable foreign key checks

SET foreign_key_checks = 0;

-- Execute the generated ALTER TABLE statements

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

(paste whole output from previous step)

-- Enable foreign key checks

SET foreign_key_checks = 1;