Unable to update from 5.24.2 to 5.25.0 (Referencing column 'email_id' and referenced column 'id' ... are incompatible)

Developer help Self-hosting
1

I’m unable to update my blog (grantwinney.com) to the latest version of Ghost (5.25.0) from the currently installed version (5.24.2).

Running ghost update 5.25.0 seems to get nearly to the end, but then fails when starting ghost back up, so then I accept the suggestion to roll it back to 5.24.2. The output in the console:

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_grantwinney-com -n 50

Debug Information:
    OS: Ubuntu, v20.04.5 LTS
    Node Version: v14.21.1
    Ghost Version: 5.25.0
    Ghost-CLI Version: 1.23.1
    Environment: production
    Command: 'ghost update 5.25.0'

Additional log info available in: /home/ghost-mgr/.ghost/logs/ghost-cli-debug-2022-12-04T03_30_27_424Z.log

The output from the log file referenced in the output above:

Debug Information:
    OS: Ubuntu, v20.04.5 LTS
    Node Version: v14.21.1
    Ghost Version: 5.25.0
    Ghost-CLI Version: 1.23.1
    Environment: production
    Command: 'ghost update 5.25.0'
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_grantwinney-com -n 50
Stack: Error: 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.
    at Server.<anonymous> (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
    at Object.onceWrapper (events.js:519:28)
    at Server.emit (events.js:400:28)
    at emitCloseNT (net.js:1677:8)
    at processTicksAndRejections (internal/process/task_queues.js:81:21)

The full error after running the journalctl command that the original error message suggested:

node[522582]: Error ID:
node[522582]:     300
node[522582]: ----------------------------------------
node[522582]: Error: alter table `suppressions` add constraint `suppressions_email_id_foreign` foreign key (`email_id`) reference>
node[522582]:     at /var/www/ghost/versions/5.25.0/node_modules/knex-migrator/lib/index.js:1032:19
node[522582]:     at Packet.asError (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/packets/packet.js:728:17)
node[522582]:     at Query.execute (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/commands/command.js:29:26)
node[522582]:     at Connection.handlePacket (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/connection.js:456:32)
node[522582]:     at PacketParser.onPacket (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/connection.js:85:12)
node[522582]:     at PacketParser.executeStart (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/packet_parser.js:75:16)
node[522582]:     at Socket.<anonymous> (/var/www/ghost/versions/5.25.0/node_modules/mysql2/lib/connection.js:92:25)
node[522582]:     at Socket.emit (events.js:400:28)
node[522582]:     at addChunk (internal/streams/readable.js:293:12)
node[522582]:     at readableAddChunk (internal/streams/readable.js:267:9)
node[522582]:     at Socket.Readable.push (internal/streams/readable.js:206:10)
node[522582]:     at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
node[522582]:
node[522582]: [2022-12-04 02:27:19] WARN Ghost is shutting down
node[522582]: [2022-12-04 02:27:19] WARN Ghost has shut down
node[522582]: [2022-12-04 02:27:19] WARN Your site is now offline
node[522582]: [2022-12-04 02:27:19] WARN Ghost was running for a few seconds
node[522582]: [2022-12-04 02:27:19] INFO Bootstrap client was closed.
systemd[1]: Stopping Ghost systemd service for blog: grantwinney-com...
node[522553]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
node[522553]:         throw error;
node[522553]:         ^
node[522553]: {
node[522553]:   message: "Ghost was able to start, but errored during boot with: alter table `suppressions` add constraint `suppr>
node[522553]: }
systemd[1]: ghost_grantwinney-com.service: Main process exited, code=exited, status=1/FAILURE
systemd[1]: ghost_grantwinney-com.service: Failed with result 'exit-code'.
systemd[1]: Stopped Ghost systemd service for blog: grantwinney-com.
systemd[1]: Started Ghost systemd service for blog: grantwinney-com.

Running ghost doctor didn’t report any errors:

✔ Checking system Node.js version - found v14.21.1
✔ Checking logged in user
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking system compatibility
✔ Checking for a MySQL installation
+ sudo systemctl is-active ghost_grantwinney-com
Instance is currently running
ℹ Validating config [skipped]
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking binary dependencies
✔ Checking free space
✔ Checking systemd unit file
✔ Checking systemd node version - found v14.21.1

The contents of the js file referenced in the log: (full path is /var/www/ghost/versions/5.25.0/core/server/data/migrations/versions/5.25/2022-11-24-10-36-add-suppressions-table.js)

const {addTable} = require('../../utils');

module.exports = addTable('suppressions', {
    id: {type: 'string', maxlength: 24, nullable: false, primary: true},
    email_address: {type: 'string', maxlength: 191, nullable: false, unique: true},
    email_id: {type: 'string', maxlength: 24, nullable: true, references: 'emails.id'},
    reason: {type: 'string', maxlength: 50, nullable: false},
    created_at: {type: 'dateTime', nullable: false}
});

Thanks for any help or suggestions. If someone needs more info, I can try to dig it up.

2

Using MYSQL tools, review the column type of the id column in the emails table, and SELECT values from that table to review their values.

The ALTER statement that’s failing is expecting the column to be of type string with a length of 24.

3

Actually, this post appears to be a dupe. The problem and a solution was recently posted here: Upgrading from 5.23.0 to 5.25.0 breaks ghost with SQL error

1 Like
4

Thanks for the suggestions, and especially for pointing out the other thread. I can confirm that the command he ran also fixed the issue for me, and that upgrading to 5.25.1 was successful.

I upgraded Ubuntu and MySql about a month ago, to the versions officially supported by Ghost, and multiple Ghost upgrades since then have gone off without a hitch. It was only when moving to 5.25.0 that I ran into a snag… probably something in that upgrade specifically that’s not in every upgrade (like updating a table) which brought to light something that had been a problem since I upgraded Ubuntu/MySql.

Anyway, seems to be fine now… thanks again.

1 Like