#1273 â€“ Unknown collation: â€˜utf8mb4_unicode_520_ciâ€™

This topic is empty.

Viewing 9 posts - 16 through 24 (of 24 total)

← 1 2

Author

Posts
May 31, 2021 at 6:02 am #10693
hassan-saeed
Participant
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_unicode_520_ci with utf8mb4_unicode_ci
- Save and try new mySql db file
after that try again,it works fine for me
September 21, 2021 at 1:23 am #10697
p.githinji
Participant
I experienced a challenge importing data into mysql exported using mysql workbench. It is a collation issue.
I solved this error by:
1. Opening the .sql file using text editor
2. Replacing "utf8mb4_unicode_520_ci" with "utf8mb4_general_ci".
3. Saving the file as .sql and importing it
It worked
October 30, 2021 at 7:17 am #10681

alan-deep
Participant

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.

November 7, 2022 at 3:56 am #10684

imam-hossain-roni
Participant

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_unicode_520_ci with utf8mb4_general_ci and CHARSET=utf8mb4 with CHARSET=utf8.

I hope this solution might help you.

November 9, 2022 at 8:49 am #10686
nanhe-kumar
Participant
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_unicode_520_ci’
February 10, 2023 at 11:54 am #10687
vishnu
Participant
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_unicode_520_ci;
```
with:
```
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
```
Save your file and restore the database.
March 21, 2023 at 10:31 am #10682

wasid-hossain
Participant

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_unicode_520_ci" (in my case) with "utf8mb4_unicode_520_ci" and save the file by pressing Ctrl+s.

Finally, rerun the DB upload process, and cheers.

July 29, 2023 at 5:47 am #10689

saptarshi-dey
Participant

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

You just need to replace utf8mb4_unicode_520_ci with utf8mb4_general_ci

March 15, 2024 at 8:36 am #10683

almounkez
Participant

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

Posts