Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)

Question

Here is my query:

INSERT INTO location_province(name, country)   
SELECT child.name
      ,location_country.id
  FROM location_1 child
 INNER JOIN location_1 parent
    ON child.parent_id = parent.id
 INNER JOIN location_country
    ON location_country.name = parent.name
 WHERE child.location_type = 1

It throws this error:

#1267 - Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

What's wrong and how can I fix it?

---

Note: Adding COLLATE utf8_unicode_ci in the end of query doesn't work either.

Answer 1

Yeah, that's because of the JOIN ON clauses and per error, collation of those columns involved in ON condition doesn't matches. Collation of those column must match. I mean the below lines

ON child.parent_id = parent.id  ------ 1
 INNER JOIN location_country
    ON location_country.name = parent.name ------ 2

Check the tables on which you are joining and verify the same

Well, change the collation while joining like

 INNER JOIN location_country
    ON location_country.name collate utf8_general_ci = parent.name collate utf8_general_ci