MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps

When creating a foreign key constraint the referenced columns on both sides need to have the same data types, including same character set. E.g.

CREATE TABLE t1(

  id SERIAL,

   msg VARCHAR(100) CHARACTER SET utf8mb3, 

  KEY(msg)

);

CREATE TABLE t2(

  id SERIAL,

  msg varchar(100) CHARACTER SET utf8mb4,

  CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)

);

will fail with "Foreign key constraint is incorrectly formed" due to the mix of utf8mb3 and utf8mb4. Even with foreign_key_checks=OFF this will still not work.

Changing the character set to "utf8mb3" I can create the 2nd table now, as expected:

CREATE TABLE t2(

  id SERIAL,

  msg varchar(100) CHARACTER SET utf8mb3,

  CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)

);

Now the problem is that starting with 10.6 I can change the character set "under the hood" without getting an error at all, regardless of foreign_key_checks being ON or OFF:

ALTER TABLE t2

  MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4;

With 10.5 and earlier the situation is a bit better, the MODIFY COLUMN will lead to

Cannot change column 'msg': used in a foreign key constraint 'fk_t1'

when foreign_key_checks are ON, but will succeed when checks are OFF; unlike the CREATE TABLE above that will fail regardless of checks being ON or OFF.

And with that we now have a table with a broken foreign key constraint, and a schema that we can dump with mysqldump, but not restore as the dump now contains a CREATE TABLE statement with a FK CONSTRAINT that will lead to an

Foreign key constraint is incorrectly formed

error even though mysqldump takes care to turn off foreign_key_checks for the duration of a restore.

So two things need to be fixed here IMHO:

• on 10.6 or later: MODIFY TABLE definitely needs to fail if foreign_key_checks are ON

• on all versions: with CREATE TABLE not allowing to create a FK constraint with column type mismatches regardless of foreign_key_checks, MODIFY COLUMN should not allow for this even with checks turned OFF either; or both should allow this when checks are OFF, for symmetry reasons