Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
Posted by: brusselsshrek
Date: October 10, 2005 09:58AM

I'm going utf8 crazy trying to sort out the following problem, and would appreciate any help!

I have been trying to convert into using utf8, which for a lot of cases seems to work OK, but not all!

I get the error message "Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)" when I do something like "SELECT * from term_case WHERE tmc_case_text = "Íslenska"". I don't always get it, so I think it is when the constant string is not a real latin1 string.

An example of where I get the error is for the text "Íslenska" as seen on:
http://dmoz.org/World/%C3%8Dslenska/

I have a table, term_case, which is designed to contain a particular search term item in a case insensitive manner:

CREATE TABLE `term_case` (
`fk_term_key` mediumint(8) unsigned NOT NULL default '0',
`tmc_case` tinyint(3) unsigned NOT NULL default '1',
`tmc_case_text` varchar(255) character set utf8 collate utf8_bin NOT NULL default '',
PRIMARY KEY (`fk_term_key`,`tmc_case`),
UNIQUE KEY `tmc_case_text` (`tmc_case_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

PHP Trace output:
SQL="SELECT * from term_case WHERE tmc_case_text = "Íslenska""
SQL error: "Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='" in file c:\program files\apache group\www\Terms.class.php, line 138
SQL="SELECT * from term WHERE term_match_text = "Íslenska""
SQL error: "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='" in file c:\program files\apache group\www\Terms.class.php, line 222


I don't really know what I'm doing wrong or how to fix it. I'm really getting out of my depth in all this utf8 stuff, though I've tried hard to understand it. I have tried:
* prefixing the literal with "_utf8"
* doing a CONVERT("$termText" USING utf8)
* using the PHP function utf8_encode($termText);

Each doesn't seem to work as I want.

Help!!!! Thanks :-)

Options: ReplyQuote


Subject
Views
Written By
Posted
Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)
4827
October 10, 2005 09:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.