Ошибка вида MySQL Query Error: [[1366] Incorrect string value: ‘\xB1N\xC30\x10\xFD…’ for column ‘COOKIES’ at row 1] или как изменить кодировку полей таблиц в MySQL

07 Апр, 2020 16:55, OliLukoye

При переносе очередного сайта и разворачивание его на VDS-ке, у меня появилась ошибка:

MySQL Query Error: [[1366] Incorrect string value: '\xB1N\xC30\x10\xFD...' for column 'COOKIES' at row 1]

1	MySQL Query Error: [[1366] Incorrect string value: '\xB1N\xC30\x10\xFD...' for column 'COOKIES' at row 1]

Она была связана с тем, что не получалось сохраненить в БД куки из-за того что кодировка из скрипта не совпадала с кодировкой в БД.

Что бы это исправить, необходимо сменить кодировку в БД и определить эту кодировку в скриптах битрикса для подключения с базой данных. Т.к. ошибка может возникать из-за того, что пытается записать в БД символы, которые состоят не из 3 байтов, как в UTF-8, а из 4-х, то для хранения поля необходимо использовать utf8mb4. Поменять кодировку можно с помощью скрипта php:

<?php
$dbName = 'YOUR_DB_NAME';
$user = 'YOUR_USER_NAME';
$password = 'YOUR_PASSWORD';
$dsn = 'mysql:dbname='.$dbName.';host=localhost';

$time_n=time();

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    exit('Подключение не удалось: ' . $e->getMessage());
}

$sql = "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$dbName';";
$arErrors = [];
$cnt = 0;
foreach ($dbh->query($sql) as $row) {
    try {
        $dbh->exec($row[0]);
    } catch (PDOException $e) {
        $arErrors[] = 'Ошибка: ' . $e->getMessage();
    }
    $cnt++;
}

$time_k=time();
echo 'Затронуто таблиц: ' . $cnt . ' Из них с ошибками: ' . count($arErrors) . '<br>';
echo 'Время выполнения: ' . strftime('%M:%S',$time_k-$time_n) . '<br>';
if (count($arErrors) > 0) {
    echo 'Список ошибок: <br>';
    echo '<pre>';
    print_r($arErrors);
    echo '</pre>';
}

<?php

$dbName = 'YOUR_DB_NAME';

$user = 'YOUR_USER_NAME';

$password = 'YOUR_PASSWORD';

$dsn = 'mysql:dbname='.$dbName.';host=localhost';

$time_n=time();

try {

$dbh = new PDO($dsn, $user, $password);

} catch (PDOException $e) {

exit('Подключение не удалось: ' . $e->getMessage());

}

$sql = "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '$dbName';";

$arErrors = [];

$cnt = 0;

foreach ($dbh->query($sql) as $row) {

try {

$dbh->exec($row[0]);

} catch (PDOException $e) {

$arErrors[] = 'Ошибка: ' . $e->getMessage();

}

$cnt++;

}

$time_k=time();

echo 'Затронуто таблиц: ' . $cnt . ' Из них с ошибками: ' . count($arErrors) . '<br>';

echo 'Время выполнения: ' . strftime('%M:%S',$time_k-$time_n) . '<br>';

if (count($arErrors) > 0) {

echo 'Список ошибок: <br>';

echo '<pre>';

print_r($arErrors);

echo '</pre>';

}

Результат выполнения скрипта:

Затем в конфигах битрикса прописать:
— в файле bitrix/php_interface/after_connect_d7.php:

$connection = \Bitrix\Main\Application::getConnection();
$connection->queryExecute("SET NAMES 'utf8'");
$connection->queryExecute('SET collation_connection = "utf8mb4_unicode_ci"');

$connection = \Bitrix\Main\Application::getConnection();

$connection->queryExecute("SET NAMES 'utf8'");

$connection->queryExecute('SET collation_connection = "utf8mb4_unicode_ci"');

— в файле bitrix/php_interface/after_connect.php:

$DB->Query("SET NAMES 'utf8'");
$DB->Query('SET collation_connection = "utf8mb4_unicode_ci"');

1 2	$DB->Query("SET NAMES 'utf8'"); $DB->Query('SET collation_connection = "utf8mb4_unicode_ci"');

После, если система заработает, следеут сделать проверку системы:

Bitrix проверка системы
Если будут ошибки, то исправить их следуя подсказкам битрикса.

Если же ошибка не исправилась, значит необходимо поиграться с вариантами кодировки. В моем случае эти действия помогли, но не с первого раза. По итогу оставил кодировку utf8 и utf8_unicode_ci, и она чудо образом стала работать. Почему до этого выпадала ошибка, для меня осталось загадкой 🙂