| Bug #4719 | Illegal mix of collation error can be fixed for some functions | ||
|---|---|---|---|
| Submitted: | 23 Jul 2004 11:51 | Modified: | 1 Dec 2005 8:40 |
| Reporter: | Alexander Barkov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | 4.1.x | OS: | Any (any) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[23 Jul 2004 13:44]
Alexander Barkov
Sorry, I meant this works fine: mysql> select 'embedded' = version(); mysql> select 'embedded' like version();
[31 Aug 2004 12:06]
Matthias Leich
I have the same problem with the more probable string function concat:
SELECT concat("---- The version to be tested is: ",@@version);
--> ..failed: 1267: Illegal mix of collations (latin1_swedish_ci,COERCIBLE)
and (utf8_general_ci,COERCIBLE) for operation 'concat'
[23 Aug 2006 6:48]
Ajinkya Patil
After an upgrade to MySQL 4.1, the statement fails with error:
mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
ERROR 1267 (HY000): Illegal mix of collations
(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
for operation 'substr_index'
The reason this occurs is that usernames are stored using UTF8 . As a result, the USER() function and the literal string '@' have different character sets (and thus different collations):
mysql> SELECT COLLATION(USER()), COLLATION('@');
+-------------------+-------------------+
| COLLATION(USER()) | COLLATION('@') |
+-------------------+-------------------+
| utf8_general_ci | latin1_swedish_ci |
+-------------------+-------------------+
One way to deal with this is to tell MySQL to interpret the literal string as utf8:
mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
+------------------------------------+
| SUBSTRING_INDEX(USER(),_utf8'@',1) |
+------------------------------------+
| root |
+------------------------------------+
or the other way around using _latin1 to convert it in to latin collation.
Another way is to change the connection character set and collation to utf8. You can do that with SET NAMES 'utf8' or by setting the character_set_connection and collation_connection system variables directly.
[23 Aug 2006 6:50]
Ajinkya Patil
The above solution i found on net.. http://www.datutorials.com/mysql/mysql_ref_manual_Charset.php This is not by me. Just posted to show all others...
Description: mysql> select locate('embedded',version()); ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'locate' However this works fine: mysql> select N'embedded' = version(); mysql> select N'embedded' like version(); The reason is that comparison and LIKE use internally character set conversion of a weak, according to coercibility rules, argument. In the above example, string constant is a weak (COERCIBLE) argument, function result is strong (IMPLICIT) argument. Other functions do not have this kind of internal character set conversion, wich leads to "Illegal mix of collations" error. How to repeat: Run the above. Suggested fix: We should convert weak arguments in functions like LOCATE(). ll string functions should be checked for a possibility of extending to use internal character set conversion.