Bug #15201 | Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COE | ||
---|---|---|---|
Submitted: | 23 Nov 2005 20:34 | Modified: | 27 Jan 2006 15:32 |
Reporter: | John Campbell | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.15 | OS: | Linux (Linux) |
Assigned to: | CPU Architecture: | Any |
[23 Nov 2005 20:34]
John Campbell
[26 Nov 2005 16:51]
Valeriy Kravchuk
Thank you for a problem report. Looks like I'll need your tables definitions and my.cnf settings, because I was not able to repeat the problem you described with a simplified test case on 5.0.17-BK on my Linux box: mysql> create table t15201 (c1 int, c2 int); Query OK, 0 rows affected (0,00 sec) mysql> insert into t15201 values(1, 1); Query OK, 1 row affected (0,00 sec) mysql> insert into t15201 values(1, 2); Query OK, 1 row affected (0,00 sec) mysql> insert into t15201 values(2, 1); Query OK, 1 row affected (0,00 sec) mysql> insert into t15201 values(2, 2); Query OK, 1 row affected (0,00 sec) mysql> insert into t15201 values(3, 1); Query OK, 1 row affected (0,01 sec) mysql> insert into t15201 values(3, 2); Query OK, 1 row affected (0,01 sec) mysql> insert into t15201 values(3, 3); Query OK, 1 row affected (0,00 sec) mysql> select 1, c1, count(distinct c2) as c2 from t15201 group by c1 -> union -> select 2, 0, count(distinct c2) as c2 from t15201; +---+------+----+ | 1 | c1 | c2 | +---+------+----+ | 1 | 1 | 2 | | 1 | 2 | 2 | | 1 | 3 | 3 | | 2 | 0 | 3 | +---+------+----+ 4 rows in set (0,00 sec) mysql> alter table t15201 modify c1 char(20); Query OK, 7 rows affected (0,01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> desc t15201; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | c1 | char(20) | YES | | NULL | | | c2 | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0,01 sec) mysql> select 1, c1, count(distinct c2) as c2 from t15201 group by c1 union select 2, 'Total', count(distinct c2) as c2 from t15201; +---+-------+----+ | 1 | c1 | c2 | +---+-------+----+ | 1 | 1 | 2 | | 1 | 2 | 2 | | 1 | 3 | 3 | | 2 | Total | 3 | +---+-------+----+ 4 rows in set (0,01 sec) mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0,00 sec) Any other ideas on how to repeat the problem you described are welcomed.
[26 Nov 2005 20:18]
David McBride
I also get the same error: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '=' Running 4.1.15 CREATE TABLE `servicelog` ( `id` int(11) NOT NULL auto_increment, `station_id` int(11) NOT NULL default '0', `airline_id` int(11) NOT NULL default '0', `tail_number` varchar(10) NOT NULL default '', `local_capture_time` datetime NOT NULL default '0000-00-00 00:00:00', `server_capture_time` datetime NOT NULL default '0000-00-00 00:00:00', `agent` varchar(20) NOT NULL default '', `user_id` int(11) NOT NULL default '0', `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP, `service_id` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=ascii; and the following query fails: select * from servicelog where upper(trim(tail_number)) = Upper('N127F'); Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
[27 Dec 2005 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[27 Dec 2005 15:32]
Valeriy Kravchuk
To John Campbell: So, can you provide the additional information to repeat the problem you described? To David McBride: I've tried to repeat the problem you described on 4.1.17-BK on Linux: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.17 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE `servicelog` ( -> `id` int(11) NOT NULL auto_increment, -> `station_id` int(11) NOT NULL default '0', -> `airline_id` int(11) NOT NULL default '0', -> `tail_number` varchar(10) NOT NULL default '', -> `local_capture_time` datetime NOT NULL default '0000-00-00 00:00:00', -> `server_capture_time` datetime NOT NULL default '0000-00-00 00:00:00', -> `agent` varchar(20) NOT NULL default '', -> `user_id` int(11) NOT NULL default '0', -> `last_update` timestamp NOT NULL default CURRENT_TIMESTAMP, -> `service_id` int(11) NOT NULL default '0', -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=ascii; Query OK, 0 rows affected (0.03 sec) mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ 3 rows in set (0.00 sec) mysql> insert into servicelog (tail_number) values('N127F'); Query OK, 1 row affected (0.00 sec) mysql> select * from servicelog where upper(trim(tail_number)) = Upper('N127F'); +----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+ | id | station_id | airline_id | tail_number | local_capture_time | server_capture_time | agent | user_id | last_update | service_id | +----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+ | 1 | 0 | 0 | N127F | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | | 0 | 2005-12-27 18:17:38 | 0 | +----+------------+------------+-------------+---------------------+---------------------+-------+---------+---------------------+------------+ 1 row in set (0.00 sec) So, please, explain what shell I do to get this error message in your case.
[30 Dec 2005 15:38]
[ name withheld ]
From David McBride: Apparently this is MY problem: SHOW VARIABLES LIKE 'collation%' Variable_name Value collation_connection utf8_general_ci collation_database ascii_general_ci collation_server latin1_swedish_ci I'm guessing that having different collations is what's causing my SQL query failure. So how do I get all collations to be the same? Thx David
[28 Jan 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".