Important Note: Converting from utf8mb4 to utf8 may cause loss of data.
It is advisable to take a full database backup, before carrying out the steps below, and inspect your site in full after the changes, to ensure there is no corruption.
Since WordPress 4.2, there have been some core changes, in particular one that has caused some confusion is upgrading database tables from utf8 to utf8mb4. WordPress will only upgrade your website when the following conditions are met:
You are currently using the utf8 character set.
Your MySQL server is version 5.5.3 or higher.
Your MySQL client libraries are version 5.5.3 or higher.
See https://make.wordpress.org for more information on this WordPress update.
You may experience an error when importing a WordPress .sql database from your localhost environment or from another hosting provider to Blacknight.
The Error
#1273 – Unknown collation: ‘utf8mb4_unicode_ci’
The Cause
The most likely cause is that you are importing a .sql database from a MySQL server version 5.5.3 which is using the ‘utf8mb4’ for encoding. The difference between utf8 and utf8mb4 is that the former stores 3 byte characters, while the latter can store 4 byte characters. In Unicode terms, utf8 can only store characters in the Basic Multilingual Plane, while utf8mb4 can store any Unicode character.
Workaround
In short, to resolve the issue, utf8mb4 encoded data needs to be converted to utf8. There are many methods available online to assist with this task, some of which we have outlined below and found in most cases works quite well.
Method 1
Use the WordPress plugin WP Migrate DB available from the WordPress directory.
Method 2
Export a ‘Custom’ .sql database file manually via phpMyAdmin on your old host, then import the .sql to your new database with Blacknight.
Open phpMyAdmin & select your database
Click the “Export” tab for the database
Click the the “Custom” radio button
In the section titled “Format-specific options:”, change the option for “Database system or older MySQL server to maximize output compatibility with” from ‘NONE’ to ‘MYSQL40’.
Scroll to the bottom and click “GO”.
Now you should be able to import your .sql into phpMyAdmin in your Blacknight control panel.
ote: Further action may be required
You may need to run an SQL Query to ALTER a table’s character set and collation.
You may need to update the WordPress wp-config.php file collate type to ‘utf8’.