I’ve had a glitch on upgrading to 4.1.0. I got the following message.
Message: Ghost was able to start, but errored during boot with: alter table members_login_events add constraint members_login_events_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_login_events_member_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 08-add-members-login-events-table.js
I’ve temporarily rolled back to the v3 so the site is still accessible. Any thoughts? (I didn’t access the log file before rolling back, which was a mistake, so if necessary, I can break it again to get hold of the logs).
I had the same problem. It was due to old database tables having a different UTF-8 collation (text encoding) to new tables, which was probably related to a MySQL update to version 8.x that happened when updating Ubuntu.
In my case I manually converted all the old tables to the new collation to fix it.
All tables in my ghost_production database have the same collation (utf8mb4_general_ci).
Did you recently upgrade to a newer version of MySQL? If so that might explain the issue - MySQL v8 changes the default table collation to something other than utf8mb4_general_ci, so any new tables that Ghost attempts to create as part of v4 migrations will be created with the new collation.
You should be able to check your database’s default charset/collation using the following query:
SELECT SCHEMA_NAME,
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME='ghost_production';
If the default charset/default collation are anything other than utf8mb4 and utf8mb4_general_ci, respectively, you’ll need to run the following command to update the defaults:
ALTER DATABASE `ghost_production`
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
Hi,
Thanks for that. The default collation was out of sync. But after updating the defaults and running again, I got the same error. I’ll have another look tomorrow.
Cheers, Will
I’m also getting this with the basic install on DigitalOcean. Haven’t had time to troubleshoot it, but running @acburdine’s sql commands still produced an error on startup, so there’s something else going on perhaps.
I ran the changes that @acburdine suggested on the Mysql database tables, but still get the same error. Any help in troubleshooting is appreciated.
Some more debug information that may be helpful.
Message: Ghost was able to start, but errored during boot with: alter table members_products add constraint members_products_member_id_foreign foreign key (member_id) references members (id) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column ‘member_id’ and referenced column ‘id’ in foreign key constraint ‘members_products_member_id_foreign’ are incompatible.
Help: Error occurred while executing the following migration: 02-add-members-products-table.js
