If you export database from phpMyAdmin in .sql or zipped format. And you want to import your exported SQL file into a new server. But while you are trying to import database you get the error on new server like #1273 – Unknown Collation : ‘utf8mb4_unicode_520_ci’. The collation code can be different but the reason for all is same.
So why exactly you are getting error Unknown collation during import of your database? That’s simple its because your old server’s MySQL version is different than your new server’s MySQL version. So the solution is just simple. Either replace collation with supported collations in your new server. Otherwise just remove it so MySQL can assign default collation to tables its going to create during import.
How to fix #1273 – Unknown collation error?
Let’s see how we can easily fix the #1273 – Unknown collation errors. Actually there are two solutions first is to change collation to supported collation of your server. But i will tell you simple solution just remove the collation from your .sql file so MySQL can create default collation tables during this import.
- Open your .sql file in a good editor like Microsoft Visual Code or Notepad++
- Search for COLLATE=utf8mb4_0900_ai_ci (Make sure to change it with your collation code)
- Remove COLLATE=utf8mb4_0900_ai_ci but don’t remove ending semicolon ;
- Now upload .sql and you should be okay as shown in video above.
Best practice for Unknown Collation
If your language or code or names have special characters then certain collations can give you the problem of unwanted characters on your website. The best practice is find a good collation and use that so in that case instead of just removing COLLATE: you can replace it with something useful like
Replace the below string:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
with:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
This way you are still guiding MySQL to use the collation you think will be suitable for data available in your website or .sql file.
If you need a developer to migrate your website or database contact us.
