Illegal mix of collations

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
rv0987
Forum Newbie
Posts: 1
Joined: Fri Sep 19, 2008 5:13 am

Illegal mix of collations

Post by rv0987 »

Hi,

I hope someone can help me with the problem below:

Php 5.2.4 & Mysql 4.1.15 (both server & client)

My whole php code is encoded in Utf-8, but, for historic reasons, my db is in latin1.

According to the 4.1 manual this should not be a problem as long as you set the connection-char-set to utf8. http://dev.mysql.com/doc/refman/4.1/en/ ... ction.html: "It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8).". Which is what I did:

Code: Select all

#NOTE: I've tried BOTH options below, no difference 
mysql_set_charset('utf8')or akdie("mysql_set_charset failed:" . mysql_error()); 
mysql_query("SET NAMES utf8") or akdie("set names utf8 failed:" . mysql_error()); 
 
SHOW VARIABLES LIKE 'collation%'; 
collation_connection: utf8_general_ci 
collation_database: latin1_swedish_ci 
collation_server: latin1_swedish_ci 
 
I build my query with sprintf & mysql_real_escape_string like this:

Code: Select all

 
$Query = 
   sprintf( 
     "select * 
      from mytable 
      where myvarcharcolumn_latin1enc = '%s' 
     " 
   , mysql_real_escape_string('my utf8 string') 
   ); 
#NOTE: myvarcharcolumn_latin1enc is a varchar column in latin1 encoded
BUT if I execute this I get this error:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
What am I doing wrong here?

Funnily the same statements in these situations works just fine:
- same db & php when run from phpMyAdmin
- at my local pc (php5.2.3 & mysql 5.0.45)

This last remarks make me think this is a bug in Mysql 4.1.15. Could this be?

Hope anybody can help me.

Cheers, Ronald
Post Reply