Unable to update Ghost from 5.2.3 to 5.4.0

Developer help Self-hosting
1

I am unable to update ghost, I am experiencing the following problem:

✖ Restarting Ghost
A GhostError occurred.

ùDebug Information:
    OS: Ubuntu, v18.04.3 LTS
    Node Version: v14.17.1
    Ghost Version: 5.4.0
    Ghost-CLI Version: 1.21.0
    Environment: production
    Command: 'ghost update'
Message: Ghost was able to start, but errored during boot with: alter table `comments` add constraint `comments_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) on delete CASCADE - Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'comments_post_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 2022-07-04-13-49-add-comments-table.js
Suggestion: journalctl -u ghost_domain-it -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table `comments` add constraint `comments_post_id_foreign` foreign key (`post_id`) reference$
    at Server.<anonymous> (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
    at Object.onceWrapper (events.js:481:28)
    at Server.emit (events.js:375:28)
    at emitCloseNT (net.js:1661:8)
    at processTicksAndRejections (internal/process/task_queues.js:81:21)

They are also talking about it here, for the moment without solution:

2

This looks similar to other reported issues, and may be related to the collation on the database tables. Search the forum to check, and for the fix.

3

This unfortunately did not solve my problem

4

I’m trying to follow these tips:

All my tables are DYNAMIC, but they are utf8mb4_general_ci, if I try to convert them to utf8mb4_0900_ai_ci, as suggested in the post, I get the following error:

Unknown character set: 'utf8mb4_0900_ai_ci'.

I also tried with the following command:

alter database dbname character set utf8mb4_0900_ai_ci COLLOCATE utf8mb4_general_ci;

the result is the following:
ERROR 1253 (42000): COLLATION 'utf8mb4_0900_ai_ci' is not valid for CHARACTER SET 'utf8mb3'

yet if I give the show character set command, I display all the collations without problems

5

In MySQL, what does SELECT version(); return? (or on the CLI: mysql --version)?

6 

+-----------+
| version() |
+-----------+
| 8.0.29    |
+-----------+
1 row in set (0.00 sec)
7

Check this solution:

8

Thanks but as I wrote in my second post, this topic didn’t help me.
I have done as it suggests, modified the mysql.cnf file, restarted mysql, tried to update ghost, it always gives me the same problem.

9

Has anyone with the same problem managed to solve it?

10

Do you have this, or a similar line, in my.cnf?

character_set_server='latin1'

If so, try changing this to:

character-set-server = utf8
11

do you mean /etc/mysql/conf.d/mysql.cnf ?

its empty, i have only [mysql] without any parameters, I tried to add the one suggested in the other topics but it didn’t help.

I also tried your suggestion, nothing changes.

12

my.cnf and mysql.cnf are the same file; just symbolic links to different names.

But the file should not be empty.

13

I also have debian.cnf and mysqldump.cnf with some parameters inside

and another directory with another mysqld.cnf file

/etc/mysql/mysql.conf.d

maybe i need to change this

EDIT

Solved

if you have the same problem as me, edit the mysqld.cnf file in this other directory /etc/mysql/mysql.conf.d

init-connect = 'SET default_collation_for_utf8mb4 = utf8mb4_general_ci'

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

15

I had similar issue and resolved it via ALTER TABLE
In my case, however, I have to update many tables, so I first created a list of all ALTER statements via this command:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA,".", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;") 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = "ghostbdb";

image
image1070×425 10.5 KB

And then I just copy-pasted all the statements.
Don’t forget to remove the | symbols in a notepad!