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 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 handy method for many system administrators is to make this change on linux, using the command"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, 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.

Fix phpMyAdmin Import SQL - Unknown collation: 'utf8mb4_unicode_520_ci'

About the author

Stealth

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

2 Comments

Leave a Comment