BAE Databases Do Not Support utf8mb4, So WordPress 4.2 Dumps Cannot Be Imported Directly

I received an email from BAE and wanted to test its performance, but when importing a database I hit the error #1273 - Unknown collation: 'utf8mb4_unicode_ci'. From the message, it was clear that the database did not support utf8mb4, so I opened a support ticket. BAE support replied with the following explanation.

Dear user, BAE databases do not support utf8mb4 encoding, so this error appears during import. We recommend exporting the SQL file without utf8mb4 encoding and using utf8 instead. Thank you for supporting our cloud platform.

WordPress 4.2 uses utf8mb4 so it can support emoji and more languages. Because BAE 3.0 does not support utf8mb4, the import error appears.

According to the official WordPress explanation of utf8mb4, only when the database supports it will some tables be upgraded to utf8mb4. That means WordPress remains backward-compatible with databases that still use utf8. So before importing, we only need to convert utf8mb4 to utf8. There are two ways to replace the database encoding.

1. Replace utf8mb4 with utf8_general_ci in a code editor

Before importing the database, open the SQL file in a code editor.

1. Find utf8mb4_unicode_ci and replace it with utf8_general_ci.
2. Find utf8mb4 and replace it with utf8.

Be careful about the replacement order. If you replace utf8mb4 first, then utf8mb4_unicode_ci can no longer be found and the replacement will fail.

2. Replace it with SQL statements

If you are comfortable with SQL, you can modify the database directly with SQL commands. The example below can be used as a reference.

mysql_select_db($dbname);
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
  foreach ($tables as $key => $value) {
     mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
}}