Upgrading from 5.23.0 to 5.25.0 breaks ghost with SQL error

[Darkice2]

Website is down after trying to upgrade from 5.23.0 to 5.25.0. Even though the install detected an issue and I said not to continue, it still did, and now I’m stuck at broken 5.25.0

-bash-4.2$ ghost start

Love open source? We’re hiring JavaScript Engineers to work on Ghost full-time.

Ghost

• sudo systemctl is-active ghost_deeptide-com
  Checking system Node.js version - found v16.17.0
  Ensuring user is not logged in as ghost user
  Checking if logged in user is directory owner
  Checking current folder permissions
• sudo systemctl is-active ghost_deeptide-com
  Validating config
  Checking folder permissions
  Checking file permissions
  Checking content folder ownership
  Checking memory availability
  Checking binary dependencies
  Checking systemd unit file
  Checking systemd node version - found v16.17.0
• sudo systemctl start ghost_deeptide-com
• sudo systemctl stop ghost_deeptide-com
  Starting Ghost: deeptide-com
  A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table suppressions add constraint suppressions_email_id_foreign foreign key (email_id) references emails (id) - Referencing column ‘email_id’ and referenced column ‘id’ in foreign key constraint ‘suppressions_email_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 2022-11-24-10-36-add-suppressions-table.js
Suggestion: journalctl -u ghost_deeptide-com -n 50

Debug Information:
OS: CentOS Linux, v7
Node Version: v16.17.0
Ghost Version: 5.25.0
Ghost-CLI Version: 1.23.1
Environment: production
Command: ‘ghost start’

Additional log info available in: /home/deeptideghost/.ghost/logs/ghost-cli-debug-2022-12-06T01_53_48_456Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to Ghost-CLI - A fully loaded tool for installation and configuration for troubleshooting.
-bash-4.2$

This is the second time Ghost has broken for me randomly when doing an upgrade. Last time it was because using MariaDB which became unsupported but now using MySQL and not sure why this is happening

Thanks for any advice

[Darkice2]

After googled, many other people seem to have similar issues when upgrading…

I had to manually run this command in MySQL but I am not sure why

mysql> SET PERSIST default_collation_for_utf8mb4=‘utf8mb4_general_ci’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Is the above issue a bug and was what I did to fix this correct?

My feedback is the upgrade process needs to be more reliable in general.

Also I am not sure why each time you do an upgrade you have to “manually” run the sudo command to fix file permissions… why can’t Ghost keep correct file permissions in the first place? This should never be an issue and the user should never need to manually reset file permissions…

I love the software itself but upgrade process needs work to improve stability and reduce unnecessary manually operations

[mjw]

This isn’t a bug, so I’ve moved the thread to #help:self-hosting.

You seem to have resolved the issued, which was caused by your database having two different character sets. Errors occur when comparing two strings that have incompatible collations.

Your problem was most likely caused when migrating from unsupported MariaDB to MySQL.

If you are concerned about managing upgrades on your own server, perhaps you should consider managed hosting?

[Ion_Prodan]

I have the same issue, I can’t make it run
Even if I have done all things related here and in other posts from this forum.

Debug Information:
OS: Ubuntu, v20.04.5 LTS
Node Version: v16.19.0
Ghost Version: 5.33.6
Ghost-CLI Version: 1.23.1
Environment: production
Command: 'ghost start

[Ion_Prodan]

@mjw

two strings that have incompatible collations.

After many attempts, I managed to run it. Here’s how:

mysql -u root -p

use <database>;

ALTER TABLE emails CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

After I edited /etc/mysql/my.cnf like this:

[mysqld]
init_connect='SET default_collation_for_utf8mb4=utf8mb4_0900_ai_ci'