DeePark.ru

При переносе базы MySQL с одного хостинга на другoй sql_mode может приводить к ошибкам. Причём, не всегда их можно сразу обнаружить. Вот например у меня был один запрос в базу который не работал, а сделал я его давно и думать про него прекратил. Всё работает а он нет. Что-то типа

SELECT
s.id id,
s.name name,
count(s.name) count
FROM catalog_section s
JOIN catalog_section_treepath t ON (s.id = t.descendant)
GROUP BY s.name HAVING count=1;

Здесь работает, а здесь не работает! Так не бывает? Оказывается бывает.

А ведь давно отработан способ переноса базы данных и никаких проблем не возникало. Но мир удивителен и в нём не заскучаешь. Случилось так случилось… Всё прекрасно перенеслось и работает отлично, но один из модулей сайта уверенно показывает ошибку. Неверный синтаксис мол. А дословно:

CDbCommand не удалось исполнить SQL-запрос: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'data_base.s.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

И действительно sql_mode оказался не такой, какой был до переноса.

А как сделать что-бы был такой?

Заходим в базу в которой всё работает и смотрим какой нужен

show VARIABLES LIKE 'sql_mode';

Там у нас так:

Теперь заходим в свою базу и устанавливаем тот sql_mode который нужен.

(Тут есть опасность о которой написано в дополнительных материалах. Но мне не грозит, потому что партиционными таблицами я ещё ни разу не пользовался.)

SET sql_mode = 'STRICT_ТRANS_TABLES,NO_ENGINE_SUBSTITYTION'

Всё равно не работает. Ну тогда глобальную переменную тоже поправим:

SET GLOBAL sql_mode = 'STRICT_ТRANS_TABLES,NO_ENGINE_SUBSTITYTION'

Всё равно не работает. Ну тогда просто уберём ONLY_FULL_GROUP_BY, а всё остальное оставим как было.

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Ну заработало!

Всё? Нет. Допустим сервер перезагрузили и все настройки создались по новому, они стали такие, какие были до исправления. Получается, что всё что мы сделали нужно занести в какой-то конфигурационный файл, какой?

Здесь мы припомним статью про настройки LAMP и сделаем, как там написано

sudo vi /etc/mysql/conf.d/sql_mode_set.cnf

[mysqld]

sql-mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Следует заметить, что хотя ранее у нас не получилось настроить sql_mode так как в предыдущей базе, сейчас в файле конфигурации это сделать можно это будет работать, т.е. можно написать
sql-mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
Здесь мы получим именно такой sql_mode который хотим. Нужно только не забыть сделать
sudo service mysql restart

Так что же за ONLY_FULL_GROUP_BY? От которого мне пришлось отказаться только для того, чтобы работал какой-то древний запрос…

Смотрим документацию и видим.

ONLY_FULL_GROUP_BY
Генерирует ошибку в запросах, в которых GROUP BY имеет не полный список не агрегированных параметров из SELECT и HAVING.

Что мы и наблюдали!

А как же жить то без GROUP BY и HAVING?

Всего то нужно было сделать:

Дополнительные материалы:

Данная статья помогла прояснить ситуацию. https://habrahabr.ru/post/166411/

А оригинал можно почитать здесь: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html