| Bug #35896 | 4.1.22 Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 8 Apr 2008 12:22 | Modified: | 30 Apr 2008 16:54 |
| Reporter: | Omry Yadan | ||
| Status: | Won't fix | ||
| Category: | Server: Charsets | Severity: | S2 (Serious) |
| Version: | 4.1.22 | OS: | Linux |
| Assigned to: | Target Version: | ||
| Triage: | D2 (Serious) | ||
[8 Apr 2008 21:14]
Sveta Smirnova
Thank you for the report. Yes, this bug has been fixed in version 5.0. Seems to be duplicate of bug #21505. Workarounds: 1. SET NAMES latin1 COLLATE latin1_general_ci; 2. SELECT u, str FROM t right join (SELECT 'foobar' collate latin1_general_ci AS u) u2 on u regexp str; 3. Start server as mysqld --character-set-server=latin1 --collation-server=latin1_general_ci --skip-character-set-client-handshake
[16 Apr 2008 7:44]
Omry Yadan
The proposed workarounds (as well as my own _utf8) workaround does not work. they yield a similar error on my unit test against 4.1.22: Database error: Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation 'regexp' MySQL Version: 4.1.22-standard SQL Query: SELECT useragent FROM firestats_useragent_classes right join (SELECT 'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-TW; rv:1.8.1.6) Gecko/20070725 Firefox/2.0.0.6' collate latin1_general_ci AS useragent) urls ON useragent REGEXP wildcard GROUP BY useragent HAVING count(wildcard) > 0
[16 Apr 2008 7:46]
Omry Yadan
my _utf8 workaround causes: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'regexp' if you need help reproducing this, please let me know.
[22 Apr 2008 14:55]
Sveta Smirnova
Thank you for the feedback. Please provide output of SHOW CREATE TABLE firestats_useragent_classes, SHOW VARIABLES LIKE 'char%' and SHOW VARIABLES LIKE 'coll%'
[22 Apr 2008 15:18]
Omry Yadan
Hi Sveta,
here is the requested output:
SHOW CREATE TABLE firestats_useragent_classes
+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| firestats_useragent_classes | CREATE TABLE `firestats_useragent_classes` (
`id` int(11) NOT NULL auto_increment COMMENT 'Primary key',
`wildcard` varchar(100) NOT NULL default '' COMMENT 'Bots wildcard',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Bots table' |
+-----------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SHOW VARIABLES LIKE 'char%';
+--------------------------+-------------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------+-------------------------------------------------------------------+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir |
/home/omry/dev/tools/mysql/bin/mysql-4.1.22/share/mysql/charsets/ |
+--------------------------+-------------------------------------------------------------------+
SHOW VARIABLES LIKE 'coll%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
[29 Apr 2008 17:41]
Susanne Ebrecht
Verified as described by using MySQL 4.1.25 (bk tree). This only fails in version 4.1. It works fine in version 5.0.
[30 Apr 2008 16:14]
Omry Yadan
is there a workaround for this problem?
[30 Apr 2008 16:26]
Miguel Solorzano
I recommend you to upgrade to 5.0 version following the Manual's upgrade instructions.
[30 Apr 2008 16:54]
Omry Yadan
I am developing an application that many users are running, and it supports mysql 4.1.x. While MySQL 5.x deployment is getting better, 41% of my users are running older versions (31% of them runs 4.1).
Description: Illegal mix of collations on 4.1.22, works fine on 5.x. I am not sure my workaround is safe (using _utf8 before literals). please advice. How to repeat: -- SETUP CREATE TABLE `t` (`str` varchar(100) collate latin1_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; INSERT INTO `t` (`str`) VALUES('foo'); -- FAILS: SELECT u FROM t right join (SELECT 'foobar' AS u) u2 ON u REGEXP str GROUP BY u HAVING count(str) > 0; -- RESPONSE: -- ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin1_general_ci,IMPLICIT) for operation 'regexp' -- WORKS: SELECT u FROM t right join (SELECT _utf8'foobar' AS u) u2 ON u REGEXP str GROUP BY u HAVING count(str) > 0; Suggested fix: my workaround is to add _utt8 before text literals. not sure if this is safe.