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:
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.
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:
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?).
You'll need to perform this step for every table in your database. Having done this, ghost will upgrade happily to the latest release.
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 :-)
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!