Illegal mix of collations MySQL Error

mysql sql mysql-error-1267
Click Upvote picture Click Upvote · Jun 17, 2009 · Viewed 169k times · Source

I'm getting this strange error while processing a large number of data...

Error Number: 1267

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

SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€'

What can I do to resolve this? Can I escape the string somehow so this error wouldn't occur, or do I need to change my table encoding somehow, and if so, what should I change it to?

Answer

Ben Hughes picture Ben Hughes · Jun 17, 2009 
SET collation_connection = 'utf8_general_ci';

then for your databases

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL sneaks swedish in there sometimes for no sensible reason.

Related questions

mysql: error code [1267]; Illegal mix of collations (latin1_general_cs,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

I want to make the password column of my User table to be case sensitive in mysql. Following is the description of the table: /*Table: mst_user*/ FIELD TYPE COLLATION ------------- ------------ ----------------- user_id VARCHAR(100) latin1_swedish_ci first_…

Read More
MYSQL case sensitive search for utf8_bin field

I created a table and set the collation to utf8 in order to be able to add a unique index to a field. Now I need to do case insensitive searches, but when I performed some queries with the collate …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More