DB Migration script 6.24.0 -> 6.25.1 error dropping nullable automated_emails.email_design_setting_id

[KevinVR]

Issue Summary

Related to #27062. Upgrading from 6.24.0 to 6.25.1 gives database migration errors

All the migration scripts run fine, except the last one 6.25/2026-03-31-20-31-19-drop-nullable-on-automated-emails-email-design-setting-id.js

This results in

...
Backfilling automated_emails.email_design_setting_id
Dropping nullable: automated_emails.email_design_setting_id
Rolling back: alter table `automated_emails` modify `email_design_setting_id` varchar(24) not null - Cannot change column 'email_design_setting_id': used in a foreign key constraint 'automated_emails_email_design_setting_id_foreign'.

It looks like db script 6.25/2026-03-30-22-16-43-add-email-design-setting-id-to-automated-emails.js creates this new FK. But then in 6.25/2026-03-31-20-31-19-drop-nullable-on-automated-emails-email-design-setting-id.js it's not allowed to modify the column due to this FK being present.

This automated_emails.email_design_setting_id column did not exist in 6.24.

Steps to Reproduce

Existing Ghost blog with 6.24.0 version
Upgrade to 6.25.1

MySQL version 8.4

Ghost Version

6.24.0

Node.js Version

Node.JS version From Ghost Docker image

How did you install Ghost?

Ghost Docker image

Database type

MySQL 8

Browser & OS version

No response

Relevant log / error output

Backfilling automated_emails.email_design_setting_id
Dropping nullable: automated_emails.email_design_setting_id
Rolling back: alter table `automated_emails` modify `email_design_setting_id` varchar(24) not null - Cannot change column 'email_design_setting_id': used in a foreign key constraint 'automated_emails_email_design_setting_id_foreign'.

Code of Conduct

• I agree to be friendly and polite to people in this repository

[cmraible]

Hi @KevinVR, thanks for reporting! I haven't be able to reproduce this yet, but I'd like to figure out what's going wrong here for you. Would you mind sharing some more details about your hosting setup?

• Did you have welcome emails enabled on your site before this migration failed? It's in Ghost Admin > Settings > Welcome Emails
• Have you previously migrated from MariaDB or a separate Ghost installation method? That seems to be the common theme from Cannot change column 'email_design_setting_id': used in a foreign key constraint 'automated_emails_email_design_setting_id_foreign' #27062
• Can you please share the output of the following query run on your mysql database? SHOW VARIABLES LIKE 'sql_mode';

[github-actions]

Note from our bot: The needs:info label has been added to this issue. Updating your original issue with more details is great, but won't notify us, so please make sure you leave a comment so that we can see when you've updated us.

[misterorion]

Also seeing this error when upgrading a containerized Ghost install. The database is AWS RDS MySQL 8.4.8. This database was created years ago so I don't recall if it was ever imported from MariaDB. Checked the welcome emails setting and it was turned off. Rolling the docker tag back to 6.24.0 brought the site back online.

After poking around and manually creating that column I was able to launch Ghost 6.26.0.

ALTER TABLE `automated_emails` 
ADD COLUMN `email_design_setting_id` varchar(24) NULL;

Output of SHOW VARIABLES LIKE 'sql_mode'; is STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

[muratcorlu]

I had this issue on one of my sites too. In my case the root cause was a collation mismatch between automated_emails and emaild_design_settings tables. One had utf8mb4_general_ci and other was utf8mb4_0900_ai_ci.

I fixed it by setting all tables' collation to same:

ALTER TABLE automated_emails CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

I couldn't understand the reason since I didn't manually change collation of any tables, and this issue occured only one site.

[KevinVR]

@cmraible

1. Welcome emails: not in use. Tried updating it and retrying the upgrade, no effect.
2. Previously migrated: I think this was a clean docker install a while back for which I used the manual import/export feature.
3. SHOW VARIABLES LIKE 'sql_mode'; Output: sql_mode,"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

@misterorion suggested below:

ALTER TABLE automated_emails
ADD COLUMN email_design_setting_id varchar(24) NULL;

^ Thanks for the suggestion. That did the trick since as I understand the db upgrade script won't try to create the column if it already exists, meaning it own't create the FK relation in the upgrade script, and the dropping nullable should then work!

This workaround works, although we do end up without a FK on the column this way, which might not be what the Ghost schema expects 🤔 .

[maquchizi]

I ran into this issue when trying to upgrade from 6.21.1 -> 6.27.0. We're not currently using automated emails so here's the workaround I used:

1. Add the email_design_setting_id column to the automated_emails table

ALTER TABLE automated_emails
ADD COLUMN email_design_setting_id varchar(24) NULL;

2. Upgrade to 6.27.0
3. Modify the column and add the expected foreign key constraint

ALTER TABLE automated_emails
MODIFY email_design_setting_id varchar(24) NOT NULL,
ADD CONSTRAINT automated_emails_email_design_setting_id_foreign
FOREIGN KEY (email_design_setting_id) REFERENCES email_design_settings (id);

Probably worth mentioning that all my tables are utf8mb4_0900_ai_ci, I'm using MySQL 8.4.7, and the output of SHOW VARIABLES LIKE 'sql_mode'; is NO_ENGINE_SUBSTITUTION

Thank you @misterorion for the original snippet.