#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’

Any help would be appreciated!

Solution 1

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;

Solution 2

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

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

Hope that helps

Solution 3

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.

Solution 4

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!

Solution 5

easy replace

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

View more solutions

Comments

[Nabil Kadimi]

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

[squarecandy]

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.

[Rick James]

What version of WordPress? See make.wordpress.org/core/2015/04/02/the-utf8mb4-upgrade

[WPZA]

There's also a quick guide here (wpza.net/unknown-collation-utf8mb4_unicode_520_ci) which explains all the 3-steps you should take to ensure you've done everything.

[Rodrigo]

sed -i dump-file.sql -e 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g'

[Amir]

I recommend the answer by Sabba (stackoverflow.com/a/44122258/168309) as it retains the utf8mb4

[Maor Barazany]

The solution below of @Sabba Keynejad is better in my opinion, since it keeps the mb4, you should try that first.

[John]

HELL NO!!! It has been widely documented that utf8mb4_unicode_520_ci has much better UTF-8 support than utf8mb4_unicode_ci! Sauce: mysql.rjweb.org/doc.php/charcoll#best_practice with visual clarification proving 520_ci is superior here: mysql.rjweb.org/utf8_collations.html

[aubreypwd]

Note, I had to replace this in multiple places in the SQL File. Also there were places where I had to do stackoverflow.com/a/44122258/1436129 below too.

[Gremio]

@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.

[Gone Coding]

Actually found this answered worked when the linked answer by Sabba did not. Presumably because of my MySql installation defaults. +1

[squarecandy]

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.

[Rick James]

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]

5.6 is when 520 was added.

[Rick James]

general and utf8 are both steps backward.

[NightOwl]

This is the best answer.

[Haritsinh Gohil]

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.

[ospider]

This is NOT a very correct answer, utf8 and utf8mb4 is not the same thing in mysql

[Osvaldas]

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

[Rakesh]

I suggest this solution because it replaces all "utf8mb4_unicode_520_ci" occurrences. Whereas savani sandip answer leaves some.

[Ken Sawyerr]

This should be the correct answer. Most of the other solutions are more like hacks. Thanks @SherylHohman

[flying-dev]

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

[robbclarke]

Lifesaver. Thanks!

[Moh .S]

I resolved it by replacing 'utf8mb4_unicode_520_ci' with 'utf8mb4_general_ci'

[agent18]

I changed utf8mb4_0900_ai_ci --> utf8mb4_general_ci. mysql dist 5.7.33 on ubuntu 16 :) and it worked.

[yts61]

perfect solution

[Eskay Amadeus]

This answer worked for me.

[Atiq Baqi]

faced similar issue for utf8mb4_0900_ai_ci , your solution worked perfectly