Код ошибки 1055

Вступление

Недавно новые версии серверов MySQL начали генерировать 1055 ошибок для запросов, которые раньше работали. В этом разделе объясняются эти ошибки. Команда MySQL работает над отказом от нестандартного расширения до GROUP BY или, по крайней мере, для того, чтобы затруднить его работу с разработчиками запросов.

замечания

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

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item , и покажет количество связанных строк в таблице с именем uses . Это хорошо работает, но, к сожалению, это не стандартный SQL-92.

Почему бы и нет? потому что предложение SELECT (и предложение ORDER BY ) в запросах GROUP BY должно содержать столбцы, которые

1. указанных в предложении GROUP BY , или
2. совокупные функции, такие как COUNT() , MIN() и тому подобное.

В предложении SELECT этого примера упоминается item.name , столбец, который не соответствует ни одному из этих критериев. MySQL 5.6 и ранее отклонят этот запрос, если режим SQL содержит ONLY_FULL_GROUP_BY .

Этот примерный запрос может быть выполнен в соответствии со стандартом SQL-92, изменив предложение GROUP BY , как это.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

Более поздний стандарт SQL-99 позволяет SELECT пропускать неагрегированные столбцы из группового ключа, если СУБД может доказать функциональную зависимость между ними и столбцами группового ключа. Поскольку item.name функционально зависит от item.item_id , исходный пример действителен SQL-99. MySQL получил версию функциональной зависимости в версии 5.7. Исходный пример работает под ONLY_FULL_GROUP_BY .

Неправильная GROUP BY возвращает непредсказуемые результаты: закон Мерфи

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

покажет строки в таблице, называемой item, и покажет количество связанных строк в таблице с именем uses. Он также покажет значение столбца, называемого uses.category .

Этот запрос работает в MySQL (до ONLY_FULL_GROUP_BY флажка ONLY_FULL_GROUP_BY ). Он использует нестандартное расширение MySQL для GROUP BY .

Но у запроса есть проблема: если несколько строк в таблице uses соответствуют условию ON в предложении JOIN , MySQL возвращает столбец category только из одной из этих строк. Какая строка? Писатель запроса и пользователь приложения не знают об этом заранее. Формально говоря, это непредсказуемо : MySQL может вернуть любое значение, которое он хочет.

Непредсказуемый случайный, с одним существенным различием. Можно ожидать случайного выбора время от времени. Поэтому, если выбор был случайным, вы можете обнаружить его во время отладки или тестирования. Непредсказуемый результат хуже: MySQL возвращает тот же результат каждый раз, когда вы используете запрос, пока он этого не сделает. Иногда это новая версия сервера MySQL, которая приводит к другому результату. Иногда это вызывает растущую таблицу, вызывающую проблему. Что может пойти не так, пойдет не так, и когда вы этого не ожидаете. Это называется законом Мерфи .

Команда MySQL работает над созданием этой ошибки для разработчиков. Более новые версии MySQL в 5,7 последовательности имеют sql_mode флаг под названием ONLY_FULL_GROUP_BY . Когда этот флаг установлен, сервер MySQL возвращает ошибку 1055 и отказывается запускать такой запрос.

Неправильная команда GROUP BY с SELECT *, и как ее исправить.

Иногда запрос выглядит так: * в предложении SELECT .

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

Такой запрос должен быть реорганизован для соответствия стандарту ONLY_FULL_GROUP_BY .

Для этого нам нужен подзапрос, который правильно использует GROUP BY чтобы вернуть значение number_of_uses для каждого item_id . Этот подзапрос короткий и сладкий, потому что ему нужно только посмотреть таблицу uses .

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

Затем мы можем присоединиться к этому подзапросу с помощью таблицы item .

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

Это позволяет сделать предложение GROUP BY простым и правильным, а также позволяет использовать спецификатор * .

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

ANY_VALUE ()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

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

Вы можете рассматривать эту ANY_VALUE() как странную своего рода совокупную функцию. Вместо того, чтобы возвращать счет, сумму или максимум, он инструктирует сервер MySQL выбирать произвольно одно значение из рассматриваемой группы. Это способ работы с ошибкой 1055.

Будьте внимательны при использовании ANY_VALUE() в запросах в производственных приложениях.

Его действительно следует называть SURPRISE_ME() . Он возвращает значение некоторой строки в группе GROUP BY. Какая строка возвращается, является неопределенной. Это означает, что это полностью зависит от сервера MySQL. Формально он возвращает непредсказуемое значение.

Сервер не выбирает случайное значение, это хуже, чем это. Он возвращает одно и то же значение каждый раз, когда вы запускаете запрос, пока он этого не сделает. Он может измениться или нет, когда таблица растет или сжимается, или когда сервер имеет больше или меньше ОЗУ, или когда версия сервера изменяется, или когда Марс находится в ретроградном (что бы это ни значило) или вообще без причины.

Вы были предупреждены.

SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants
FROM `pr_opportunity` as o
LEFT OUTER JOIN pr_internal_applicant as pia
ON o.id = pia.positionid
WHERE o.approval_status='Approved'
and DATE(o.deadline) > DATE(NOW())
GROUP BY o.id

function get_opportunity_list()
{
$sql = "SELECT *,o.id as id, GROUP_CONCAT(pia.applicantid) as applicants
FROM `".$this->myTables['opportunity']."` as o
LEFT OUTER JOIN pr_internal_applicant as pia
ON o.id = pia.positionid
WHERE o.approval_status='Approved'
and DATE(o.deadline) > DATE(NOW())
GROUP BY o.id";
$query=$this->db->query($sql);

if($query->num_rows() > 0){
$rows = $query->result();

}
return $rows;
}

Решение

vi /etc/mysql/my.cnf

[mysqld]
# ... other stuff will probably be here
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sudo service mysql restart

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

SELECT o.id as id, GROUP_CONCAT(pia.applicantid) as applicants
FROM `".$this->myTables['opportunity']."` as o
LEFT OUTER JOIN pr_internal_applicant as pia
ON o.id = pia.positionid
WHERE o.approval_status='Approved'
and DATE(o.deadline) > DATE(NOW())
GROUP BY o.id"

Explored the other options and came up with the below:

The following rules apply for the columns (other than the group by column(s)) to be used in select list:

Case 1: Select involving single table

1) group by col1 => col1 has to be unique & not null or the primary key column of the corresponding table

         select super_ssn from employee … group by lname => Error

         select super_ssn from employee … group by lname (if lname is unique not null) => Success

         select mgr_start_date from department … group by dname => Error

         select mgr_start_date from department … group by dname (if dname is unique not null) => Success

2) group by col1, col2 …  => One of col1, col2 … should be unique not null or primary key column of the corresponding table

         select super_ssn from employee … group by sex, lname => Error

         select super_ssn from employee … group by sex, lname (if lname is unique not null) => Success

         select mgr_start_date from department … group by mgr_ssn, dname => Error

         select mgr_start_date from department … group by mgr_ssn, dname (if dname is unique not null) => Success

Case 2: Select involving multiple tables

1) group by col1 => Only columns from the same table can be used in select list if col1 is unique & not null or the primary key column of the corresponding table

         select super_ssn from employee, department … group by ssn => Success

         select super_ssn from employee, department … group by dnumber => Error

         select mgr_start_date from employee, department … group by dnumber => Success

         select mgr_start_date from employee, department … group by ssn => Error

2) group by col1, col2 …  =>

     a) Only columns from the same table as one of col1, col2 … can be used in select list if col1/col2 … is unique & not null or primary key column of the corresponding table

         select super_ssn from employee, department … group by ssn, dname => Success

         select super_ssn from employee, department … group by lname, dname => Error

         select mgr_start_date from employee, department … group by dnumber, lname => Success

         select mgr_start_date from employee, department … group by dname, lname => Error

     b) Any column of tany able can be used if one of the group by colums is the primary of any table. This doesn’t apply to the group by primary key column is part of the join condition

         Ex 1: ssn even though primary key column of employee table, it can be used to select any column in any table

         select mgr_start_date from department, employee … group by ssn => Success

          Ex 2: Dnumber even though primary key column of department table, it can’t be used to select any column in any table since it is part of the join condition

          select super_ssn from department, employee … group by dnumber => Error

“Что случилось с моим приложением? Я установил новую версию 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/