[Resolved] When I faced “#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci’” Error

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