#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Question

I have a WordPress website on my local WAMP server. But when I upload its database to live server, I get error

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

`sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' file.sql` — Nabil Kadimi, Dec 12 '17 at 13:54
In a sea of find-and-replace solutions below, don't forget to checkout @SherylHohman's answer first - just upgrade to mariadb / mysql 5.6 so your server supports this collation. — squarecandy, May 06 '18 at 20:24
What version of WordPress? See https://make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade/ — Rick James, Nov 20 '18 at 18:40
There's also a quick guide here (https://wpza.net/unknown-collation-utf8mb4_unicode_520_ci/) which explains all the 3-steps you should take to ensure you've done everything. — WPZA, Feb 25 '19 at 22:09
sed -i dump-file.sql -e 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' — Rodrigo, Oct 23 '20 at 13:41
[@thomasrutter](https://dba.stackexchange.com/users/1820/thomasrutter) has an amazing explanation of *what* and *why* this is [here: "mysql to mariadb: unknown collation utf8mb4_0900_ai_ci"](https://dba.stackexchange.com/a/314442/262276), and there are some great methods to alter collation the "right way" [here: "How to convert an entire MySQL database characterset and collation to UTF-8?"](https://stackoverflow.com/q/6115612/705296) — Joe Sadoski, Oct 14 '22 at 02:40
utf8mb4_unicode_520_ci (introduced in 2013) is very close to utf8mb4_0900_ai_ci (2018). This shows no diffs for typical accented letters: http://mysql.rjweb.org/utf8mb4_collations.html — Rick James, Oct 27 '22 at 17:12

score 464 · Answer 1 · edited Dec 24 '22 at 09:20

464

I believe this error is caused because the local server and live server are running different versions of MySQL. To solve this:

Open the sql file in your text editor
Find and replace all utf8mb4_unicode_520_ci with utf8mb4_unicode_ci
Save and upload to a fresh mySql db

enter image description here

edited Dec 24 '22 at 09:20

starball

20,030
7
43
238

answered May 22 '17 at 21:15

Sabba Keynejad

7,895
2
26
22

12

**HELL NO!!!** It has been widely documented that `utf8mb4_unicode_520_ci` has *much* better UTF-8 support than `utf8mb4_unicode_ci`! Sauce: http://mysql.rjweb.org/doc.php/charcoll#best_practice with visual clarification proving 520_ci is superior here: http://mysql.rjweb.org/utf8_collations.html – John Feb 05 '18 at 05:31
11

@John, The articles you linked don't really defend that comment. Their "Thus" can't even be properly stated from their premise. Overall the whole article is poorly written, although there does appear to be some good information there. The 520 collation uses a newer version of the Unicode Collation Algorithm (UCA - version 5.2.0) with different weights, but to say it "has much better UTF-8 support" seems more than a stretch. – Gremio Apr 18 '18 at 14:40
2

Let's suppose that a Standards body (Unicode) will usually make each newer version (5.20) "better" than an older version (4.0). – Rick James Jul 20 '18 at 19:54
This is the best answer. – NightOwl Oct 05 '18 at 07:06
I suggest this solution because it replaces all "utf8mb4_unicode_520_ci" occurrences. Whereas savani sandip answer leaves some. – Rakesh May 23 '19 at 08:52
faced similar issue for `utf8mb4_0900_ai_ci` , your solution worked perfectly – Atiq Baqi Jun 20 '22 at 05:44

score 390 · Accepted Answer · edited Sep 13 '17 at 18:48

390

You can solve this by finding

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

in your .sql file, and swapping it with

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

edited Sep 13 '17 at 18:48

dǝɥɔS ʇoıןןƎ

1,674
5
19
42

answered Feb 22 '17 at 07:26

savani sandip

4,190
1
10
8

40

I recommend the answer by Sabba (https://stackoverflow.com/a/44122258/168309) as it retains the utf8mb4 – Amir Jun 14 '17 at 07:22
6

The solution below of @Sabba Keynejad is better in my opinion, since it keeps the mb4, you should try that first. – Maor Barazany Aug 02 '17 at 16:23
Note, I had to replace this in multiple places in the SQL File. Also there were places where I had to do https://stackoverflow.com/a/44122258/1436129 below too. – aubreypwd Mar 12 '18 at 15:51
Actually found this answered worked when the linked answer by Sabba did not. Presumably because of my MySql installation defaults. +1 – iCollect.it Ltd Apr 26 '18 at 09:18
2

it is not working, now it is giving error: `COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' `, i have replaced `utf8mb4_unicode_520_ci` with `utf8mb4_unicode_ci` and it's working, as per Sabba's answer. – Haritsinh Gohil Dec 13 '18 at 05:44
This is NOT a very correct answer, utf8 and utf8mb4 is not the same thing in mysql – ospider Jan 18 '19 at 02:53
I resolved it by replacing 'utf8mb4_unicode_520_ci' with 'utf8mb4_general_ci' – Moh .S Oct 14 '20 at 13:40
I changed `utf8mb4_0900_ai_ci` --> `utf8mb4_general_ci`. mysql dist 5.7.33 on ubuntu 16 :) and it worked. – agent18 Mar 06 '21 at 23:12
This answer worked for me. – Eskay Amadeus Apr 15 '22 at 19:57

SherylHohman · Answer 3 · 2017-07-03T03:54:55.260

65

In my case it turns out my
new server was running MySQL 5.5,
old server was running MySQL 5.6.
So I got this error when trying to import the .sql file I'd exported from my old server.

MySQL 5.5 does not support utf8mb4_unicode_520_ci, but
MySQL 5.6 does.

Updating to MySQL 5.6 on the new server solved collation the error !

If you want to retain MySQL 5.5, you can:
- make a copy of your exported .sql file
- replace instances of utf8mb4unicode520_ci and utf8mb4_unicode_520_ci
...with utf8mb4_unicode_ci
- import your updated .sql file.

edited Jul 03 '17 at 03:54

answered Jul 03 '17 at 03:21

SherylHohman

16,580
17
88
94

3

This is absolutely the best way to do it if you have full control over your server. Upgrade the new server so it matches the old server. If you don't have the access to upgrade your new server location, the find-replace method will probably be ok, but in any case where you can just do this upgrade instead, you should. – squarecandy May 06 '18 at 20:22
1

5.6 is when _520_ was added. – Rick James Jul 20 '18 at 19:55
This should be the correct answer. Most of the other solutions are more like hacks. Thanks @SherylHohman – Ken Sawyerr Sep 01 '19 at 19:43

score 45 · Answer 4 · answered Mar 04 '18 at 20:22

45

Open the sql file in your text editor;

1. Search: utf8mb4_unicode_ci Replace: utf8_general_ci (Replace All)

2. Search: utf8mb4_unicode_520_ci Replace: utf8_general_ci (Replace All)

3. Search: utf8mb4 Replace: utf8 (Replace All)

Save and upload!

answered Mar 04 '18 at 20:22

VUUB

487
4
5

2

I'm really glad I found your solution to this, I've been getting this #1273 error for days and I just couldn't find a solution to this – frontendstu Dec 16 '19 at 23:34

score 38 · Answer 5 · answered Sep 21 '21 at 13:23

38

I experienced a challenge importing data into mysql exported using mysql workbench. It is a collation issue. I solved this error by:

Opening the .sql file using text editor
Replacing "utf8mb4_0900_ai_ci" with "utf8mb4_general_ci".
Saving the file as .sql and importing it

It worked

answered Sep 21 '21 at 13:23

P.Githinji

1,459
11
5

perfect solution – yts61 Nov 22 '21 at 22:58
worked also for me, thanks! – borko Mar 30 '23 at 08:44

score 32 · Answer 6 · edited Jun 29 '18 at 05:08

32

easy replace

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql

edited Jun 29 '18 at 05:08

4b0

21,981
30
95
142

answered Jun 29 '18 at 05:05

Ryabinin Sergey

494
4
6

6

On macOS: ```sed -i '' 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql``` – Osvaldas Feb 13 '19 at 09:12

score 22 · Answer 7 · edited Feb 17 '18 at 19:55

22

just remove "520_"
utf8mb4_unicode_520_ci → utf8mb4_unicode_ci

edited Feb 17 '18 at 19:55

Stphane

3,368
5
32
47

answered Feb 17 '18 at 18:08

Shakil Hossain

1,701
13
25

score 18 · Answer 8 · answered Jul 22 '19 at 17:57

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". Source link https://www.freakyjolly.com/resolved-when-i-faced-1273-unknown-collation-utf8mb4_0900_ai_ci-error/

score 11 · Answer 9 · answered May 31 '21 at 18:02

this error is caused because the conflict of different versions of MySQL. To solve this:

Open the sql file in any text editor
Find and replace all utf8mb4_0900_ai_ci with utf8mb4_unicode_ci
Save and try new mySql db file

after that try again,it works fine for me

score 8 · Answer 10 · answered Apr 06 '18 at 05:37

8

find and replace:

utf8mb4_unicode_520_ci

with

utf8_general_ci

in whole sql file

answered Apr 06 '18 at 05:37

Nur Uddin

1,798
1
28
38

5

`general` and `utf8` are both steps backward. – Rick James Jul 20 '18 at 19:56

score 8 · Answer 11 · answered Jun 24 '19 at 19:27

Getting collation error #1273 - Unknown collation: 'utf8mb4_unicode_520_ci' is caused by the difference of the MySQL version from which you export and our MySQL server to which you import. Basically, the Wordpress library for newer version checks to see what version of SQL your site is running on. If it uses MySQL version 5.6 or more, it assumes the use of a new and improved Unicode Collation Algorithm (UCA) called “utf8mb4_unicode_520_ci”. This is great unless you end up moving your WordPress site from a newer 5.6 version of MySQL to an older, pre 5.6 version of MySQL.

To resolve this you will either have to edit your SQL export file and do a search and replace, changing all instances of ‘utf8mb4_unicode_520_ci’ to ‘utf8mb4_unicode_ci’. Or follow the steps below if you have a PHPMyAdmin:

Click the Export tab for the database
Click the Custom radio button.
Go the section titled Format-specific options and change the drop-down for Database system or older MySQL server to maximize output compatibility with: from NONE to MYSQL40.
Scroll to the bottom and click GO.

Achu · Answer 12 · 2022-10-30T09:36:42.837

7

In my case I substitute it with utf8_general_ci with sed like this:

sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' MY_DB.sql 
sed -i 's/utf8mb4_unicode_520_ci/utf8_general_ci/g' MY_DB.sql

After that, I can import it without any issue.

edited Oct 30 '22 at 09:36

answered Nov 13 '19 at 07:32

Achu

819
1
8
14

If anyone is copying this command lines, the search is for utf8mb4_0900_ai_ci, not utf8_0900_ai_ci – crafter Oct 17 '22 at 07:07

score 7 · Answer 13 · answered Sep 24 '20 at 16:01

7

I solved it this way, I opened the .sql file in a Notepad and clicked CTRL + H to find and replace the string "utf8mb4_0900_ai_ci" and replaced it with "utf8mb4_general_ci".

answered Sep 24 '20 at 16:01

Herman

137
1
4

score 4 · Answer 14 · answered Feb 10 '23 at 11:54

According to my experience, the destination's MySQL server is an older version than the source. The required database collation is not present on the destination server.

To fix this, we can make a small change to the backup file. Replace "utf8mb4 0900 ai ci" with "utf8mb4 general ci" and "CHARSET=utf8mb4" with "CHARSET=utf8" in the database backup file.

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Save your file and restore the database.

Obmerk Kronen · Answer 15 · 2018-12-12T04:55:30.477

Late to the party, but in case this happens with a WORDPRESS installation :

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci

In phpmyadmin, under export method > Format-specific options( custom export )

Set to : MYSQL40

If you will try to import now, you now might get another error message :

1064 - You have an error in your SQL syntax; .....

That is because The older TYPE option that was synonymous with ENGINE was removed in MySQL 5.5.

Open your .sql file , search and replace all instances

from TYPE= to ENGINE=

Now the import should go smoothly.

score 1 · Answer 16 · answered Nov 07 '22 at 03:56

After a little investigation, I found that the MySQL server running on the destination is an older version than the source. So we got that the destination server doesn’t contain the required database collation.

Then we do a little tweak in the backup file to resolve this. Edit the database backup file(your_sql_file.sql) in a text editor and replace utf8mb4_0900_ai_ci with utf8mb4_general_ci and CHARSET=utf8mb4 with CHARSET=utf8.

I hope this solution might help you.

score 1 · Answer 17 · answered Nov 09 '22 at 08:49

Use the sed command to replace text in files directly

Linux OS

sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_general_ci/g' YOUR_SQL_FILE.sql

Mac OS

sed -i '' s/utf8mb4_unicode_520_ci/utf8mb4_general_ci/g' YOUR_SQL_FILE.sql

The help of this command i have fixed issue ERROR 1273 (HY000) at line 51: Unknown collation: 'utf8mb4_0900_ai_ci'

hexhad · Answer 18 · 2021-05-17T06:59:18.187

1273 - Unknown collation: 'utf8mb4_0900_ai_ci'

in my case I was unable to import DB using

ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;

and

ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

both. But changing it to this in .SQL File resolved the problem

ENGINE=InnoDB DEFAULT CHARSET=latin1;

UPDATED

using 'utf8mb4_general_ci'resolved the problem

ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

score 0 · Answer 19 · answered Oct 30 '21 at 19:17

Very strange that all answers recommend replacing collation. Which is a very bad practice because you want to use the same MySQL version as the one in development and the one in production. Therefore, your local mysql server should be the same.

First of all, Execute the query SHOW COLLATION to check all the collations your server supports. If you're using xampp or any other similar tool to start your server, it might come shipped with maria db server instead of mysql server.

What you should do is replace your current mysql (which is really mariadb) by the real mysql one.

So what you should do is simply replace your maria db server by mysql server.

@crafter because by that time the collation was not supported for mariadb. Not sure if this changed. — Alan Deep, Apr 15 '23 at 15:56

score 0 · Answer 20 · answered Mar 21 '23 at 10:31

In Addition

For large .sql files, I recommend using HeidiSQL (a free and open-source database tool) and pressing Ctrl+O to load the file by browsing from the folder.

After that press Ctrl+f and replace the "utf8mb4_0900_ai_ci" (in my case) with "utf8mb4_0900_ai_ci" and save the file by pressing Ctrl+s.

Finally, rerun the DB upload process, and cheers.

score 0 · Answer 21 · answered Jul 29 '23 at 17:47

0

I am facing the utf8mb4_0900_ai_ci this issue, I resolved it by using.

You just need to replace utf8mb4_0900_ai_ci with utf8mb4_general_ci

answered Jul 29 '23 at 17:47

Saptarshi Dey

19
3

Your answer was already covered [here](https://stackoverflow.com/a/69269659/8517948), [here](https://stackoverflow.com/a/57151403/8517948), [here](https://stackoverflow.com/a/67778445/8517948), [here](https://stackoverflow.com/a/64050168/8517948), [here, but as shell commands](https://stackoverflow.com/a/58832266/8517948), [here](https://stackoverflow.com/a/74341818/8517948) and [here](https://stackoverflow.com/a/75800025/8517948). As you have enough reputation you can upvote an existing answer instead of posting a new one. – LW001 Aug 04 '23 at 17:03

score -1 · Answer 22 · answered Apr 07 '21 at 19:33

-1

I simply removed the COLLATE and other attributes and left only till ENGINE. like the following

FROM:

ENGINE=InnoDB AUTO_INCREMENT=429 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

TO:

ENGINE=InnoDB;

and it worked for me just fine.

answered Apr 07 '21 at 19:33

ahmednawazbutt

823
12
34

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

22 Answers22

Linked