Migration failed update from 5.19.3 to 5.22.1 with ER_FK_COLUMN_CANNOT_CHANGE

Developer help Migrations
1

Hi,

When I try to update my ghost from 5.19.3 to 5.22.1, I encounter the following error:

[2022-11-01 10:01:50] INFO Ghost is running in production...
[2022-11-01 10:01:50] INFO Your site is now available on https://blog.***.com/
[2022-11-01 10:01:50] INFO Ctrl+C to shut down
[2022-11-01 10:01:50] INFO Ghost server started in 1.689s
[2022-11-01 10:01:50] WARN Database state requires migration.
[2022-11-01 10:01:51] INFO Creating database backup
[2022-11-01 10:01:51] INFO Database backup written to: /var/lib/ghost/content/data/***com.ghost.2022-11-01-10-01-51.json
[2022-11-01 10:01:51] INFO Running migrations.
[2022-11-01 10:01:51] INFO Dropping nullable: subscriptions.tier_id
[2022-11-01 10:01:51] INFO Rolling back: alter table `subscriptions` modify  `tier_id` varchar(24) not null  - Cannot change column 'tier_id': used in a foreign key constraint 'subscriptions_tier_id_foreign'.
[2022-11-01 10:01:51] INFO Rollback was successful.
[2022-11-01 10:01:51] ERROR alter table `subscriptions` modify  `tier_id` varchar(24) not null  - Cannot change column 'tier_id': used in a foreign key constraint 'subscriptions_tier_id_foreign'

alter table `subscriptions` modify  `tier_id` varchar(24) not null  - Cannot change column 'tier_id': used in a foreign key constraint 'subscriptions_tier_id_foreign'

{"config":{"transaction":true},"name":"2022-10-18-05-39-drop-nullable-tier-id.js"}
"Error occurred while executing the following migration: 2022-10-18-05-39-drop-nullable-tier-id.js"

Error ID:
    300

Error Code: 
    ER_FK_COLUMN_CANNOT_CHANGE

----------------------------------------

Error: alter table `subscriptions` modify  `tier_id` varchar(24) not null  - Cannot change column 'tier_id': used in a foreign key constraint 'subscriptions_tier_id_foreign'
    at /var/lib/ghost/versions/5.22.1/node_modules/knex-migrator/lib/index.js:1032:19
    at Packet.asError (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/5.22.1/node_modules/mysql2/lib/connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

[2022-11-01 10:01:51] WARN Ghost is shutting down
[2022-11-01 10:01:51] WARN Ghost has shut down
[2022-11-01 10:01:51] WARN Your site is now offline
[2022-11-01 10:01:51] WARN Ghost was running for a few seconds

Ghost Version

5.19.3

Node.js Version

v16.18.0

Database type

MySQL 8 (AWS RDS)

1 Like
2

The default SQL mode in MySQL 8.0 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION.

Ghost expects the default sql mode, and requires STRICT_TRANS_TABLES

I’ve just update sql_mode to default value and now it works!

2 Likes
3

Can you please elaborate what did you exactly ? how did you change the sql_mode ? did you do 'set sql_mode= STRICT_TRANS_TABLES ? @NickN

4

@ragab - (this is for RDS). I created a custom parameter group and set the sql_mode value there. Then updated the instance to use the custom parameter group and applied changes

2 Likes
5

Found this ghost error page helpful in resolving this specific issue myself.