Today we will fix well known MySQL/MariaDB problem (in WordPress) when users are uploading the dumps into mysql. Migrating WordPress from one host to another require database exporting (into MySQL dump) and then extracting in the target hosting account.
During this procedure we often facing to the following error:
#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
occurs on an attempt to import a database dump (on the target host).
What is collation?
Collation is a way of encoding the information into a database. In our case the version incompatibility causes mainly this kind of error.
Collation is the assembly of written information into a standard order. Many systems of collation are based on numerical order or alphabetical order, or extensions and combinations thereof. Collation is a fundamental element of most office filing systems, library catalogs, and reference books.
Fixing the collation issue
The problem caused to an invalid character set and collation between 2 hosting systems: this current server and the target (new) server are running different versions of MySQL. To solve this we should convert collation manually:
1. Open the *.sql file in your code editor (it should support big files as dumps are pretty heavy sometimes)
2. Find and replace all ‘utf8mb4_unicode_520_ci’ with ‘utf8mb4_unicode_ci’ in the current dump sql-file.
3. Save and upload new dump file to a target MySQL database.