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.

Answer 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.

Answer 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.

Answer 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.

Answer 4

I have solved my problem after adding the CHARSET utf8 in a parameter list. may be this will help full to other,

   PROCEDURE `USP_USR_AuthenticateUser`(
      IN ip_username VARCHAR(50) CHARSET utf8**,
      IN ip_pwd NVARCHAR(256)
   )