Illegal mix of collations error in MySql

[MySQL]

In these (very rare) cases:

• two tables that really need different collation types

• values not coming from a table, but from an explicit enumeration, for instance:

  SELECT 1 AS numbers UNION ALL SELECT 2 UNION ALL SELECT 3

you can compare the values between the different tables by using CAST or CONVERT:

CAST('my text' AS CHAR CHARACTER SET utf8)

CONVERT('my text' USING utf8)

See CONVERT and CAST documentation on MySQL website.

Here's how to check which columns are the wrong collation:

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; 

And here's the query to fix it:

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

Link

Check the collation type of each table, and make sure that they have the same collation.

After that check also the collation type of each table field that you have use in operation.

I had encountered the same error, and that tricks works on me.