WordPress, Программное обеспечение
- 20.11.2015
- 48 176
- 10
- 19.01.2020
- 14
- 13
- 1
- Содержание статьи
- Решение проблемы
- Правильный способ
- НЕ Правильный способ
- Комментарии к статье ( 10 шт )
- Добавить комментарий
- Решение проблемы
После покупки очередного сайта и переносе его на свой старенький хостинг, столкнулся с этой ошибкой. Сайт на CSM WordPress. Данная проблема возникает из-за того, что начиная с версии MySQL 5.5.3 и выше — появилось сравнение utf8mb4_unicode_ci, которое не поддерживается более старыми версиями. Из-за этого при импорте базы из дампа более новой версии (в моем случае это Mysql 5.5.45) на старую версию — 5.1.73 и вылезла данная ошибка.
error #1273 — Unknown collation: ‘utf8mb4_unicode_ci’
Решение проблемы
Итак, проблему можно решить как минимум двумя разными способами:
Правильный способ
Просто обновить MySQL до версии 5.5.3 или выше. После этого данная ошибка должна самоустраниться
НЕ Правильный способ
Открываем дамп базы данных любым текстовым редактором и делаем замену строки «utf8mb4_unicode_ci» на «utf8_general_ci» для всех найденных совпадений. После этого сохраняем новую версию дампа и уже её импортируем в MySQL. Если у вас вылезет ошибка «Unknown character set: ‘utf8mb4’«, то прочитайте эту статью.
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’
asked Feb 22, 2017 at 7:23
Shishil PatelShishil Patel
3,3492 gold badges12 silver badges16 bronze badges
7
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
starball
14.9k6 gold badges26 silver badges129 bronze badges
answered May 22, 2017 at 21:15
Sabba KeynejadSabba Keynejad
7,8072 gold badges26 silver badges22 bronze badges
6
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;
dǝɥɔS ʇoıןןƎ
1,6345 gold badges19 silver badges42 bronze badges
answered Feb 22, 2017 at 7:26
savani sandipsavani sandip
4,1521 gold badge10 silver badges8 bronze badges
9
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.
answered Jul 3, 2017 at 3:21
SherylHohmanSherylHohman
16.3k17 gold badges86 silver badges93 bronze badges
3
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!
answered Mar 4, 2018 at 20:22
1
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
answered Sep 21, 2021 at 13:23
P.GithinjiP.Githinji
1,41911 silver badges5 bronze badges
2
easy replace
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' your_sql_file.sql
4b0
21.7k30 gold badges95 silver badges141 bronze badges
answered Jun 29, 2018 at 5:05
1
just remove «520_»
utf8mb4_unicode_520_ci
→ utf8mb4_unicode_ci
Stphane
3,3685 gold badges31 silver badges47 bronze badges
answered Feb 17, 2018 at 18:08
0
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
answered May 31, 2021 at 18:02
Hassan SaeedHassan Saeed
6,2261 gold badge37 silver badges37 bronze badges
find and replace:
utf8mb4_unicode_520_ci
with
utf8_general_ci
in whole sql file
answered Apr 6, 2018 at 5:37
Nur UddinNur Uddin
1,7901 gold badge28 silver badges38 bronze badges
1
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.
answered Jun 24, 2019 at 19:27
0
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«.
answered Sep 24, 2020 at 16:01
HermanHerman
1371 silver badge4 bronze badges
0
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.
answered Nov 13, 2019 at 7:32
AchuAchu
8191 gold badge8 silver badges14 bronze badges
1
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.
answered Feb 10 at 11:54
VishnuVishnu
3,8292 gold badges18 silver badges18 bronze badges
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.
answered Nov 5, 2018 at 5:44
Obmerk KronenObmerk Kronen
15.6k16 gold badges66 silver badges105 bronze badges
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.
answered Nov 7, 2022 at 3:56
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’
answered Nov 9, 2022 at 8:49
Nanhe KumarNanhe Kumar
15.3k5 gold badges78 silver badges70 bronze badges
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;
answered May 17, 2021 at 4:48
hexhadhexhad
1,11912 silver badges14 bronze badges
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.
answered Oct 30, 2021 at 19:17
Alan DeepAlan Deep
2,0171 gold badge13 silver badges22 bronze badges
2
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_0900_ai_ci"
and save the file by pressing Ctrl+s.
Finally, rerun the DB upload process, and cheers.
answered Mar 21 at 10:31
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.
answered Apr 7, 2021 at 19:33
При переносе базы данных (БД) сайта с одного хостинга на другой может возникнуть уведомление об ошибке кодировки #1273 «Unknown collation utf8mb4 unicode 520 ci». Оно связано с тем, что импортируемая старая база была создана в кодировке более старой версии MySQL, чем новая.
Чтобы решить эту проблему, требуется открыть дамп БД любым удобным редактором кода (н/п Notepad++, SublimeText3, Visual Studio Code) или встроенным «блокнотом» (Notepad). Затем совершить следующую последовательность действий:
- найти строчки с отрывком «utf8mb4_unicode_ci» (возможен другой вариант — «utf8mb4_unicode_520_ci») и заменить их на «utf8_general_ci»;
- найти строчки с отрывком «utf8mb4» и заменить их на «utf8».
После совершенных замен импорт базы данных пройдёт без ошибок и сайт сможет нормально функционировать.
Так же можно попробовать выполнить загрузку базы данных используя MariaDB 10.3 в качестве сервера баз данных, а не MySQL 5.5.60. Для этого воспользуйтесь инструкцией по изменению типа сервера баз данных. В этом случае можно обойтись без изменений в самой базе.
Решение ошибки, которая возникала при импорте базы данных MySQL в панели управления phpMyAdmin.
При переносе базы данных WordPress с одного хостинга на другой возникла ошибка:
#1273 — Unknown collation: ‘utf8mb4_unicode_520_ci’
В целом сообщение об ошибке было таким:
Ошибка
SQL запрос:
— Структура таблицы `wp_subscribe_reloaded_subscribers`
CREATE TABLE `wp_subscribe_reloaded_subscribers` (
`stcr_id` int(11) NOT NULL,
`subscriber_email` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL,
`salt` int(15) NOT NULL,
`subscriber_unique_id` varchar(50) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Ответ MySQL:
#1273 — Unknown collation: ‘utf8mb4_unicode_520_ci’
Причина оказалась в том, что таблица в базе данных MySQL плагина Subscribe To Comments Reloaded находилась в кодировке utf8mb4_unicode_520_ci. А для верной работы необходима кодировка utf8mb4_unicode_ci.
Ошибка Unknown utf8mb4_unicode_520 может возникнуть из-за неверного формата таблицы любого другого плагина. Например, в одном из случаев, у меня такая ошибка возникала сразу во многих других таблицах (не только относящихся к плагинам), например:
— Структура таблицы `wp_commentmeta`
CREATE TABLE `wp_commentmeta` (
`meta_id` bigint(20) UNSIGNED NOT NULL,
`comment_id` bigint(20) UNSIGNED NOT NULL DEFAULT ‘0’,
`meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_520_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Любая ошибка такого рода (не важно что за таблица или плагин) исправляется одинаково. Причем операции со вкладкой «Структура» можно и пропускать и делать только «Операции» (об этом ниже). Но чтобы всё точно работало, лучше сделать и то и другое.
Если ИМЕЕТСЯ доступ к базе данных старого хостинга
Заходим в эту базу через phpMyAdmin.
В колонке слева выбираем таблицу wp_subscribe_reloaded_subscribers. Сверху панели выбираем вкладку «Структура».
Нажимаем изменить и меняем сравнение utf8mb4_unicode_520_ci на utf8mb4_unicode_ci.
Затем выбираем вкладку «Операции», снова выбираем кодировку utf8mb4_unicode_ci, отмечаем галочку Change all column collations. Нажимаем вперед, подтверждаем, что хотим выполнить эту операцию.
После этого можно экспортировать свою базу данных и импортировать на новый хостинг. На этот раз всё должно пройти гладко.
Если доступа к базе данных старого хостинга НЕТ
В этом случае надо открыть базу данных через специализированные программы, например, Notepad++ или Transmit в Mac OS. Там найти строчки utf8mb4_unicode_520_ci и заменить их на utf8mb4_unicode_ci. Сохранить и дальше импортировать к себе на хостинг.
Если ошибку вызывает другой плагин, не обязательно wp_subscribe_reloaded_subscribers или какая иная таблица, то действовать нужно по аналогии с этой инструкцией, но уже для другой таблицы.
The collation utf8mb4_0900_ai_ci is a character set collation for MySQL databases, introduced in MySQL 8.0.1. It is based on the Unicode Collation Algorithm (UCA) 9.0.0, and the character set is utf8mb4, which supports a wide range of Unicode characters.
The “ai” in the collation name stands for “accent insensitive” and the “ci” stands for “case insensitive.” This means that comparisons between characters are done without considering differences in case or accents.
If you are receiving an “Unknown collation” error, it may be because your MySQL server version is older than 8.0.1 and doesn’t support this collation. To fix this issue, you can:
Problem
During the migration of a web application, I got the below error while restoring a database on another server. The collation id may differ based on the MySQL version.
Error message:
Error 1273 (HY000) at line 25 Unknown collation: ‘utf8mb4_0900_ai_ci’
See the error screenshot during database restoration.
Here you go with a solution.
Solution
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 in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.
- Replace the below string:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- With:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Here we are changing the CHARSET to utf8, that is the older version and have limitation, Read the implications at the end of this article before making the changes in database.
Save your file and restore the database.
The Linux system users can use the sed command to replace text in files directly.
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql
That it. after the above changes, the database was successfully restored!
Hope this is solution helped you to resolve “Unknown collation: ‘utf8mb4_0900_ai_ci’” issue.
Limitations of UTF8 Character Set:
Changing the character set from utf8mb4 to utf8 in MySQL is not inherently bad, but it may have some implications that you should consider before making the change:
- Limited Unicode support: The utf8 character set in MySQL only supports a limited range of Unicode characters, specifically the Basic Multilingual Plane (BMP), which includes characters from the Unicode code points U+0000 to U+FFFF. In contrast, utf8mb4 supports the full range of Unicode characters, including supplementary characters (code points U+10000 to U+10FFFF), such as emojis and certain rare symbols or scripts. If you need to store these supplementary characters in your database, you should use utf8mb4.
- Data loss or corruption: If your existing data contains characters outside of the BMP, converting the character set from utf8mb4 to utf8 may result in data loss or corruption. These characters will be replaced with the Unicode replacement character (U+FFFD) during the conversion process.
- Index length limitations: The utf8 character set uses less storage space (up to 3 bytes per character) compared to utf8mb4 (up to 4 bytes per character). This may help you work around index length limitations, especially with older versions of MySQL. However, you should be aware of the trade-offs in terms of Unicode support.