Bug #13615 "Illegal mix of collations" bug still not fixed.
Submitted: 29 Sep 2005 17:31 Modified: 12 Oct 2005 14:25
Reporter: Ernie Miller Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Query Browser Severity:S2 (Serious)
Version:1.1.15 OS:Microsoft Windows (Windows XP Professional)
Assigned to: Mike Lischke CPU Architecture:Any

[29 Sep 2005 17:31] Ernie Miller
I noted some old bugs in the DB that reported this problem but they no longer show up as open. This issue is not fixed.

Attempting to select FIND_IN_SET(some_column, 'string1, string2') where the DB is configured to use latin1_swedish_ci and all tables reflect it as appropriate will result in:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'find_in_set'

The client is setting its charset to utf8 instead of observing the DB default latin1 encoding, resulting in the comparison against the user-supplied string on the right-hand side being invalid.  The same query works properly in the command-line client and DBD-mysql 3.x under Perl.

How to repeat:
Configure a table to use latin1 encoding.

 select FIND_IN_SET(some_column, 'string1, string2')

Suggested fix:
I'd be happy if my attempts to override the client's charset using the advanced tab of the connection window would be stored and observed, but it doesn't seem to be.
[12 Oct 2005 14:25] Mike Lischke
Please note that one could see it as a gotcha, but it is not a real bug. You should keep in mind that our tools strictly work with Unicode where possible, particular for the connection/server charset. So giving a string constant will implicitely set it as Unicode string (utf-8 encoded). If you then give the expression a column with a different charset then you get the mentioned error.

What you can do is either to set the server charset to your desired value:

1) start a transaction in QB
2) set names latin1
3) select FIND_IN_SET(ansi_column, 'string1,string2')

or you consequently use Unicode, which is the better alternative, as you can avoid all the charset trouble in the future (for the price to have slightly higher storage requirements).

[12 Oct 2005 14:27] Mike Lischke
I forgot to mention, you have actually a third option. Set the strings charset explicitely to latin1:

select FIND_IN_SET(latin1_column, _latin1'string1,string2')

Note the leading underscore for the charset introducer.