Link to home
Start Free TrialLog in
Avatar of d60eba
d60eba

asked on

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

Hi,

I have upgraded to a new server, running MySQL 4.1.7 and using phpMyAdmin 2.6.1-pl3.

On running a simple query:

SELECT *
FROM pi_sms_message_queue
WHERE message_sent = 'n'
LIMIT 0 , 30

I receive the following error in phpMyAdmin:

#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

The collation of that message_sent column is latin1_swedish_ci. Running this query:

SHOW VARIABLES LIKE 'coll%'

Produces:

Variable_name  Value  
collation_connection utf8_general_ci
collation_database latin1_swedish_ci
collation_server utf8_general_ci

And /etc/my.cnf is as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-----------

So...any ideas how I can fix this problem? I have googled but with no luck.

Cheers,

Leon
Avatar of mherchl
mherchl
Flag of Slovakia image

Try to change the database collation to utf8.
Avatar of d60eba
d60eba

ASKER

Any ideas how I could?

I thought having default-character-set=utf8 in the my.cnf would achieve that, but it doesn't seem to work.

ASKER CERTIFIED SOLUTION
Avatar of k141
k141

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of d60eba

ASKER

Hi,

That did work, and is useful to know, although I prefered to not have to put "_latin" many times in each query!

In the end I did this:

Put: "default-character-set=latin1"  in my.conf

In phpMyAdmin, click on the little Home icon in the left bar, and then in the right bar changed  MySQL connection collation to latin_swedish_ci.

All now works!

Cheers,

Leon
well i gave this solution because it seemed that you DID want to have a default character set to utf8 :)
yeah those things are annoying.. and i think they're even going to change the default from latin_swedish_ci to latin_general_ci, if not done yet.