Ameya Patkar Ameya Patkar - 2 years ago 296
SQL Question

getting error Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

1- I am getting the below error when trying to do a select through a stored procedure in MySQL

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.

2 - SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' or Variable_name LIKE 'collation%';

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'utf8mb4'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'utf8mb4'
'character_set_system', 'utf8'
'collation_connection', 'utf8_general_ci'
'collation_database', 'utf8mb4_unicode_ci'
'collation_server', 'utf8mb4_unicode_ci'

All the tables are in utf8mb4_unicode_ci and character set is utf8mb4.

Link to store procedure code

Answer Source

I see that you are using temporary tables too. Please define the same collation and character sets for temporary tables too. Use column-level collation in the SELECT query itself (also, where ever you put a join)

select * from some_table
where some_column collate 
utf8_general_ci = 'Name of the City';

This should work for you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download