Unable to update Ghost from 5.2.3 to 5.4.0

Developer help Self-hosting
14

I was facing the same issue. Don’t have much experience about what collate is how it works. But just to let you guys know what I was doing wrong.

As suggested by the page https://ghost.org/docs/faq/supported-databases/ under section How to update MySQL 5 to MySQL 8, I tried modifying the tables using the command given.

I could get the ALTER table commands generated properly with the given script

mysql <database> -u <username> -p -B --disable-column-names -h localhost -e 'SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ", "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") AS alter_sql FROM information_schema.TABLES WHERE TABLE_SCHEMA = database();'

But I was not able to run the generated alter table commands directly as given in the article.

mysql <database> -u <username> -p 'set foreign_key_checks=0; <ALTER TABLE COMMANDS> set foreign_key_checks=1;'

Maybe something wrong with my copy paste. Instead I logged into mysql, disabled the foreign key check and then pasted just the alter table commands.

mysql -u root -p

set foreign_key_checks=0;

Run all ALTER table commands by pasting them on the mysql prompt.

set foreign_key_checks=1;

You should see many rows updated with similar messages as below…

mysql> ALTER TABLE ghost.webhooks CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ghost.webhooks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

After this ghost start worked fine

1 Like