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 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' 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, the .sql file will replace "utf8mb4" with "utf8", "utf8_unicode_ci" with "utf8_general_ci" and "utf8_unicode_520_ci" with "utgen. Of course, you can make these changes on one 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 gadget and IT, I am pleased to write on stealthsettings.com from 2006 and I love to discover new things about computers and macOS, Linux operating systems, Windows, iOS and Android.

2 Comments

Leave a Reply to KaiC X