Problem upgrading to Ghost 5.30 and higher

Developer help Self-hosting
1

When trying to update to 5.30, 5.31 or 5.4, I get the same error, causing the upgrade to fail:

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.
-bash: syntax error near unexpected token `(‘

At a bit of a loss as how to address this. Anyone?

2

What version were you upgrading from? And, are you running MySQL 8 or MariaDB?

3

Upgrading from 5.24, running MySQL 8:

  • OS: Ubuntu, v20.04.4 LTS
  • Node Version: v14.19.3
  • Ghost-CLI Version: 1.21.0
1 Like
4

An update:

This is almost certainly related to a database migrated from MySQL 5 to 8. I have three installs running on Digital Ocean droplets - the one that’s always been on MySQL 8 updated fine, the two that were migrated from 5 to 8 for Ghost 5 are the ones failing.

So, I have a clearer idea of the problem - but not how to fix it!

1 Like
5

I thought it may be database related, as I’ve seen something similar in the forum when migrating from MariaDB to MySQL.

If I have a moment later today, I’ll see if I can find something.

1 Like
6

It’s most likely collation related, where the original posts table has ended up with a different collation than new tables are configured to use.

There’s a related thread here with a potential fix, although I haven’t tried it myself Unable to upgrade Ghost from v4.2.0 to v4.3.0 (CASCADE - UNKNOWN_CODE_PLEASE_REPORT) - #56 by jebarjonet

The problem is also mentioned in the docs Resolving a misconfigured MySQL database with Ghost - Ghost Developers

4 Likes
7

Yes, that worked. Following these instructions resolved the situation easily and simply.

Thank you!

1 Like