MySQL - Illegal mix of collations

I have no idea if this is a bug or no but I know it took me a half day to figure out!
My try to join 2 tables was going repeatedly to failure ending up with this message:

(utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation ”

Making long story short, actually it was because of the mismatching of the collations! After a few times of Googling (It was one of those unGoogled issues!) I could manage it like this:

In MySQL console:

mysql> show variables like ‘colla%’;

Gave me this result:

Just a SET command helped to set them all to same collation:
SET collation_connection=utf8_general_ci
(To make it persistent modify my.ini or mysql.conf)

But it was still giving me the very same error!
I checked php_myadmin and found out that the matching fields in JOIN statement have different collations, fare enough! I don’t know how on earth it became like that but it was the only reason and everything was fine after changing collations.

Who knows it’s a MySQL bug or phpMyAdmin’s. Or maybe it’s just me, again!

Sphere It March 20th, 2006

1 Comment Add your own

1. Maclord | March 28th, 2010 at 18:10

I’ve been having a similar problem when I installed Lester Chan’s wp-email 2.50 plugin on a Wordpress blog of mine in Turkish after adding the localized .po and .mo files.

The page involved with the plug-in yields the following error:
WordPress database error: [Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’]
SELECT email_timestamp FROM wp_goblog2_email WHERE email_ip = ‘78.172.145.85′ AND email_host = ‘78.172.145.85′ AND email_status = ‘Ba?ar?l?’ ORDER BY email_timestamp DESC LIMIT 1

on this page:
http://go-blog.ozar.net/mevzu/arac-klimalari-ve-havalandirma/email/

Trying to figure out how to fix that.