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';

More About the Author

Ben Bausili

Global Director of Product
Webinar Replay: Redesign the Analytics Experience with Curator A video replay for this webinar is available at the bottom of the blog. A lot of analytics tools are fantastic at visualizing data, but ...
The Biggest Takeaways from Tableau Conference 2022 Christmas, I mean Tableau Conference, came early this year!  That’s right, the 2022 Tableau Conference has already come and gone, ...

See more from this author →

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
Geschäftsführer: Mel Stephenson

Telefon: +49 (0)211 5408 5301

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