Unable to update Ghost from 5.2.3 to 5.4.0

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:

1 Like

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.

1 Like

This unfortunately did not solve my problem

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

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


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

Check this solution:

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.

1 Like

Has anyone with the same problem managed to solve it?

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

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.

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

But the file should not be empty.

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'