Skip to main content

We're currently improving our systems to provide you with a new, more unified login experience.

"Illegal mix of collations" errors in mysql.

Cody Selzer
  • Updated

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

 

Related articles