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
How Do You Make an AI Chatbot? Have you ever considered creating a chatbot? It might seem like a straightforward task, but, in reality, it involves more complexities ...
Solution Stories: Deep Dive To Uncover Hidden Data Issues Welcome to our latest Solution Stories blog series, where we delve into real-world client stories that offer valuable insights into ...

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


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