[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

31 Replies to “[Resolved] When I faced “#1273 – Unknown collation: ‘utf8mb4_0900_ai_ci'” Error

  1. Just have faced this problem a few minutes ago, tried your solution, everything run smootly again. Thankyou very much brother.

  2. Got to love that you provide items that clearly should be copied and pasted but then disable selection.

Leave a Reply

Your email address will not be published. Required fields are marked *