Reputation: 3977

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

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’

Upvotes: 361

Answers (25)

Wasid Hossain

Reputation: 65

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_general_ci" and save the file by pressing Ctrl+s.

Finally, rerun the DB upload process, and cheers.

Upvotes: 1

Hana Hasanah

Reputation: 244

SQL Error (1273)

In my case, I got the same code but with the error "Unknown collation: 'utf8mb4_0900_ai_ci'" when importing a MySQL db dump in HeidiSQL in version 5.7.33 (version lower than 8.0); You can check the version with the command:

SELECT VERSION();

utf8mb4_0900_ai_ci is only supported in MySQL 8.0+. If you are using a version of MySQL lower than 8.0, this collation is not available.

Replace all utf8mb4_0900_ai_ci with utf8mb4_unicode_ci or utf8mb4_general_ci. Hope it helps!

Upvotes: 1

Nanhe Kumar

Reputation: 16307

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'

Upvotes: 6

Vel

Reputation: 9342

Replace with alter query

ALTER TABLE `tablename` COLLATE = utf8mb4_general_ci ;

Upvotes: 0

AlMounkez

Reputation: 77

n your Laravel project, locate the config/database.php file. Inside this file, find the 'connections' array and look for the configuration related to your MySQL connection.

Within the MySQL connection configuration, add or update the 'collation' parameter to use a supported collation like 'utf8mb4_unicode_ci'

Upvotes: 1

Saptarshi Dey

Reputation: 205

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

Upvotes: 13

Vishnu

Reputation: 4293

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.

Upvotes: 5

Sabba Keynejad

Reputation: 8591

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

Upvotes: 512

Imam Hossain Roni

Reputation: 1076

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.

Upvotes: 1

Achu

Reputation: 849

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.

Upvotes: 8

Alan Deep

Reputation: 2105

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.

Upvotes: 1

P.Githinji

Reputation: 1598

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

Upvotes: 49

Hassan Saeed

Reputation: 7130

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

Upvotes: 12

hexhad

Reputation: 1303

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;

Upvotes: 0

ahmednawazbutt

Reputation: 833

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.

Upvotes: -1

Herman

Reputation: 135

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

Upvotes: 7

Code Spy

Reputation: 9964

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/

Upvotes: 18

Mushfiqur Rahman

Reputation: 492

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.

Upvotes: 9

Obmerk Kronen

Reputation: 15979

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.

Upvotes: 2

Ryabinin Sergey

Reputation: 564

easy replace

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

Upvotes: 39

Nur Uddin

Reputation: 1840

find and replace:

utf8mb4_unicode_520_ci

with

utf8_general_ci

in whole sql file

Upvotes: 8

VUUB

Reputation: 525

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!

Upvotes: 48

Shakil Hossain

Reputation: 1743

just remove "520_"
utf8mb4_unicode_520_ci → utf8mb4_unicode_ci

Upvotes: 22

savani sandip

Reputation: 4434

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;

Upvotes: 412

SherylHohman

Reputation: 17980

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.

Upvotes: 71

#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’

Answers (25)

Related Questions