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

Question

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.

Solution 1

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.

Solution 2

in case it helps someone, we had the same error while doing a query in different databases of different servers, the one with the error was a migration from the other. In our case it was fixed by changing the "collation_server" in mysql.ini and restarting the mysql service.

Solution 3

Re-importing stored procs and stored functions once you have your encoding and collation settings right will resolve the issues. Ran into the same exact problem. Another helpful mysql native function for troubleshooting: select collation(some_col) from some_table, if you suspect issues with your table data format.

Comments

[Packet Tracer]

I was right on my suppositions, the system is somehow set to utf8_general_ci. If I convert every table collation to utf_general_ci I don't get the error message anymore. So It seems that the problem is that my system is working with utf8_general_ci

[usethe4ce]

Not likely. The encoding, which affects how data is stored, is utf8 regardless, and you're just changing the collation. I ran into a similar problem once, where a stored procedure gave this error, but manually executing the statement within the stored procedure did not, and re-creating the stored procedure made the problem go away. Somewhere MySQL has cached the wrong collation, and once you change the right things that cache gets flushed out.

[Packet Tracer]

in my case, I don't think it was a cache problem, because happened with a new database just migrated, and with new procedures, and even restarting the server. once, i migrated the data with the correct enconding i never got that kind of errors again. thanks anyway, coz your comment can help to other users

[Packet Tracer]

in my case, the problem was not on the stored procedures but on the database data, that was encoded the wrong way. thanks, for contributing to this question

[Doug]

Is there any way to change the collation in place (i.e., without reimporting)?

[Packet Tracer]

you have it in the question... 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;