(Resolved) Unknown collation: utf8mb4_unicode_520_ci

A collation is a set of rules that defines how to compare and sort character strings in a database server. In this tutorial, we are discussing an error faced during database restoration on another server.

Let’s see the problem and solution to the issue faced recently:

The Problem:

During the migration of a WordPress application, I faced the following error with the restoration of the MySQL database. The collation id may differ based on the MySQL version.

Error message:

Error 1273 (HY000) at line 36 Unknown collation: 'utf8mb4_unicode_520_ci'

Here you go with a solution.

The Solution:

After searching for the error, I found that the MySQL server running on the other server is an older version than the source. So we find out that the destination server doesn’t support the ‘utf8mb4_unicode_520_ci’ collation.

To resolve this issue, I did a little change in the backup file. Edit the database backup file in text editor and replace all occurrences of 「utf8mb4_unicode_520_ci」 with 「utf8mb4_general_ci「. Also, if you found 「CHARSET=utf8mb4「, replace this with 「CHARSET=utf8「.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save the backup file and restore the database.

The Linux system users can use the sed command to replace text in files directly.

sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/g' backup.sql  
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql  

That’s it, Now the database is successfully restored without any errors!

Hopefully, this is solution helped you to resolve 「Unknown collation: ‘utf8mb4_unicode_520_ci’」 issue with MySQL databases.