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.

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