Fixing MySQL Error : Unknown collation: ‘utf8mb4_unicode_ci’

I was migrating a wordpress site to a new server, and had some issues using with the migration plugins, so went back to manual process and uploaded all files via FTP and exported the mysql database. When I tried to import the sql dump to the new database server I got the error “Unknown collation: ‘utf8mb4_unicode_ci'”. After some troubleshooting and googling I found out that the root cause was the mysql version. The utf8mb4 only supports database version 5.x.

Luckily there is an option on the export page to make things easy. To fix this issue you need to select the MYSQL40 value in the ‘Database system or older MySQL server to maximize output compatibility with option. (see below screenshot)

 

mysql_compatibility

 

See below sql dumps before and after selecting the above option:

 

Before:

 

DROP TABLE IF EXISTS `wp_commentmeta`;
CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_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 `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=1 ;

 

After:

 

DROP TABLE IF EXISTS `wp_commentmeta`;
CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`(191))
) TYPE=MyISAM AUTO_INCREMENT=1 ;