Ghost error update from v4 to v5 - foreign key constraint
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'Solution on MySql
SET GLOBAL FOREIGN_KEY_CHECKS=0; Restart your Ghost v5 instance.
Finally remember to put back foreign_key_checks to 0.
SET GLOBAL FOREIGN_KEY_CHECKS=1; If you are using AWS RDS you will receive the following error because AWS prevent it, in this case follow the below way using parameter groups.
SQL Error [1227] [42000]: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
Solution on AWS RDS - MySql
Set up a custom parameter group and attach it to your RDS instance.
Than temporary edit in your parameter group the "foreign_key_checks" param from 1 to 0.
Restart your Ghost v5 instance.
Finally remember to put back foreign_key_checks to 0.
Full log
ghost_1 | [2023-04-29 10:04:19] INFO Ghost is running in production...
ghost_1 | [2023-04-29 10:04:19] INFO Your site is now available on https://www.yuribacciarini.com/
ghost_1 | [2023-04-29 10:04:19] INFO Ctrl+C to shut down
ghost_1 | [2023-04-29 10:04:19] INFO Ghost server started in 0.845s
ghost_1 | [2023-04-29 10:04:19] WARN Database state requires migration.
ghost_1 | [2023-04-29 10:04:19] INFO Creating database backup
ghost_1 | [2023-04-29 10:04:19] INFO Database backup written to /var/lib/ghost/content/data/yuba.ghost.2023-04-29-10-04-19.json
ghost_1 | [2023-04-29 10:04:19] INFO Running migrations.
ghost_1 | [2023-04-29 10:04:19] INFO Adding products.monthly_price column
ghost_1 | [2023-04-29 10:04:19] INFO Adding products.yearly_price column
ghost_1 | [2023-04-29 10:04:19] INFO Adding products.currency column
ghost_1 | [2023-04-29 10:04:20] INFO Updating 1 Tiers with price and currency information
ghost_1 | [2023-04-29 10:04:20] INFO Adding table: subscriptions
ghost_1 | [2023-04-29 10:04:20] INFO Adding table: members_feedback
ghost_1 | [2023-04-29 10:04:20] INFO Adding newsletters.feedback_enabled column
ghost_1 | [2023-04-29 10:04:20] INFO Dropping nullable: subscriptions.tier_id
ghost_1 | [2023-04-29 10:04:20] 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'.
ghost_1 | [2023-04-29 10:04:20] INFO Removing newsletters.feedback_enabled column
ghost_1 | [2023-04-29 10:04:20] INFO Dropping table: members_feedback
ghost_1 | [2023-04-29 10:04:20] INFO Dropping table: subscriptions
ghost_1 | [2023-04-29 10:04:20] INFO Removing currency and price information for all tiers
ghost_1 | [2023-04-29 10:04:20] INFO Removing products.currency column
ghost_1 | [2023-04-29 10:04:20] INFO Removing products.yearly_price column
ghost_1 | [2023-04-29 10:04:20] INFO Removing products.monthly_price column
ghost_1 | [2023-04-29 10:04:20] INFO Rollback was successful.
ghost_1 | [2023-04-29 10:04:20] 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'
ghost_1 |
ghost_1 | 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'
ghost_1 |
ghost_1 | {"config":{"transaction":true},"name":"2022-10-18-05-39-drop-nullable-tier-id.js"}
ghost_1 | "Error occurred while executing the following migration: 2022-10-18-05-39-drop-nullable-tier-id.js"
ghost_1 |
ghost_1 | Error ID:
ghost_1 | 300
ghost_1 |
ghost_1 | Error Code:
ghost_1 | ER_FK_COLUMN_CANNOT_CHANGE