Illegal mix of collations

# Common log entries
WordPress database error Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation '='

MySQL and MariaDB return this error when one query compares or joins text columns that use different collations, for example utf8mb4_unicode_ci and utf8mb4_unicode_520_ci. In WordPress it usually follows a migration or a partial import, when some tables were created with a different default than the rest.

Common causes

References

1. Migrating WordPress (Advanced Administration Handbook)
2. Debugging in WordPress (Advanced Administration Handbook)

Rather not fix it yourself? Let WP119 handle it automatically.

Mismatched table collations are handled automatically: the system converts the incompatible collations and lists the table names in the report. Fixes are made on a copy of your site in the recovery station and written back only after re-verification passes. In your site’s database, the protected tables (members, comments, orders and form submissions) are copied as structure only. In SSH and FTP mode, the system creates a pre-recovery backup on your host before writing back and restores from it if verification after write-back fails. If the recovery cannot be completed, the recovery fee is refunded in full.

1. Initial assessment: Site URL entry
2. Copy to the recovery station: Work on a copy
3. Diagnosis: Item-by-item checks
4. Recovery and verification: Home and inner pages
5. Write-back or delivery: Only after verification passes