MySQL error upgrading from v5.55.0...v5.58.0 - errored during boot with: alter table `donation_payment_events`

[Darkice2]

Hey, I have the following error when upgrading… any idea how to fix? Suspect I need to change some mysql setting for the char set but not sure… please advise

-bash-4.2$ ghost upgrade

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

Ghost

• sudo systemctl is-active ghost_deeptide-com
  ? Sudo Password [hidden]
  Checking system Node.js version - found v16.18.1
  Ensuring user is not logged in as ghost user
  Checking if logged in user is directory owner
  Checking current folder permissions
  Checking folder permissions
  Checking file permissions
  Checking content folder ownership
  Checking memory availability
  Checking free space
  Checking for available migrations
  Checking for latest Ghost version

5.58.0

• Fixed member filtering on newsletter subscription status (#17583) - Michael Barrett

---

View the changelog for full details: Comparing v5.57.3...v5.58.0 · TryGhost/Ghost · GitHub

Help us translate Ghost + Portal: Help translate Ghost (beta)

Fetched release notes
Downloading and updating Ghost to v5.58.0

• sudo systemctl stop ghost_deeptide-com
  Stopping Ghost
  Linking latest Ghost and recording versions
• sudo systemctl start ghost_deeptide-com
• sudo systemctl stop ghost_deeptide-com
  Restarting Ghost
  A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table donation_payment_events add constraint donation_payment_events_member_id_foreign foreign key (member_id) references members (id) on delete SET NULL - Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘donation_payment_events_member_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 2023-07-27-11-47-49-create-donation-events.js
Suggestion: journalctl -u ghost_deeptide-com -n 50

Debug Information:
OS: CentOS Linux, v7
Node Version: v16.18.1
Ghost Version: 5.58.0
Ghost-CLI Version: 1.24.2
Environment: production
Command: ‘ghost upgrade’

Additional log info available in: /home/deeptideghost/.ghost/logs/ghost-cli-debug-2023-08-06T14_00_58_751Z.log

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

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.

? Unable to upgrade Ghost from v5.55.0 to v5.58.0. Would you like to revert back to v5.55.0? (Y/n)

Debug Information:
OS: CentOS Linux, v7
Node Version: v16.18.1
Ghost Version: 5.58.0
Ghost-CLI Version: 1.24.2
Environment: production
Command: ‘ghost start’
Message: Ghost was able to start, but errored during boot with: alter table donation_payment_events add constraint donation_payment_events_member_id_foreign foreign key (member_id) references members (id) on delete SET NULL - Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘donation_payment_events_member_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 2023-07-27-11-47-49-create-donation-events.js
Suggestion: journalctl -u ghost_deeptide-com -n 50
Stack: Error: Ghost was able to start, but errored during boot with: alter table donation_payment_events add constraint donation_payment_events_member_id_foreign foreign key (member_id) references members (id) on delete SET NULL - Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘donation_payment_events_member_id_foreign’ are incompatible.
at Server. (/usr/local/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
at Object.onceWrapper (node:events:627:28)
at Server.emit (node:events:513:28)
at emitCloseNT (node:net:1857:8)
at processTicksAndRejections (node:internal/process/task_queues:82:21)

[Darkice2]

I notice that this ghost has utf8mb4_general_ci on the tables

another ghost I have has utf8mb4_0900_ai_ci on the tables and that one upgrades correctly and is exact same version

do I need to fix something on the collation of tables?

[Darkice2]

I was able to fix it by basically doing this

Unable to update Ghost from 5.2.3 to 5.4.0

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 …

except i set forgeign key check to 0
then manually do this for each table…

ALTER TABLE subscriptions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

then set back to 1

then upgrade worked