MySQL – Fixing an Illegal mix of Collation

MySQL – Fixing an Illegal mix of Collation

//

Mixing Collations

While creating an ETL process for a Client, I ran into the following MySQL error:

"Illegal mix of collations (latin1_swedish_ci,IMPLICIT) 
and (latin1_general_ci,IMPLICIT) for operation '='"

A quick google search resulted a lot of bug reports on the MySQL website, but not any concrete instructions on how to identify and fix the problem.  Obviously some tables in my query had different collations, but looking at table properties didn’t result in any useful information.  This is because individual columns can have different collations in MySQL.

The Fix

In order to pinpoint the problem column(s) and table(s) run the following query (replacing the appropriate collation for your error):

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE collation_name = 'latin1_general_ci'
ORDER BY table_schema, table_name,ordinal_position;

You can change the individual collations on each column, but I prefer tackling it on a per table basis (since I had multiple tables to change):

ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE 'latin1_swedish_ci';

Related

Need Expert Help?

See Our Full Menu of Data Services

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK

×

Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Germany
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072

×

Love our blog? You should see our emails. Sign up for our newsletter!