WordPress Migration – Fixing “Unknown collation: ‘utf8mb4_unicode_ci'”
Recently a few popular plugins (specifically Gravity Forms) have begun to take advantage of a more sophisticated encoding type for varchar columns in the database, utf8mb4. In short, utf8 values are stored as 3 bytes, where utf8mb4 are stored as 4 bytes, thus allowing for compliance with more Unicode characters. For more information on utf8mb4, you can check out the MySQL docs.
This new encoding type was introduced in MySQL version 5.5.3. This can cause some issues if you’re attempting to migrate from 5.5.3+ to an older database. Thankfully, the solution is quite simple. We handle most of our database operations using phpMyAdmin, but similar settings should be available in other database managers. If you export normally, you’ll most likely get the error message of Unknown collation: ‘utf8mb4_unicode_ci’ when importing into an older MySQL database. To export to older MySQL databases, follow these few steps:
- Export your database/tables normally, but select Custom when choosing your Export Method.
- Select MYSQL40 from the dropdown for the Database system or older MySQL server to maximize output compatibility with: option.
That’s it, one simple setting change and you shouldn’t encounter any errors.