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, `long_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 other 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 import can not be done.

SQL error import error through phpMyAdmin or SSH - Unknown collation: 'utf8mb4_unicode_520_ci'

The simplest method is to replace the .SQL file, the "utf8mb4" with "utf8". To do this, you have phpMyAdmin at hand, from where to download the database file to your PC. Before you make any changes, make sure you have a backup of the database. Backup is always indicated before you start to change into a database.

Let's say that in our scenario we have the file "database_name_name.sql" where we have to replace "utf8mb4" with "utf8"The method urges many system administrators to make this change on linux using the"thirst" for "Find & Replace". So, upload the file to a linux server, then run it command lines in the directory where you have the .sql file where you want to make the changes.

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

After executing these command lines, the .sql file will replace "utf8mb4" 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.

Fix phpMyAdmin Import SQL - Unknown collation: 'utf8mb4_unicode_520_ci'

About the author

Stealth

Passionate about everything that means gadgets and IT, I write with pleasure stealthsettings.com from 2006 and I like to discover with you new things about computers and operating systems macOS, Linux, Windows, iOS and Android.

Leave a Comment