Unknown Collation Error – WordPress DB Migration

We were recently tasked with temporarily migrating a client’s website from an external host over to ours while we build their new bespoke WordPress site.

Initially, when attempting to import their database tables in phpMyAdmin, the pesky Unknown collation: ‘utf8mb4_unicode_520_ci’ error made an appearance. This is usually resolved by selecting a compatibility mode when exporting the .sql file from the live site, however we couldn’t do that this time, as the file was provided by an external source.

After some trial and error, we managed to deploy the site simply by doing a few find and replace actions to remove all references to ‘mb4’ and ‘520’ within the character set references. For example:

Unknown collation: ‘utf8mb4_unicode_520_ci’

Is replaced with:

Unknown collation: ‘utf8_unicode_ci’

And all other partial references are also modified, like where ‘utf8mb4’ just appears other strings.

Disclaimer: We have yet to extensively test all functionality front and back to ensure nothing is broken, however this seems to fit the bill for temporarily keeping an old site live… We’d be interested to hear other people’s approaches to this. 🙂

Jason Nye