Ghost+MySql: Together built for sadness

Robin Michael -

Again I am fighting with a MySql collations and character sets.  Why would this be a problem when I have set the default collation and changed all the tables to character set utf8mb4 and collation utf8mb4_0900_ai_ci.  Well it's because the columns within the MySql database are still on the old collation which causes an error whenever a new table is created that has the good defaults I have placed it will say the foreign key constraint is incompatible and things go boom.

How to fix:

  • Get the MySql password for ghost ghost config get database.connection.password
  • Connect to MySql with the password mysql -u ghost -p
  • Disable foreign key checks SET foreign_key_checks = 0;
  • Run this SELECT command to build the collation/convert statements for the tables

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="ghost_production"
AND TABLE_TYPE="BASE TABLE";

  • Copy the ALTER commands it will look something like this

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

  • Run all the commands 🙏
  • If no errors.  YAY!.  If errors read and adjust statements (this is to variable for me to predict)
  • Re-enable foreign key checks SET foreign_key_checks = 1