“Что случилось с моим приложением? Я установил новую версию MySQL. Запросы, что выполнялись на старой версии теперь падают с кучей ошибок.”
Многие программисты сталкиваются с этим вопросом при обновлении до версий 5.7 или 8. В этой статье мы рассмотрим один из самых частых кейсов и его решение.
Мы говорим об этой ошибке
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
Видели ли вы когда-либо её?
SQL_MODE
Для начала разрешите мне представить концепцию SQL_MODE.
MySQL может работать используя разные SQL режимы, которые влияют на синтаксис запросов и валидацию. В зависимости от установленного значения переменной sql_mode запрос может быть валидным и выполняться или может получить ошибку валидации и не может быть выполнен.
Старейшие версии MySQL научили пользователей писать запросы, которые семантически корректны, потому что разработаны для работы в “прощающем режиме”. Пользователи могли писать любой синтаксически правильный запрос независимо от соответствия SQL стандарту или сематических правил.
Это была плохая привычка, которая была исправлена введением sql_mode, чтобы настроить MySQL работать более строгим способом для проверки запросов.
Некоторые пользователи не знают об этой функции, потому что значение по умолчанию не было таким строгим. Начиная с версии 5.7, значение по умолчанию является более строгим, и по этой причине у некоторых пользователей возникают проблемы с неожиданными ошибками запросов после перехода на 5.7 или 8.0.
Переменная sql_mode может быть установлена в файле конфигурации (/etc/my.cnf) или может быть изменена во время выполнения. Область действия переменной может быть GLOBAL или SESSION, поэтому может измениться в соответствии с целью для любого отдельного соединения.
Переменная sql_mode может иметь несколько значений, разделённых запятой, для настройки различных поведений.
Например, вы можете проинструктировать MySQL как обращаться с датами с нулями, как 0000-00-00,
чтобы дата считалась действительной или нет.
В “прощающем режиме” (или если переменная sql_mode пуста), вы можете вставить такое значение без проблем.
# установка sql в "прощающий режим"
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1( mydate date );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------------+
| mydate |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)
Но это не правильное поведение, как заявлено в режиме TRADITIONAL. Как хорошие программисты знают, что нужно проверять даты в коде приложения, чтобы избежать некорректных данных или некорректных результатов.
Далее мы показываем, как динамически проинструктировать MySQL вести себя в traditional режиме, чтобы выбросить исключений вместо замалчивания ошибки:
mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'mydate' at row 1
Существует множество режимов, которые вы можете использовать. Покрытие всех режимов - не цель данной статьи, поэтому обратитесь к официальной документации за подробностями и примерами.
Проблема ONLY_FULL_GROUP_BY
Давайте сосредоточимся на самом частом кейсе ошибок миграции с 5.7 на 8.0. Как уже было сказано, в 5.7 более строгий режим, чем в 5.6, в 8.0 более строгий, чем в 5.7.
Это работает, если вы обновляете MySQL, копируя старый файл my.cnf, который не имеет специфичных настроек для переменной sql_mode. Итак, имейте в виду.
Давайте создадим простую таблицу для хранения кликов на вебстраницах нашего сайта. Мы будем записывать название страницы и ID зарегистрированного пользователя.
mysql> create table web_log ( id int auto_increment primary key, page_url varchar(100), user_id int, ts timestamp);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into web_log(page_url,user_id,ts) values('/index.html',1,'2019-04-17 12:21:32'),
-> ('/index.html',2,'2019-04-17 12:21:35'),('/news.php',1,'2019-04-17 12:22:11'),('/store_offers.php',3,'2019-04-17 12:22:41'),
-> ('/store_offers.php',2,'2019-04-17 12:23:04'),('/faq.html',1,'2019-04-17 12:23:22'),('/index.html',3,'2019-04-17 12:32:25'),
-> ('/news.php',2,'2019-04-17 12:32:38');
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from web_log;
+----+--------------------+---------+---------------------+
| id | page_url | user_id | ts |
+----+--------------------+---------+---------------------+
| 1 | /index.html | 1 | 2019-04-17 12:21:32 |
| 2 | /index.html | 2 | 2019-04-17 12:21:35 |
| 3 | /news.php | 1 | 2019-04-17 12:22:11 |
| 4 | /store_offers.php | 3 | 2019-04-17 12:22:41 |
| 5 | /store_offers.html | 2 | 2019-04-17 12:23:04 |
| 6 | /faq.html | 1 | 2019-04-17 12:23:22 |
| 7 | /index.html | 3 | 2019-04-17 12:32:25 |
| 8 | /news.php | 2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+
Теперь мы хотим написать запрос для подсчёта наиболее посещаемых страниц сайта
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT page_url, user_id, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+---------+--------+
| page_url | user_id | visits |
+-------------------+---------+--------+
| /index.html | 1 | 3 |
| /news.php | 1 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+---------+--------+
4 rows in set (0.00 sec)
Этот запрос работает, но на самом деле не корректен. Легко понять, что page_url - столбик для группировки, значение, которое нас больше всего интересует и мы хотим, чтобы оно было уникальным для подсчёта. Также столбик visits понятен, это счётчик. Но как насчёт user_id? Что представляет эта колонка? Мы сгруппировали по page_url, поэтому значение, возвращаемое для user_id - только одно из значений в группе. Фактически не только пользователь номер 1 посетил index.html, но также пользователи 2 и 3 посетили эту страницу. Как нам интерпретировать значение? Это первый посетитель? Или последний?
Мы не знаем правильного ответа! Мы должны рассматривать значение колонки user_id как случайный элемент из группы.
В любом случае, правильный ответ - запрос семантически некорректен, так как нет смысла для возвращаемого значения столбика, что не является частью функции группировки. Запрос будет недействительным в традиционном SQL.
Давайте проверим это
mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT page_url, user_id, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Как и ожидалось, у нас ошибка.
Режим SQL ONLY_FULL_GROUP_BY - это часть TRADITIONAL режима и включен по умолчанию начиная с 5.7.
Множество программистов столкнулось с разновидностью этой ошибки после миграции на последнюю версию MySQL.
Теперь мы знаем, что вызывает эту ошибку, но наше приложение всё ещё не работает. Какие возможные решения у нас есть, чтобы вернуть приложение к работе?
Решение 1 - переписать запрос
Так как не корректно выбирать колонку, которая не является частью группировки, мы можем переписать запрос без этой колонки. Очень просто.
mysql> SELECT page_url, COUNT(*) AS visits
-> FROM web_log
-> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url | visits |
+-------------------+--------+
| /index.html | 3 |
| /news.php | 2 |
| /store_offers.php | 2 |
| /faq.html | 1 |
+-------------------+--------+
Если много ваших запросов затронуты проблемой, вы можете потенциально сделать много работы, чтобы найти и переписать их. Или, возможно, проблемные запросы - часть старого приложения, которое нет возможности изменить.
Но это решение заставляет вас писать правильные запросы и пусть конфигурация вашей базы данных проверяет на такие ошибки в терминах SQL-валидации.
Решение 2 - вернуть “прощающий режим”
Вы можете поменять конфигурацию подключения или MySQL сервера и вернуть “прощающий” режим.
Или вы можете убрать только ONLY_FULL_GROUP_BY из настроек по умолчанию. По умолчанию SQL режим в 5.7 включает режимы: ONLY_FULL_GROUP_BY, STRINCT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER.
#set the complete "forgiving" mode
mysql> SET GLOBAL sql_mode='';
# alternatively you can set sql mode to the following
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';
Для yii2-приложения конфиг может выглядеть так:
<?php
return [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=' . getenv('MYSQL_HOST') . ';port=' . getenv('MYSQL_PORT') . ';dbname=' . getenv('MYSQL_DB'),
'username' => getenv('MYSQL_USER'),
'password' => getenv('MYSQL_PASSWORD'),
'charset' => 'utf8',
'attributes' => [
PDO::ATTR_PERSISTENT => true,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"'
],
];
Решение 3 - использование агрегирующих функций
Если ваше приложение точно нуждается в получении поля user_id для какой-то корректной причины, или слишком сложно менять исходный код (например, для поддержки обратной совместимости с внешними приложениями), вы можете положиться на агрегирующие функции, чтобы избежать изменения SQL-режима. Тогда для всех новых запросов проверка уже будет выполняться.
Например мы можем использовать агрегирующие функции MAX(), MIN() или даже GROUP_CONCAT().
mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
mysql> SELECT page_url, MAX(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------+--------+
| page_url | MAX(user_id) | visits |
+-------------------+--------------+--------+
| /index.html | 3 | 3 |
| /news.php | 2 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+--------------+--------+
mysql> SELECT page_url, GROUP_CONCAT(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+-----------------------+--------+
| page_url | GROUP_CONCAT(user_id) | visits |
+-------------------+-----------------------+--------+
| /index.html | 1,2,3 | 3 |
| /news.php | 1,2 | 2 |
| /store_offers.php | 3,2 | 2 |
| /faq.html | 1 | 1 |
+-------------------+-----------------------+--------+
MySQL даже предоставляет специальную функцию для решения этой проблемы: ANY_VALUE().
mysql> SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------------+--------+
| page_url | ANY_VALUE(user_id) | visits |
+-------------------+--------------------+--------+
| /index.html | 1 | 3 |
| /news.php | 1 | 2 |
| /store_offers.php | 3 | 2 |
| /faq.html | 1 | 1 |
+-------------------+--------------------+--------+
Заключение
Лично я предпочитаю решение номер 1, так как оно заставляет вас писать запросы по стандарту SQL-92. Следование стандартам часто считается лучшей практикой. Также хочу заметить, что это ловит часть ошибок, аналогично статическому анализу кода.
Решение 2 подходит, если вы не можете поменять код приложения или переписывание всех запросов действительно очень сложное. Отличное решение исправить проблему за несколько секунд, хотя я настоятельно рекомендую иметь план по переписыванию запросов, которые соответствуют стандарту SQL-92.
Больше деталей: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
По мотивам https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/