Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) within stored procedure

Packet Tracer picture Packet Tracer · Feb 8, 2012 · Viewed 41.7k times · Source
  1. All the tables are in utf_unicode_ci.

    I've done this to check

    SELECT table_schema, table_name, column_name, character_set_name, collation_name
        FROM information_schema.columns
    WHERE collation_name <> 'utf8_unicode_ci' AND table_schema LIKE 'my_database'
        ORDER BY table_schema, table_name, ordinal_position;
    

    And converted every table just in case

    ALTER TABLE `my_database`.`table_name` DEFAULT COLLATE utf8_unicode_ci;      
    ALTER TABLE `my_database`.`table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;         
    
  2. My database collation settings are in utf8_unicode_ci.

    charsets are

    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | utf8                       |
    | character_set_connection | utf8                       |
    | character_set_database   | utf8                       |
    | character_set_filesystem | binary                     |
    | character_set_results    | utf8                       |
    | character_set_server     | utf8                       |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+
    8 rows in set (0.02 sec)

    collations are

    mysql> show variables like 'colla%';
    +----------------------+-----------------+
    | Variable_name        | Value           |
    +----------------------+-----------------+
    | collation_connection | utf8_unicode_ci |
    | collation_database   | utf8_unicode_ci |
    | collation_server     | utf8_unicode_ci |
    +----------------------+-----------------+
    3 rows in set (0.00 sec)
  3. the error is triggered whether I call the stored procedure via web browser or via mysql bash client. just in case my ubuntu/linux locale settings are:

    $ locale
    LANG=es_ES.UTF-8
    LANGUAGE=es_ES.UTF-8
    LC_CTYPE=es_ES.UTF-8
    LC_NUMERIC="es_ES.UTF-8"
    LC_TIME="es_ES.UTF-8"
    LC_COLLATE=es_ES.UTF-8
    LC_MONETARY="es_ES.UTF-8"
    LC_MESSAGES=es_ES.UTF-8
    LC_PAPER="es_ES.UTF-8"
    LC_NAME="es_ES.UTF-8"
    LC_ADDRESS="es_ES.UTF-8"
    LC_TELEPHONE="es_ES.UTF-8"
    LC_MEASUREMENT="es_ES.UTF-8"
    LC_IDENTIFICATION="es_ES.UTF-8"
    LC_ALL=

The only way I've been able to solve this issue is using convert inside each query that causes the error (or using COLLATE inside the query), but the problem is that there are a lot of quite complex stored procedures so it's hard to identify the "bad" queries and takes a lot of time.

I guess that somehow the variables passed to the stored procedure from my system (ubuntu : mysql client, browser), are being sent in utf8_general_ci, so it makes conflict with ut8_unicode_ci from my database.

It seems that the os is working with utf8_general_ci even though the mysql connection is set to utf_unicode_ci.

Answer

Packet Tracer picture Packet Tracer · Feb 14, 2012

I solved my problem, and was due to a wrong conversion during migration, I was converting to utf_general_ci instead of utf8_unicode_ci, so though the mysql database structure was correct the source data was encoded in the wrong encoding (utf8_general_ci) and inserted in the mysql ddbb that way.

So the thing is that you can have the right character set and collation in your mysql database and still get this "Illegal mix of collations" error, because of the data is enconded with another collation.

Hope this helps to somebody in the future.