MySQL: Illegal mix of collations

collationencodingMySQLutf-8

I've tried using a stored procedure to create indices, and got the following error:

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and 
                    (utf8_unicode_ci,IMPLICIT) for operation '='

There's no line number or any other debugging hint.

  • What's the best way to debug this error?
  • Are there any good guidelines to avoid encoding inconsistencies?

Best Answer

After some trial and error, I've learned how and where to apply COLLATE:

Converted lines like:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema = given_database
AND   table_name   = given_table
AND   index_name   = given_index;

To:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema COLLATE utf8_unicode_ci = given_database
AND   table_name   COLLATE utf8_unicode_ci = given_table
AND   index_name   COLLATE utf8_unicode_ci = given_index;
Related Question