Converting character sets and foreign key constaints

Question

I'm trying to convert the character set and collation of my MySQL database and all containing tables from utf8/utf8_unicode_ci to utf8mb4/utf8mb4_unicode_ci.

I will refer to my database name as: MyDB
I will refer to my table(s) as: MyTable1 [, MyTable2, MyTable3, etc]

I have set the database character set and collation using:

ALTER DATABASE MyDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Then I used this SQL script to generate all of my table commands:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ") 
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'MyDB';

This SQL generated a table with these contents:

ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

Now my table has a lot of foreign key constraints and if I directly execute this query I get foreign key constraint errors because of incompatible character sets half way through the modification process:

#1025 - Error on rename of './MyDB/#sql-bba_3975' to './MyDB/MyTable1' (errno: 150)

So I figured I could just set FOREIGN_KEY_CHECKS to 0 before I run my query. I remember doing this before when I had to convert my database from latin1 to utf8.

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE MyDB.MyTable1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE MyDB.MyTable2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
... etc

I still get the same error for the same table.

SHOW ENGINE INNODB STATUS;

...
LATEST FOREIGN KEY ERROR
------------------------
161103  9:59:06 Error in foreign key constraint of table MyDB/MyTable1:
there is no index in the table which would contain
the columns as the first columns, or the data types in the
table do not match the ones in the referenced table
or one of the ON ... SET NULL columns is declared NOT NULL. Constraint:
,
 CONSTRAINT "MyTable1_ibfk_4" FOREIGN KEY ("MyTable1_Column1") REFERENCES "MyTable2" ("MyTable2_PK") ON UPDATE CASCADE
...

How could I still be getting foreign key errors if I have disabled foreign key checks?

I have tried some strange experiments...

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All the selects return 1 when it should return 1 0 1.

But if I execute this:

SELECT @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS=0;
SELECT @@FOREIGN_KEY_CHECKS;
#SET FOREIGN_KEY_CHECKS=1;
SELECT @@FOREIGN_KEY_CHECKS;

All of the selects return 0 when it should return 1 0 0.

This causes me to think that something is fundamentally wrong with my understanding of how the server session variables work.

Note:

I know I could remove all of the foreign keys in my database before I do the conversion but I'd rather not do that if I don't have to. My database has a lot of foreign keys and I don't want to miss one during the conversion.

Answer 1

Sort the ALTERs so that the FK checks will stop complaining. Do a child before a parent (or is it the other way??).

If you have any circular references, then, yeah, remove and re-apply the FKs.

(You have pointed out yet another reason to eschew FKs.)

While you are at it, consider utf8mb4_unicode_520_ci; it comes from a newer Unicode standard.