Dataflows in many forms. Someday I was about to transfer MySQL data from one of my servers to my local PC having PHPMyAdmin. On my server PC, I was having WorkBench. A WorkBench is a free tool to manage MySQL queries acts like a relational database management tool.
I took a dump of a database with stored procedures using Export data wizard in WorkBench. So I was ready to import all data from the SQL file. I use XAMPP to set up a localhost server and PHPMyAdmin.
The Problem
SQL file was having a bit large size of 130MB so I followed a few steps first to increased upload limit in PHPMyAdmin tool which is usually set to max 20MB’s. After that, I started the process of Import .sql file. It was going normal, then suddenly it stopped with a red message “#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci‘”
Like a normal engeeneer, I tried to figure out some info about this strange error. I found many solutions and similar issue like these people were already facing. For few these were resolved and others were still getting it.
Some of the other issues I found:
“#1273 – Unknown collation: ‘utf8mb4_unicode_ci’ “
ERROR 1273 (HY000): Unknown collation: ‘utf8mb4_0900_ai_ci’
#1273 – Unknown collation: ‘utf8mb4_general_ci’
#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
What were these?
According to Wikipedia
“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.”
It is basically a technique of encoding the information into a database. This is mainly caused due to the version difference and supports the inability of the database engine to render data encoding type.
Finally Solution
This is how I resolved it. I didn’t run any encoding queries in the database or on SQL data in the sql file.
I just opened the dump.sql file in Notepad++ and hit CTRL+H to find and replace the string “utf8mb4_0900_ai_ci” and replaced it with “utf8mb4_general_ci“.
Bingo after that it got imported successfully!
Hope this will help somebody 🙂 so I just shared!
[Resolved] When I faced “#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci'” Error