Logs say WordPress database error Illegal mix of collations Print

Problem Description

The web server log indicates something like:

WordPress database error Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE)

Problem Resolution

This means that your WordPress tables in the database have differing encoding, when it's typically best that they are all the same. There are three levels of encoding:

• The database default that's used for creating new tables
• Each table has its own specifically configured collation
• Each column of a table can have its own collation

To set all of these collations to be the same, modern encoding:

1. Login Plesk
2. Make a backup of the database (important, in case of problems after changing collation)
3. Navigate to phpMyAdmin for your WordPress database - make sure it's the correct one! If it's not labelled in Plesk, head to wp-config.php and look it up
4. Choose "Operations" tab
5. Scroll to bottom where you see "Collation"
6. Select "utf8mb4_unicode_ci;" from the dropdown - this will change the database default
7. Checking the two remaining boxes to update each table and individual column as well
8. Save changes

This may take some time. Do not interrupt it.

Check website when done - if any issues, roll back immediately to your dumpfile from step (2)

You can also do this manually, but it's more work:

Change collation default for DB:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

Change live collation for existing table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;