Bug #13751 find_in_set: Illegal mix of collations

• Submitted: 4 Oct 2005 17:52
• Modified: 20 Oct 2005 20:32
• Reporter: Olaf van der Spek (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: *
• OS: Any (All)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[4 Oct 2005 17:52] Olaf van der Spek

Description:
I'm not sure, but http://dev.mysql.com/doc/mysql/en/charset-collation-charset.html appears to say this issue has been solved >= 4.1.8.

mysql> select * from forum where find_in_set('-1', childlist);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'find_in_set'
mysql>

How to repeat:
set names 'latin1';
drop table if exists forum;
create table forum (childlist varchar(255)) default charset=utf8;
select * from forum where find_in_set('-1', childlist);

[5 Oct 2005 5:05] Jorge del Conde

Thanks for your bug report.  I was able to reproduce this using 5.0.13:

mysql> set names 'latin1';
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists forum;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table forum (childlist varchar(255)) default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from forum where find_in_set('-1', childlist);
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and
(utf8_general_ci,IMPLICIT) for operation 'find_in_set'

[5 Oct 2005 5:05] Jorge del Conde

I was also able to reproduce this problem under Linux

[5 Oct 2005 6:02] Olaf van der Spek

4.1.14-nt is affected too.

[7 Oct 2005 4:57] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30797

[14 Oct 2005 7:02] Alexander Barkov

Fixed in 4.1.16.

[14 Oct 2005 8:40] Alexander Barkov

Merged into 5.0.16.

[20 Oct 2005 20:32] Paul DuBois

Noted in 4.1.16, 5.0.15 changelogs.

[16 Nov 2005 0:31] Rex Lorenzo

Is there a work around for this problem? Even though the bug says it is fixed in MySQL 4.1.16, 5.0.15, neither version is out at the moment.

[13 Mar 2006 18:27] Malcolm Holst

Working with 5.0.18 (running on Windows and Linux), still having the same problem!