Fix Unknown collation utf8mb4_unicode_ci [WP Migration]

Do you get an error Unknown collation utf8mb4_unicode_ci while migrating your WordPress database? No worries, this tutorial will explain how to fix both utf8mb4_unicode_ci collation & utf8mb4 character set errors.

Before we see the fix, let’s understand the reason for the error and few snapshots.

Reason for Unknown collation utf8mb4_unicode_ci & utf8mb4 character set errors

If you export WordPress database from MySQL server version 5.5.3+ and import into a MySQL server lower than version 5.5.3, then you are likely to see the below errors.

Unknown collation utf8mb4_unicode_ci

&

Unknown character set: 'utf8mb4'

Here’s the table structure which uses utf8mb4_unicode_ci collation and utf8mb4  character set.

CREATE TABLE `wp_nfd_termmeta` (
 `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `term_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `meta_value` longtext COLLATE utf8mb4_unicode_ci,
 PRIMARY KEY (`meta_id`),
 KEY `term_id` (`term_id`),
 KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Import the SQL dump  (exported from MySQL server version 5.5.3) into MySQL server version < 5.5.3. Below is the sample error:

mysql> source sample_wrdp1.sql;
ERROR 1115 (42000): Unknown character set 'utf8mb4'
ERROR 1273 : Unknown collation: utf8mb4_unicode_ci

So MySQL version mismatch is the issue? Well, you got it, that’s exactly I was trying to explain. Because, the exported SQL dump file contains references for COLLATION set to utf8mb4_unicode_ci and CHARSET set to utf8mb4.

So what are the COLLATION & CHARSET supported by MySQL versions lower than 5.5.3?

The MySQL versions < 5.5.3 support utf8_general_ci collation & utf8_unicode_ci collations and charsets ‘utf8’.

Fix Unknown collation utf8mb4_unicode_ci & utf8mb4 character set errors?

Method 1: Export SQL with compatibility for lower version of MySQL

Using PHPMyAdmin

Follow the below steps to export SQL file with the compatibility for lower versions of MySQL.

1. Click Export
2. Select “Custom – display all possible options” radio button under “Export Method“
3. Scroll down to find “Format-specific” options heading
4. Lookout for ‘Database system or older MySQL server to maximize output compatibility with:‘ drop down & select “MYSQL40” option.
5. Now click “Go” to export.

In command-line:

Use ‘–compatible‘ option for mysqldump command as shown below.

$ mysqldump -u <username> -p --compatible=mysql40 <database_name> > sample_dump.sql

Import it into a lower version of MySQL and it should work.

Method 2: Edit the exported SQL file and replace collation & charset

You can quickly open the exported SQL file in your favorite editor and replace utf8mb4_unicode_ci to utf8_general_ci & “utf8mb4” to “utf8”.

1. $vim sample_dump.sql
2. Search and replace as shown below:

:g/utf8mb4_unicode_ci/s//utf8_general_ci/g
&
:g/utf8mb4/s//utf8/g

Save the file and try importing.

mysql> source sample_dump.sql