Fix phpMyAdmin Import SQL – Unknown collation: ‘utf8mb4_unicode_520_ci’

The Import Problem Via Via phpMyAdmin or from SSH (with order line for import) a databases or archived, which appears especially when moving a database from one server to another, which does not use the same settings for encoding or are different versions with specific settings in MySQL.


Error
SQL query:

CREATE TABLE `wp_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_520_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_520_ci,
PRIMARY KEY (`meta_id`),
KEY `term_id` (`term_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
MySQL said: Documentation

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

After this error, the import of a database WordPress or another CMS will stop suddenly.

The problem arises because of the different encoding, used by the two systems. In this case, “utf8mb4_unicode_520_ci” Not recognized, and the import cannot be done.

Solution Import SQL Error through Phpmyadmin or SSH – Unknown collation: ‘utf8mb4_unicode_520_ci’

The simplest method is to replace in .SQL file, string “UTF8MB4” cu “UTF8“. To do this, you have a handy PHPMYADMIN, where to download the database file on the PC. Before you start changes, make sure a backup of the database. Backup is always indicated before we start to change in a database.

Let's say that in our scenario we have the file “name_baza_de_date.sql” in which we need to replace“UTF8MB4” cu “UTF8“.. The inner method for many system administrators is to make this change on Linux, using the command “sed” for “find & replace“. So, climb the file on a Linux server, then execute command lines in the director in which you have the .SQL file in which you want to make the changes.

 sed -i 's/utf8mb4/utf8/g' nume_fisier.sql
 sed -i 's/utf8_unicode_ci/utf8_general_ci/g' nume_fisier.sql
 sed -i 's/utf8_unicode_520_ci/utf8_general_ci/g' nume_fisier.sql

After the execution of these command lines, in the .SQL file will be replaced “UTF8MB4” cu “UTF8”, “utf8_unicode_ci” cu “rainy” and “utf8_unicode_520_ci” cu “rainy”. Of course, you can make these changes on a Windows PC or Mac, using a TXT editor in which to open the .SQL file.

Import the modified file through PHPMYADMIN.