Ошибки при переходе с MySQL 5.6 на 5.7 и как их исправить - импорт дампа БД завершился ошибкой или не работает INSERT. Отключение строгого режима STRICT_TRANS_TABLES или использование IGNORE

09.11.23 ИТ / Базы данных 2084

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

Строгий режим MySQL

Начиная с версии MySQL 5.7, вводится строгий режим MySQL (STRICT MODE), который включен по умолчанию. То есть, ранее он также был доступен, но его необходимо было включить вручную. Такое нововведение вызывает ошибки с написанным кодом — сайт или веб-приложение отказывается работать.

Вначале стоит проверить, действительно ли включен строгий режим в MySQL. Если присутствует значение STRICT_TRANS_TABLES в переменной sql_mode — строгий режим включен. Набираем в консоли и смотрим на результат, вот команда для этого: SHOW VARIABLES LIKE 'sql_mode'.

Ошибки при переходе с MySQL 5.6 на 5.7

Как проявляются ошибки при переходе с MySQL 5.6 на 5.7? Например, скорее всего в начале это было замечено на локальном хостинге. При смене версии MySQL — код переставал работать, к примеру:

• Импорт дампа базы в phpMyAdmin завершился с ошибкой. Потребовалось вручную отредактировать дамп, удалить строки с ошибками — например, данные были слишком большими;
• Не добавляются новые записи в БД, не работает команда INSERT. Это из-за того, что необходимо заполнять все поля в таблице, для которых не заданы значения по умолчанию. В нестрогом режиме такого требования нет.

Как исправить ошибки

Как исправить ошибки при переходе с MySQL 5.6 на 5.7? Можно выделить два сценария:

• Исправить сам код запросов или дамп, как было сказано выше;
• Выключить строгий режим MySQL или исправить таблицы в БД — например, задать все необходимые значения по умолчанию (DEFAULT).

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

• Пересмотреть структуру таблиц — задать все необходимые значения по умолчанию, проверить типы данных и длину вставляемых значений;
• Просмотреть весь код для работы с БД и произвести его рефакторинг — задать значения для всех полей без значений по умолчанию, проверить соответствия типов полей и добавляемых данных, а также их длину. Или использовать команду IGNORE для игнорирования ошибок в конкретном запросе;
• Для локального хостинга или если есть доступ к конфигурационным файлам: открыть конфигурационный файл MySQL и в секции [mysqld] снять все ограничения, написать sql-mode = "". Не забыть перезапустить сервер;
• Написать в начале своего скрипта код, который выполнит команду: SET GLOBAL sql_mode = "".

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