| Bug #35896 | 4.1.22 Illegal mix of collations | ||
|---|---|---|---|
| Submitted: | 8 Apr 2008 10:22 | Modified: | 30 Apr 2008 14:54 |
| Reporter: | Omry Yadan | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 4.1.22 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[8 Apr 2008 19: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 5: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 5: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 12: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 13: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 15: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 14:14]
Omry Yadan
is there a workaround for this problem?
[30 Apr 2008 14:26]
MySQL Verification Team
I recommend you to upgrade to 5.0 version following the Manual's upgrade instructions.
[30 Apr 2008 14: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.