Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

Question

How can i solve

"Illegal mix of collations (latin5_turkish_ci,IMPLICIT) 
and (latin1_swedish_ci,COERCIBLE) 
for operation '='" problem?

My query: select * from up where name='camış'

connection options:

SET NAMES 'latin5'
SET character_set_connection = 'latin5'
SET collation_connection = latin5_turkish_ci

-- I changed the collate as latin5_turkish_ci but doesn't work. Between, after a few minutes (lots of queries), the problem disappears on its own.

dev-null-dweller dev-null-dweller · Accepted Answer · 2010-12-14T20:02:14

This rather bad idea but should work:

SELECT * 
  FROM up 
 WHERE CONVERT(name USING latin5) COLLATE latin5_turkish_ci = 'camış'

Good idea is to convert all your column to desired character set (and this should be UTF-8).

[+] By converting I mean not changing default collation of table/column, but to convert all data in it. So I would create new column with target character set, populate it with UPDATE table SET new_column = CONVERT(old_column USING characterset), drop old column and rename new one.

Illegal mix of collations (latin5_turkish_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

1 Answers