I believe this error is caused because the local server and live server are running different versions of MySQL. To solve this:
Hope that helps
In my case it turns out my
new server was running
old server was running
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
MySQL 5.6 does.
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
- replace instances of
- import your updated
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!
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql
just remove "520_"
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/
find and replace:
in whole sql file
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:
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.
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".
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 :
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.
.sql file , search and replace all instances
Now the import should go smoothly.