#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
MysqlSqlWordpressCollationMysql Problem Overview
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!
Mysql Solutions
Solution 1 - Mysql
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
withutf8mb4_unicode_ci
- Save and upload to a fresh mySql db
Hope that helps
Solution 2 - Mysql
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 3 - Mysql
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
andutf8mb4_unicode_520_ci
...withutf8mb4_unicode_ci
- import your updated
.sql
file.
Solution 4 - Mysql
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 - Mysql
easy replace
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql
Solution 6 - Mysql
just remove "520_"
utf8mb4_unicode_520_ci
→ utf8mb4_unicode_ci
Solution 7 - Mysql
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/
Solution 8 - Mysql
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
Solution 9 - Mysql
find and replace:
utf8mb4_unicode_520_ci
with
utf8_general_ci
in whole sql file
Solution 10 - Mysql
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.
Solution 11 - Mysql
In my case I substitute it with utf8_general_ci
with sed like this:
sed -i 's/utf8_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.
Solution 12 - Mysql
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".
Solution 13 - Mysql
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
Solution 14 - Mysql
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.
Solution 15 - Mysql
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;
Solution 16 - Mysql
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.
Solution 17 - Mysql
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.