MySQL – Fixing an Illegal mix of Collation

Dev

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 Experience Practice Director
The Biggest Takeaways from Tableau Conference 2022 Christmas Tableau Conference came early this year! That’s right, the 2022 Tableau Conference has already come and gone, but ...
Better, Faster, Stronger: The Power of the Cloud in BI Two weeks ago, my fellow content connoisseur Andrea Avey sat down with InterWorks IT gurus Scott Matlock and Tim Rhymer to talk about ...

See more from this author →

Subscribe to our newsletter

  • I understand that InterWorks will use the data provided for the purpose of communication and the administration my request. InterWorks will never disclose or sell any personal data except where required to do so by law. Finally, I understand that future communications related topics and events may be sent from InterWorks, but I can opt-out at any time.
  • This field is for validation purposes and should be left unchanged.

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