| Bug #60160 | Error Code: 1271 Illegal mix of collations for operation 'concat' | ||
|---|---|---|---|
| Submitted: | 17 Feb 2011 13:51 | Modified: | 30 Apr 2011 22:36 |
| Reporter: | Jose Luis Artigas | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Workbench: SQL Editor | Severity: | S3 (Non-critical) |
| Version: | 5.2.32CE Revision 7115 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[17 Feb 2011 14:22]
Valeriy Kravchuk
I was not able to repeat this in Workbench while connected to local 5.1.54 server version.
It also works OK in command line:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.1.54-community MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table userp (a varchar(45), b INT(10));
Query OK, 0 rows affected (0.28 sec)
mysql> select concat('',elt(b,'Dona','Don'),ifnull(ut.a,''),' ') from Userp ut;
Empty set (0.03 sec)
mysql> show create table userp\G
*************************** 1. row ***************************
Table: userp
Create Table: CREATE TABLE `userp` (
`a` varchar(45) DEFAULT NULL,
`b` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)
mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)
Please, try to connect to your server with mysql command line client, execute statements presented above and send the output. I wonder what character sets are used in your case.
[21 Feb 2011 8:33]
Jose Luis Artigas
Soryr i couldn´t write before. This is my character set in MySQL version 5.1.
mysql> show variables like 'char%';
+--------------------------+-----------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+-----------------------------------------------
-----+
| character_set_client | latin1
|
| character_set_connection | latin1
|
| character_set_database | latin1
|
| character_set_filesystem | binary
|
| character_set_results | latin1
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\
ets\ |
+--------------------------+-----------------------------------------------
-----+
8 rows in set (0.00 sec).
Only gives the ERROR in WorkBench SQLEditor (The version that i am using is 5.2.31CE REV 7115) and also in the MySQL Administrator SQL Browser (Version 1.2.17) .
create table userp (a varchar(45), b INT(10));
select concat('',elt(b,'Doña','Don'),ifnull(ut.a,''),' ') from Userp ut;
In MySQL Comand Line WORKS FINE.
[22 Feb 2011 9:23]
Valeriy Kravchuk
Please, send the output of: show create database <your database name>; from the same Workbench session immediately after you get this error message.
[23 Feb 2011 8:10]
Jose Luis Artigas
This is the output
create table userp (a varchar(45), b INT(10));
Meesage: 0 row(s) affected
select concat('',elt(b,'Doña','Don'),ifnull(ut.a,''),' ') from Userp ut;
Message: Error Code: 1271
Illegal mix of collations for operation 'concat'
show create database mydb;
'mydb', 'CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */'
Regards from Jose Luis
[30 Mar 2011 22:36]
MySQL Verification Team
Looks for me that isn't Workbench issue, try the query changing ñ by n in the word Doña. Thanks.
[30 Apr 2011 23: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".
Description: I am using latin1 as collation, when i excute this sql statement the server gives an error. I have test in 5.5.9 and 5.1 also an the bug still there. create table userp (a varchar(45), b INT(10)); select concat('',elt(b,'Doña','Don'),ifnull(ut.a,''),' ') from Userp ut; gives Error Code: 1271 Illegal mix of collations for operation 'concat' instead all this statements works fine, select concat('',elt(b,'Doña','Don'),ifnull(1,''),' ') from Userp ut; select concat('',elt(b,'Dona','Don'),ifnull(ut.a,''),' ') from Userp ut; select concat('','Doña',ifnull(ut.a,''),' ') from Userp ut; But all this works fine in MySQL comand line. Also fail in Forwar Engeniering process of the workbench modelling How to repeat: create table userp (a varchar(45), b INT(10)); select concat('',elt(b,'Doña','Don'),ifnull(ut.a,''),' ') from Userp ut;