2

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.

  • Script out the foreign keys, drop them, convert the collation, and recreate them. You can get info from INFORMATION_SCHEMA.KEY_COLUMN_USAGE using referenced_table_name where it is not null. – Randolph West Nov 3 '16 at 18:56
  • @RandolphWest: Looks like the OP already knows he has that option (see the last paragraph). If you believe that dropping/recreating the FKs is the only way, consider posting that as an answer. – Andriy M Nov 3 '16 at 20:13
  • Do you guys have any insight on why setting FOREIGN_KEY_CHECKS to 0 is not working in my case? – Bradley Odell Nov 3 '16 at 20:31
  • Why You decide - FOREIGN_KEY_CHECKS to 0 is not working? Look like - FOREIGN_KEY_CHECKS to 1 is not working. Check what in error message - include datatypes, historic database could include something which not support by new settings, so all work unless You not touch it. – a_vlad Nov 3 '16 at 21:04
  • 1
    I tell You - check datatypes - dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html. "even if foreign_key_checks = 0, MySQL does not permit the creation of a foreign key constraint where a column references a nonmatching column type" or more relevant - "Also, if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine. To change the storage engine, you must drop any foreign key constraints first" – a_vlad Nov 3 '16 at 22:34
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.

  • Unfortunately, I'm still on MySQL 5.5 (Ubuntu 14.04 LTS repo) which doesn't have utf8mb4_unicode_520_ci or else I'd be using it. – Bradley Odell Nov 3 '16 at 23:50
  • 1
    OK. BTW, MySQL 8.0 has utf8mb4_unicode_900_ci, a still newer standard. Don't get too far behind; upgrading gets harder. – Rick James Nov 3 '16 at 23:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.