MySQL Bugs: #2255: Illegal mix of collations error ...

Bug #2255	Illegal mix of collations error ...
Submitted:	2 Jan 2004 4:33	Modified:	1 Apr 2004 10:41
Reporter:	Lenar Lõhmus	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1-alpha	OS:	Linux (Debian/Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Getting this error

ERROR 1266 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'if'

when executing this query:

select IF(LENGTH(val) > 0, val, '/') from x;

How to repeat:
For this test I created table:

create table x (val varchar(20) not null) default charset=utf8;

now executing query: select IF(LENGTH(val) > 0, val, 'empty') from x;

results in mentioned error. changing 'empty' to _utf8'empty' makes things work.
But I think mysql should automatically do whatever conversion is needed in this case.

Even more .. i do not understand from where that latin1_swedish_ci collation for 'empty' literal comes. Tried to SET CHARACTER SET utf8; before executing query - no difference.

OT: one question. Is there a way to convert whole database to utf8 without mysql
really converting data (data is already utf8 encoded). Anything other than dump and replacing occurences of latin1 with utf8?

You would use SET NAMES utf8; rather than SET CHARACTER SET utf8.  Your client is using latin1, so without specifying a character set for the string 'empty', it is using that of your client.

Well, SET CHARACTER SET should take care of the same thing SET NAMES does, so let's verify this, instead.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This is expected behaviour. You should use "SET NAMES utf8",
as Dean suggested. latin1 comes from "character_set_connection"
and "collation_connection" variables.

There is a way to convert whole a table, but bug system is not a
right place to provide a detailed explaination.Please write a letter to mysql@lists.mysql.com.
Shortly, the idea is to do it in two stage using a binary 
data type (binary, varbinary or blob) as an intermediate
storage to avoid character set conversion:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

As I said I tried with SET CHARACTER SET utf8; 
 
Now, I re-read docs and this is what I read: 
 
> SET CHARACTER SET indicates what is in the SQL statement that the client 
> sends, and also what is in the result set that the server sends back to the  
> client. Thus, SET CHARACTER SET includes SET NAMES, and also specifies what  
> character set the column values will have if, for example, you use a SELECT  
> statement. 
 
So if I issue SET CHARCATER SET utf8; cmd to mysql I should get expected 
result. 
 
So either there is a bug or this thing is misdocumented right now.

You're right, this is a documentation problem.
We'll check the manual and fix this asap.
I'm reopening this ticket not to forget.

Please try SET NAMES. It should work for your task.

Paul, I want to remind you, please take care about this bug.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

SET CHARACTER SET sets the character set and collation to be
those of the default character set.  SET NAMES sets them to the
character set you specify, which is why you were seeing the problem
even after a SET CHARACTER SET. The difference is now explained
here more accurately:

http://www.mysql.com/doc/en/Charset-connection.html

See also this page for an example of dealing with illegal
mixed collations:

http://www.mysql.com/doc/en/Charset-collation-charset.html