Fix phpMyAdmin Import SQL - Unknown collation: 'utf8mb4_unicode_520_ci'

An issue of import via phpMyAdmin or SSH (with command line for import) a databases or archived, which occurs especially when moving a database from one server to another, which does not use the same encoding settings or 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, Importing a database WordPress or another CMS, it will stop suddenly.

The problem arises due to the different encoding used by the two systems. In this case, "utf8mb4_unicode_520_ci"Not recognized, and the import cannot be done.

Troubleshooting SQL import via phpMyAdmin or SSH

The easiest way is to replace the strings in the .SQL file. "utf8mb4" with "utf8". To do this, you have at hand phpMyAdmin, where to download the database file to your PC. Before making any changes, make sure you back up your database. Backup is always recommended before you start modifying a database.

Let's say that in our scenario we have the file "database_name.sql" in which we have to replace "utf8mb4" with "utf8“.. The method is at hand for many administratorand the system is to make this change on linux, using the command "thirst" for "find & replace“. So, upload the file to a server linux, then run command lines in the directory where you have the .sql file where 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 executing these command lines, in the .sql file “utf8mb4” will be replaced with “utf8”, “utf8_unicode_ci” with “utf8_general_ci” and “utf8_unicode_520_ci” with “utf8_general_ci”. Of course, you can make these changes on a Windows PC or Mac, using a TXT editor to open the .SQL file.

Import the modified file through phpMyAdmin.