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.sqlsed -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.