Why I am suddenly getting “unknown collation ‘utf8mb4_unicode_520_ci’” error?
Well that’s the same question I had when I encountered this problem while migrating my website server. When I dig into the detail I found a solution for this problem. If you are facing the same problem then stay around and I will share 3 working ways to fix unknown collation ‘utf8mb4_unicode_520_ci‘ error that really works.
Why “unknown collation ‘utf8mb4_unicode_520_ci‘” error occurs?
This error generally occurs when you are trying to migrate database server from a newer version to an older version. MySQL servers higher than 5.6 versions support ‘utf8mb4_unicode_520_ci’ collation. If your destination server is having lower version than 5.6 then it does not support ‘utf8mb4_unicode_520_ci’ collation and you will get this error message.
How to fix “unknown collation ‘utf8mb4_unicode_520_ci’”?
Here is the list of 3 different working ways that you can use to fix the unknown collation ‘utf8mb4_unicode_520_ci’error message.
Try these methods one by one and see which one works for you.
1. Change MYSQL server version
As this issue is primarily because of MYSQL server version then upgrading your server to higher and latest version is the easiest and recommended way of getting rid of this error message.
Higher version by default supports ‘utf8mb4_unicode_520_ci’ and it will ensure higher data security and integrity for your website data. But if you don’t want to upgrade the MYSQL server version, there are other ways to resolve the error. You can still fix the issue by using other following methods.
Also Read: [FIX] MySQL ERROR 1046 (3D000) No Database Selected
2. Update database .sql file
Follow the below steps in order to update the .sql file for your database in the server.
- Locate the .sql file
- Select .sql file > Right Click > Edit with Notepad++
- Press Ctrl + H. Find all occurrences of utf8mb4_unicode_520_ci and replace it with utf8mb4_unicode_ci
- Save the .sql file and try to reimport the database again. You should able to import the database without any error message.
3. Using PHPMyAdmin
If you are using PHPMyAdmin to manage your database then you can easily get rid of this error message. Here are the steps to resolve unknown collation ‘utf8mb4_unicode_520_ci’ error message using PHPMyAdmin.
- Open PHPMyAdmin and click on Export tab
- In the Export Method section select Custom option
- Go to the section “Format-specific options” and in the “Database system or older MySQL server to maximize output compatibility with” option select MYQL40 from the dropdown list.
- Click on Go button.
This should fix the error while importing the database server.
Final thoughts:
We hope that with the above steps, you are able to resolve the “unknown collation ‘utf8mb4_unicode_520_ci’” error message and able to migrate the database successfully from source to the target environment.
Do share your experience of using above methods in the comment section and let other fellow readers know which method really worked for you.
Cheers!
Similar article:
1. How to FIX “ora-01033 oracle initialization or shutdown in progress” in 5 Easy Steps
