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