Illegal mix of collations in stored procedure

Question

my stored procedure in MySQL fails with Mysql::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='.

The procedure fails when in SELECT clause it tries to compare a VARCHAR column with VARCHAR parameter passed to this procedure.

All the columns in my tables have utf8_unicode_ci collation. Database collation is the same. I have even specified collation in `/config/database.yml.

However, when I run /script/console I have following variables set:

>> ActiveRecord::Base.connection.select_rows "show variables like '%colla%'"
=> [["collation_connection", "utf8_general_ci"], ["collation_database", "utf8_unicode_ci"], ["collation_server", "utf8_general_ci"]]

And possibly the most interesting fact is that I have another database on the same MySQL server with same collations (even querying for collation variables from Rails console gives same results) which runs this stored procedure without any problem.

Thanks for your help.

Chalcopyrite

I just found out that one of my tables has collation set to utf8_general_ci set which is inproper in my database and this is the reason. Thank you for spending time on this question. – Chalcopyrite

Answer 1

To quick fix,

SELECT * FROM YOUR_TABLE 
WHERE YOUR_COL=@YOUR_VARIABLES COLLATE utf8_general_ci;

OR

SELECT * FROM YOUR_TABLE 
WHERE YOUR_COL=@YOUR_VARIABLES COLLATE unicode_ci;
/* depends on the collation for YOUR_COL */

Permanent fix

• http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_collation_server
• http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_collation_database

You probably would need to re-create your database using the right/same collation

Harry

+1 Thanks for your answer. Links which you have posted are very useful and they introduce some information not mentioned (or not clearly mentioned) in section of manual related to character sets. Actually, your answer led me indirectly to real reason. – Chalcopyrite

In addition to @ajreal's answer: Maybe it is good to know that when you use multiple statements, ALL OF THEM need the COLLATE statement. So it would be SELECT * FROM YOUR_TABLE WHERE YOUR_COL1=@YOUR_VARIABLES1 COLLATE unicode_ci AND YOUR_COL2=@YOUR_VARIABLES2 COLLATE unicode_ci AND YOUR_COL3=@YOUR_VARIABLES3 COLLATE unicode_ci; Took me some while to figure that out. – Mooncalf

Answer 2

If you moved from MySQL 5.7 to 8 the stored procedure variables must be prefixed with the @ symbol to prevent this same error.

In 5.7

SELECT * FROM tablename WHERE column=stored_procedure_varchar;

would work fine and this will also save fine in 8.0, but in 8.0 it needs to be correctly formatted as below

SELECT * FROM tablename WHERE column=@stored_procedure_varchar;

Iggie

Answer 3

Change stored procedure parameter collation property like below make sure table column also has the same collation property

example: CREATE PROCEDURE create_new_profile ( p_MID varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci , p_firstname varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci, p_email varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci, p_phone varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci )

Reverent