"Illegal mix of collations" errors in mysql.

Symptoms

Errors similar to the following are encountered when importing, exporting, modifying, or reading a database.

Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation

SQL Error [1271] [HY000]: Illegal mix of collations for operation

Description 

This issue occurs when two strings with different collations and the same coercibility are compared.

Workaround

To correct this, you will have to do one of the following.

• Change the collation of one string so that both strings use the same collation.
• Force one of the strings not to be coercible.
• Change the coercibility of one string so that the strings have different coercibility.
• Change the encoding of the strings so that one is Unicode and the other is not.

Incorrectly modifying the collation, coercibility, or encoding can corrupt your database or cause data loss. As such, modifying the collation, coercibility, or encoding should only be performed by a qualified database administrator.

Additional resources

Character Sets, Collations, Unicode