SQL_MODE не работает на MySQL 5.7.18

Question

SQL_MODE не работает на MySQL 5.7.18

Я получаю следующую ошибку из моего приложения PHP:

Неверный SQL-запрос (база данных) В агрегированном запросе без GROUP BY выражение # 1 списка SELECT содержит неагрегированный столбец ‘db_20172201.cm_order.buyer’; это несовместимо с sql_mode = only_full_group_by

Посмотрев некоторое время и попытавшись определить запрос, выдавший эту ошибку, я решил изменить параметр SQL_MODE на моем сервере MySQL.

Я сделал 2 вещи для достижения этой цели:

выполнил этот запрос

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

Добавлено следующее в my.cnf который является файлом конфигурации для MySQL

[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 в моем окружении по этому запросу

SELECT @@GLOBAL.sql_mode

это показывает значения, вставленные выше.

Я не знаю, где еще искать. Я также пытался установить переменную sql_mode из своего приложения перед выполнением запроса, но не решил.

— ОБНОВИТЬ

Я только что напечатал в своем приложении результат запроса SELECT @@SESSION.SQL_MODE и получается так, как я это установил:

array(1) { [0]=> object(stdClass)#5 (1)
{
["@@SESSION.sql_mode"] => string(118) "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"}
}

Но я все еще получаю ошибку

PS. Версия MySQL 5.7.18

0

apache2 mysql php sql-mode ubuntu

Решение

Другие решения

Других решений пока нет …

Источник

Accepted Answer

Хранимые программы — процедуры, функции, триггеры и события — имеют некоторые специальные механизмы, которые многие пользователи никогда не замечают, потому что они обычно «просто работают».

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

SHOW CREATE PROCEDURE ... показывает вам эти значения.

Один из них является @@SQL_MODE,

Это позволяет определителю процедуры гарантировать, что среда, находящаяся в месте выполнения процедуры, всегда имеет эти атрибуты.

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

Но … атрибуты здесь на самом деле хранятся в mysql.proc Таблица. Таким образом, у вас есть потенциальное «быстрое решение» здесь, если вы помните следующее:

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

https://dev.mysql.com/doc/refman/5.7/en/stored-routines-privileges.html

Если эта система не критична и у вас есть резервные копии, вы можете осторожно манипулировать строками таблицы mysql.proc, чтобы изменить sql_mode, а затем перезапустить процесс сервера MySQL, чтобы изменения в таблице были приняты во внимание.

Обратите внимание, что другой, казалось бы, жизнеспособный вариант — создание файла дампа с mysqldump и перезагрузить его — не будет иметь никакого эффекта, так как mysqldump добавляет операторы в файл дампа, чтобы гарантировать, что процедуры будут восстановлены с теми же настройками, которые существовали в процедурах при создании файла дампа, поэтому изменения в глобальной конфигурации сервера не будут иметь никакого эффекта.

Конечно, вы можете изменить файлы дампа, чтобы изменить эти значения, но это довольно сложная операция.

Если вы загрузили эту схему из файла, который содержал операторы SQL, но явно не устанавливал sql_mode, то перезагрузка файла также должна дать желаемый эффект.

Наконец, обратите внимание, что ONLY_FULL_GROUP_BY пытается защитить вас от запросов, которые используют группу MySQL с помощью расширений способом, который не согласуется с целью этой функции. Начиная с 5.7.x, запрос, который отклоняется ONLY_FULL_GROUP_BY на самом деле может приводить к недетерминированным результатам или может использовать преимущества побочных эффектов или результатов, которые являются правильными по случайности или совпадению, а не по замыслу, и такое поведение может исчезнуть в будущем выпуске (например, из-за изменений в оптимизаторе запросов) и, следовательно, не следует полагаться.

Лучшее решение — сделать запросы теоретически правильными.

1