The one when compatible foreign keys are incompatible
Upgrading our ghost blog ran into issues migrating the database schema. Foreign keys failed with "Referencing column and referenced column in foreign key constraint are incompatible.". Let's fix it.
Upgrading our own ghost blog kept running into issues on the database migration. The application always failed during upgrade with the following output:
[2021-10-27 07:34:44] INFO Ghost is running in production...
[...]
[2021-10-27 07:34:45] WARN Adding permission(Auth Stripe Connect for Members) to role(Administrator)
[2021-10-27 07:34:45] INFO Adding table: members_product_events
[2021-10-27 07:34:46] INFO Dropping table: members_product_events
[2021-10-27 07:34:46] INFO Removing permission(Auth Stripe Connect for Members) from role(Administrator)
[2021-10-27 07:34:46] ERROR alter table `members_product_events` add constraint `members_product_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_product_events_member_id_foreign' are incompatible.
alter table `members_product_events` add constraint `members_product_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_product_events_member_id_foreign' are incompatible.
{"config":{"transaction":false},"name":"02-add-members-products-events-table.js"}
"Error occurred while executing the following migration: 02-add-members-products-events-table.js"This has been due an upgrade to the latest version. For a while I've been digging around general issues with this but didn't found much useful information about it.
Whats happening?
During upgrade ghost blog will perform database migrations. In this specific case it's the step that is performed on 4.13 where a new table will be added (https://github.com/TryGhost/Ghost/blob/main/core/server/data/migrations/versions/4.13/02-add-members-products-events-table.js).
The table is added and afterwards knox (the tool ghost uses for db migrations) is trying to create a foreign key - which fails.
Digging deeper i checked out options why "compatible" fields may not be compatible. The best thing you can think of is that the value are treated somehow different - speaking of databases this is called collations, which affect how charakter values are handled (like case sensitive or not on sorting and so on).
Searching more specific on this, you find a discussion on the ghost community itself (https://forum.ghost.org/t/unable-to-upgrade-ghost-from-v4-2-0-to-v4-3-0-cascade-unknown-code-please-report/22086/41) which states that starting with mysql 8 new tables are assigned another collation by default.
And yes - we've upgraded to mysql 8 meanwhile, which means that our database will create new tables with another collation which in turn will prevent the database from setting up foreign keys between seemingly matching members with different collations.
Checking out the collations of the tables confirmed this:
SELECT Table_Name,Engine,Row_Format,TABLE_COLLATION FROM information_schema.tables WHERE table_schema = DATABASE();
+----------------------------------------+--------+------------+--------------------+
| TABLE_NAME | ENGINE | ROW_FORMAT | TABLE_COLLATION |
+----------------------------------------+--------+------------+--------------------+
| actions | InnoDB | Dynamic | utf8mb4_0900_ai_ci |
| api_keys | InnoDB | Dynamic | utf8mb4_general_ci |
| benefits | InnoDB | Dynamic | utf8mb4_0900_ai_ci |
[...]
| members_stripe_customers_subscriptions | InnoDB | Dynamic | utf8mb4_general_ci |
| members_subscribe_events | InnoDB | Dynamic | utf8mb4_general_ci |
| migrations | InnoDB | Dynamic | utf8mb4_general_ci |
| migrations_lock | InnoDB | Dynamic | utf8mb4_general_ci |
| mobiledoc_revisions | InnoDB | Dynamic | utf8mb4_general_ci |
| oauth | InnoDB | Dynamic | utf8mb4_0900_ai_ci |
[...]
| products_benefits | InnoDB | Dynamic | utf8mb4_0900_ai_ci |
[...]
| webhooks | InnoDB | Dynamic | utf8mb4_general_ci |
+----------------------------------------+--------+------------+--------------------+
45 rows in set (0.00 sec)
Let's fix this
The solution is to either set up mysql to assign a matching collation for new tables or convert older tables to the new collation. In my case i've converted to the newer collation (new is always better?).
SET FOREIGN_KEY_CHECKS=0;
alter table actions convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
alter table api_keys convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
...
alter table users convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
alter table webhooks convert to character set utf8mb4 collate utf8mb4_0900_ai_ci;
SET FOREIGN_KEY_CHECKS=1;You'll need to perform this step for every table in your database. Having done this, ghost will upgrade happily to the latest release.
Why FOREIGN_KEY_CHECKS=0?
Careful readers might have noticed that i've disabled foreign key checks during this script.
This is right. To alter collations we cannot keep FKs enabled because upgrading a table would violate the FK (as the FK cannot be enforced). So we can either drop all FKs, disable them or just disable enforcement for the current session.
Disabling during the current session is the least manipulation you can propably do - and it's the fastest. Seems like a win-win situation here :-)
Lesson learned
Relational databases are working like clockwork - and they are doing this with highest guarantees on consistency matching the specifications!
As the engine cannot decide wheter collations are compatible or not, it doesn't even try. And that's the right choice.
In case you're seeing weird errors that columns are incompatible, keep collations in mind!
