Перейти к содержимому
BXWiki

Illegal mix of collations - разные кодировки таблиц и соединения

Запрос падает на сравнении строк из разных таблиц, а вместо букв иногда видны знаки вопросов. Разбираем причины по убыванию частоты.

Как проверить

Смотрим, сколько кодировок в базе:

SELECT TABLE_COLLATION, COUNT(*) AS cnt FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() GROUP BY TABLE_COLLATION;
-- одна строка в ответе - всё в порядке, две и больше - причина найдена

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

Находим выбивающиеся колонки:

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> (SELECT TABLE_COLLATION FROM information_schema.TABLES
                         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_user')
LIMIT 20;
-- за образец берут таблицу платформы: у неё кодировка заведомо правильная

Колонка умеет отличаться от своей таблицы, и это самый неприятный случай. Таблица в списке выглядит правильной, а сравнение всё равно падает - потому что разошлась отдельная колонка.

Смотрим кодировку соединения:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation_connection';
-- кодировка соединения отличается от кодировки таблиц - будут знаки вопросов

Приводим таблицу к общей кодировке:

-- сначала резервная копия: операция переписывает данные всей таблицы
ALTER TABLE vendor_orders CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- целевую кодировку берут ту же, что у таблиц платформы в этой базе

Конвертация переписывает данные и на больших таблицах идёт долго. Её планируют на спокойное время и обязательно после резервной копии: на середине операции откатиться уже нечем.

Причины

  1. Своя таблица создана с чужой кодировкой примерно 35% случаев

    ПризнакОшибка приходит только на запросах, где своя таблица соединяется с таблицей платформы.

    ПроверкаСравниваем кодировку своей таблицы с кодировкой любой таблицы платформы в этой же базе.

    Что делатьПриводим свою таблицу к общей кодировке и добавляем указание кодировки в скрипт её создания.

  2. База сконвертирована не целиком примерно 25% случаев

    ПризнакРазные кодировки у разных таблиц, ошибка появляется в случайных местах админки.

    ПроверкаСчитаем таблицы по кодировкам одним запросом: в ответе больше одной строки.

    Что делатьДоводим конвертацию до конца по всем таблицам, начиная с самых мелких и с резервной копией.

  3. Кодировка соединения не та примерно 20% случаев

    ПризнакВместо русских букв знаки вопросов или ромбики, при этом ошибок в журнале нет.

    ПроверкаСмотрим переменные кодировки соединения и настройку подключения в файле настроек платформы.

    Что делатьПриводим кодировку соединения к кодировке таблиц; правку делают в настройках подключения, а не запросом.

  4. Свой запрос соединяет данные из разных источников примерно 12% случаев

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

    ПроверкаЧитаем текст запроса: сравнение или объединение строк из таблиц с разной кодировкой.

    Что делатьПриводим кодировку в самом запросе явным преобразованием либо, что надёжнее, чиним таблицу.

  5. Настройки сервера базы по умолчанию другие примерно 8% случаев

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

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

    Что делатьЗадаём кодировку по умолчанию в настройках сервера, чтобы новые таблицы создавались правильными.

Если ничего не помогло

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

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

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

Частые вопросы

Какую кодировку выбрать для базы?

Ту же, что уже используют таблицы платформы в этой базе: разнобой опаснее любого конкретного выбора. Проверить её можно на любой системной таблице, например на таблице пользователей.

Откуда берутся знаки вопросов вместо букв?

Из несовпадения кодировки соединения и кодировки таблиц: данные записываются с потерей символов. Это отдельная от ошибки сравнения беда, и лечится она настройками подключения, а не конвертацией таблиц.

Опасна ли конвертация таблиц?

Она переписывает данные целиком, поэтому делается после резервной копии и в спокойное время. На больших таблицах операция идёт долго и блокирует работу с ними.

Почему ошибка появилась после переноса на другой сервер?

У нового сервера базы другая кодировка по умолчанию, и таблицы, созданные уже на нём, получили её. Настройку сервера правят один раз, а созданные таблицы приводят к общей кодировке.

Можно ли обойти ошибку прямо в запросе?

Можно явным преобразованием кодировки в условии сравнения, но это заплатка на одном месте. Причина остаётся в структуре, и следующий такой запрос упадёт точно так же.

Смежное

Первоисточник