Ошибка кодировка базы utf8mb4 отличается от кодировки соединения utf8

Большинство web-мастеров, владельцев сайтов и людей, так или иначе сталкивающихся с web-программированием, рано или поздно встают перед задачей переноса базы данных сайта с одного хостинга на другой. Кроме этого, зачастую, у людей, тесно связанных с web-технологиями, возникает необходимость развернуть сайт локально на компьютере, к примеру, под программным решением Denwer. И в той, и в другой ситуации пользователь может столкнуться с различными ошибками экспорта/импорта базы данных. Об устранении одной из таких ошибок и пойдёт речь дальше.

Причины возникновения ошибки «#1273 — Unknown collation»

Итак, при импорте базы данных на хостинг или на Denwer процесс загрузки может быть прерван с ошибкой следующего содержания:

Ошибка
SQL-запрос:

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;

Ответ MySQL: Документация

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci' 

Последней строкой может быть указана и другая кодировка, например, utf8mb4_unicode_ci и тому подобное.

Вот как эта ошибка выглядит в браузере:

Причина возникновения ошибки #1273 в том, что в новых версиях СУБД MySQL добавляются новые кодировки сопоставления, которых может не оказаться на хостинге и в последних версиях продукта Denwer.
Исходя из озвученной причины ниже приведём три различных варианта решения проблемы Unknown collation.

Оптимальный вариант решения ошибки #1273, по нашему мнению, это обновление версии системы sql до актуального релиза. Главный минус данного способа заключается в том, что для его реализации вам необходимо иметь доступ до СУБД на сервере.
То есть, этот вариант подходит в том случае, если вы работаете с программным продуктом Denwer, расположенным на вашем компьютере (не рассматриваем ситуацию, при которой вы используете VDS — виртуальный выделенный сервер).

Чтобы обновить систему MySQL в установленном пакете Denwer на компьютере, в первую очередь, необходимо скачать нужные файлы новой версии sql. Предлагаем вам сделать это одним из двух способов:

1. Скачать один из прикреплённых к статье файлов с архивом, соответствующих разрядности вашей операционной системы: mysql-5.7.19-winx86 или mysql-5.7.17-winx64
2. Скачать на официальном сайте полный архив MySQL Community Server под вашу версию операционной системы и извлечь оттуда файлы mysql.exe, mysqlcheck.exe, mysqld.exe и mysql_upgrade.exe, расположенные в каталоге bin

Далее останавливаем Денвер запуском ярлыка Stop Denwer, переходим на компьютере в директорию MySQL установленного пакета Denwer. По умолчанию задан следующий путь:

C:WebServersusrlocalmysql-5.5bin

В этот каталог помещаем скачанные exe-файлы и соглашаемся на замену.
Запускаем Денвер ярлыком Start Denwer и повторно импортируем базу данных. Ошибка устранена.

Экспорт базы данных сайта в режиме совместимости со старым MySQL

Этот способ обхода ошибки Unknown collation: utf8mb4 может быть использован в том случае, если мы имеем возможность повторного экспорта базы данных. Данное условие вытекает из того, что основные действия в этом варианте производятся на этапе создания бэкапа, а не его загрузки на сервер.

Суть заключается в том, что перед выгрузкой базы данных нам необходимо активировать расширенные настройки в приложении создания бэкапа и включить режим совместимости выгружаемой базы.

Рассмотрим, как выгрузить базу сайта в режиме совместимости MySQL на примере web-приложения phpMyAdmin.
Заходим в phpMyAdmin, выбираем нашу базу в списке и переходим на вкладку Экспорт

На открывшейся странице в разделе Метод экспорта ставим флаг-галку у пункта Обычный — отображать все возможные настройки. Теперь прокручиваем страницу вниз до раздела Параметры формата и в пункте Максимальная совместимость с системой базы данных, или устаревшей версией MySQL: из выпадающего списка выбираем вариант MYSQL40.
Остальные параметры можно не менять, скроллим страницу до конца и нажимаем кнопку Вперёд.

Сформированный архив будет импортироваться на сервер с MySQL старой версии без ошибок.

Если после этого при открытии страничек сайта будут проблемы с кодировкой: некорректное отображение текста, «иероглифы» и так далее — то дополнительно будет необходимо в файле конфигурации вашего CMS (к примеру, в WordPress это файл wp-config.php) заменить параметр, отвечающий за кодировку с utf8mb4 на utf8, сохранить внесённые изменения и обновить страницу

Ручное изменение кодировки базы данных сайта

И последний пример борьбы с ошибкой импорта базы данных сайта, который мы рассмотрим в рамках текущей публикации — это изменение кодировки SQL базы в таблице.
Проделать это можно как с помощью специальных sql-команд, так и вручную. Рассмотрим второй вариант.

Для того, чтобы отредактировать кодировку в базе, нам необходимо разархивировать бэкап сайта, если он находится в сжатой папке zip или rar. После этого открываем получившийся файл с расширением *.sql в удобном текстовом редакторе, например, в Notepad++
В окне текстового редактора нам необходимо заменить значения utf8mb4_unicode_ci и utf8mb4_unicod_520i на utf8_general_ci. Кроме этого, если в таблицах базы встречается значение utf8mb4, то, как и в предыдущем способе, его также надо изменить на utf8.

После проделанной корректировки сохраняем изменённый файл и повторяем импорт базы данных на сервер.

Мы рассмотрели три способа борьбы с ошибкой #1273 — Unknown collation: ‘utf8mb4_unicode_ci’.
Надеемся, что вам удалось побороть данную проблему одним из вариантов.

WordPress 4.2 introduced support for «utf8mb4» character encoding for security reasons, but only MySQL 5.5.3 and greater support it. The way the installer (and updater) handles this is that it checks your MySQL version and your database will be upgraded to utfmb4 only if it’s supported.

This sounds great in theory but the problem (as you’ve discovered) is when you are migrating databases from a MySQL server that supports utf8mb4 to one that doesn’t. While the other way around should work, it’s basically a one-way operation.

As pointed out by Evster you might have success using PHPMYAdmin’s «Export» feature. Use «Export Method: Custom» and for the «Database system or older MySQL server to maximize output compatibility with:» dropdown select «MYSQL 40«.

For a command line export using mysqldump. Have a look at the flag:

$ mysqldump --compatible=mysql4

Note: If there are any 4-byte characters in the database they will be corrupted.

Lastly, for anyone using the popular WP Migrate DB PRO plugin, a user in this WordPress.org thread reports that the migration is always handled properly but I wasn’t able to find anything official.

The WP Migrate DB plugin translates the database from one collation to the other when it moves 4.2 sites between hosts with pre- or post-5.5.3 MySQL

At this time, there doesn’t appear to be a way to opt out of the database update. So if you are using a workflow where you are migrating a site from
a server or localhost with MySQL > 5.5.3 to one that uses an older MySQL version you might be out of luck.

Озадачился я на днях с BB-редактором для модуля комментариев и оказалось, что Windows 10 Emoji не работают в utf8 кодировке, для 1C Битрикс Emoji нужна utf8mb4 кодировка.

Оказывается уже и кодировка utf8 считается морально устаревшей, вместо нее лучше использовать кодировку utf8mb4, т.к. она поддерживает гораздо большее количество символов выше 0xFFFD, в том числе Bitrix Emoji, полностью совместима с utf8 и поддерживается сервером MySQL с версии 5.5.3.

Итак, если кодировка (charset) будет utf8mb4, то сравнение (collation) будет utf8mb4_unicode_ci

Кодировка MySQL-сервера

Можно в самом конфиге MySQL (my.cnf) задать кодировку сервера и соединения по умолчанию.
Но если вы в этом ничего не понимаете или у вас шаред, то пропустите этот шаг, достаточно будет настроить это в конфигах Битрикса для конкретного сайта, а не всего сервера.

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci
init-connect="SET NAMES utf8 COLLATE utf8_unicode_ci"

Кодировка новой базы

Если создается новая база под новый проект, например в программе HeidiSQL, то достаточно задать сравнение, все новые таблицы будут использовать сравнение базы, тут все просто.

Кодировка существующей базы

Это самое сложное, сделать конвертацию действующей базы на рабочем проекте, просто поменять сравнение у базы и таблиц будет недостаточно, еще нужно задать сравнение и кодировку всем полям таблицы типа: char, varchar, tinytext, text, mediumtext, longtext, enum, set, json

Для автоматизации конвертации всей базы я написал свой скрипт busconvert.php, его нужно залить на сайт на Битриксе, в любое место, главное открыть его в браузере и нажать кнопку Начать конвертацию.

BUSCONVERT v1.0.0

Сначала скрипт выводит текущие значения кодировки и сравнения до конвертации.
После нажатия на кнопку Начать конвертацию скрипт начнет конвертировать всю текущую базу сайта.

Выделено желтым — кодировка, в которую скрипт будет конвертировать.
Выделено красным- текущие значения.

По окончании работы выводит результаты После конвертации и Лог конвертации, а также количество затронутых таблиц  и новую кодировку базы, если все прошло успешно.

В таблице лога выводится новая кодировка и все SQL-запросы, которые совершал скрипт над данными полей каждой таблицы.

После успешной конвертации остается поменять настройки соединения с базой в 2-х файлах.
Здесь в запросах заменяем utf8 на utf8mb4

//bitrix/php_interface/after_connect.php
$DB->Query("SET NAMES 'utf8mb4'");
$DB->Query('SET collation_connection = "utf8mb4_unicode_ci"');

//bitrix/php_interface/after_connect_d7.php
$connection = BitrixMainApplication::getConnection();
$connection->queryExecute("SET NAMES 'utf8mb4'");
$connection->queryExecute('SET collation_connection = "utf8mb4_unicode_ci"');

А также в файле /bitrix/.settings.php

'utf_mode' =>
  array(
     'value'    => true,
     'readonly' => true,
  ),

Еще желательно сбросить весь кэш сайта, либо в админке, либо удалив вот эти папки с диска.

/bitrix/cache
/bitrix/managed_cache
/bitrix/stack_cache
/bitrix/html_pages/example.com

Все, это должно помочь, других настроек нет.

Чтобы проверить новую кодировку откройте статью, новость или товар, и в форме редактирования, например, в названии вставьте Windows 10 Emoji комбинация Windows + . в английской раскладке.

Вот такая красота Emoji 🤞😜🤞 будет везде, и в заголовке страницы, и в хлебных крошках, и в заголовке окна браузера, и в результатах поиска Google и Yandex ваши материалы станут более заметны!

Заметки

1) Скрипт будет работать, даже если закрыть окно браузера, пока все не выполнит.

2) Скрипт тестировался только на кодировке utf8, для кириллицы cp1251 пока не подходит, позже постараюсь и этот момент доработать.

3) Если все же хочется попробовать другую кодировку, то изменяйте значение в этих переменных в самом начале скрипта.

$charset = 'utf8mb4';
$collate = 'utf8mb4_unicode_ci';

4) Я базу тестового сайта конвертировал раз 50 и никаких проблем, если что, можно запускать скрипт несколько раз.

5) При конвертации тип и другие параметры поля не изменяются, тщательно все проверил, некоторые запросы почему-то изменяли тип поля text на mediumtext

А проверить это можно инструментом от 1С Битрикс Проверка системы.

Настройки - Инструменты - Проверка системы

Кодировка соединения — будет ругаться, пока Битрикс про это дело не в курсе.
Структура базы данных — Если успешно, значит конвертация прошла успешно, ни одно поле не изменилось в процессе конвертации, важно проверить этот момент еще до конвертации, чтобы потом не гадать, когда это случилось.
Если перед конвертацией скрипт найдет отличающиеся от базы поля, то надо все исправить, кроме случаев, когда вы сами изменяли  поля в таблицах и точно знаете, что делаете.

В этом случае следите за тем, что бы кто-нибудь из ваших разрабов или даже клиент не вернул тут все обратно, скрипт Битрикса может вернуть кодировки обратно в utf8.

6) На двух тестовых сайтах, которые у меня на OSPanel, никаких проблем с конвертацией не было, а вот на текущем пришлось повозиться с такой вот проблемой.

2017-12-05 03:20:12 - Host: tuning-soft.ru - UNCAUGHT_EXCEPTION - [BitrixMainDBSqlQueryException] 
Mysql query error: (1071) Specified key was too long; max key length is 767 bytes (400)
ALTER TABLE `b_user_option` MODIFY `NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT  NULL ;

Ровно в 20 таблицах пришлось менять тип поля с varchar(255) на varchar(191)

Получется, что для хранения строки utf8 нужно 3 байта на символ, а utf8mb4 нужно 4 байта.
Предел для utf8 составляет 767/3 = ~255 символов, для utf8mb4 это 767/4 = ~191 символ.

• utf8 VARCHAR(255)
• utf8mb4 VARCHAR(191)

Но почему-то это не для всех полей varchar(255) требовал, только для некоторых, не знаю почему.

Менял я длину значения поля в базе с помощью своего модуля TSAdminer, вы можете это сделать в любом инструменте для работы с БД.

Ошибки возможно и у вас будут, их просто надо исправить все, пока конвертация не закончится.

Файлы к статье

• busconvert v.1.0.0

Переходим с utf8 на utf8mb4 в MySQL.

Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.

Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.

utf8mb4_general_ci или utf8mb4_unicode_ci

В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.

Настройка кодировки utf8mb4 для СУБД MySQL

Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.

Если у вас уже есть базы данных, то обязательно создайте резервные копии всех баз данных.

В конфигурационном файле MySQL (my.ini(windows)/my.cnf(Linux)) необходимо изменить кодировку на utf8mb4:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
init_connect ='SET collation_connection = utf8mb4_unicode_ci'
init_connect ='SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Проверяем корректность работы применимых настроек:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Результат:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set, 1 warning (0.00 sec)

Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL

Запросы для измениния кодировки и сравнения для базы данных, таблиц и столбцов на utf8mb4.

Для базы данных:

ALTER DATABASE [db_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Для таблицы:

ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Для столбцов:

ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Восстановление и оптимизация всех таблиц

После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:

REPAIR TABLE [table_name];
OPTIMIZE TABLE [table_name];

Или с использованием команды mysqlcheck:

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Пример миграции для Yii2

В этом примере мы изменим кодировку для столбца content в таблице post:

/**
* @return void
* @throws yiidbException
*/
public function safeUp()
{
 $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
 Yii::$app->db->createCommand($sql)->execute();
}

/**
* @return void
* @throws yiidbException
*/
public function safeDown()
{
 $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
 Yii::$app->db->createCommand($sql)->execute();
}

Битрикс-стандарт INFO Оптимизированные тарифы для быстрой работы корпоративных и информационных сайтов на 1С Битрикс.