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