Sql error code 1055

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

Solve Query Failures Regarding ONLY_FULL_GROUP_BY SQL Mode

“Hey, what’s going on with my applications? I installed a newer version of MySQL. I have queries that perfectly run with the older version and now I have a lot of errors.”

This is a question some customers have asked me after upgrading MySQL. In this article, we’ll see what one of the most frequent causes of this issue is, and how to solve it.

We are talking about this error:

Have you ever seen it?

SQL_MODE

As the first thing let me introduce the concept of SQL_MODE.

MySQL can work using different SQL modes that affect the syntax of the queries and validation checks. Based on the configured value of the variable sql_mode, it means that a query can be valid and regularly executes or can receive a validation error and cannot be executed.

The oldest versions of MySQL got users accustomed to writing queries that were not semantically correct because it was designed to work in the “forgiving mode”. Users could write any kind of syntactically valid query regardless of SQL standard compliance or semantic rules. This was a bad habit that was corrected introducing the sql_mode to instruct MySQL to work in a more restrictive way for query validation.

Some users are not aware of this feature because the default value was not so restrictive. Starting from 5.7, the default value is more restrictive and this the reason why some users have problems with unexpected query failures after migration to 5.7 or 8.0.

The sql_mode variable can be set in the configuration file (/etc/my.cnf) or can be changed at runtime. The scope of the variable can be GLOBAL and SESSION, so it can change by the purpose of the mode for any single connection.

The sql_mode variable can have more values, separated by a comma, to control different behaviors. For example, you can instruct MySQL how to deal with dates with zeros as ‘0000-00-00’, to ensure the date be considered as valid or not. In the “forgiving mode” (or if sql_mode variable is empty) you can INSERT such a value without problems.

Источник

MySQL
Ошибка 1055: ONLY_FULL_GROUP_BY: что-то не в условии GROUP BY .

Вступление

замечания

В течение долгого времени MySQL содержал печально известное нестандартное расширение GROUP BY , которое позволяет использовать поведение oddball во имя эффективности. Это расширение позволило бесчисленным разработчикам по всему миру использовать GROUP BY в производственном коде без полного понимания того, что они делают.

В частности, это плохая идея использовать SELECT * в запросе GROUP BY , потому что стандартное предложение GROUP BY требует перечисления столбцов. К сожалению, многие разработчики этого сделали.

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

Если вы недавно начали получать 1055 ошибок, каковы ваши варианты?

1. исправить оскорбительные SQL-запросы или заставить их авторов сделать это.
2. перейдите к версии совместимого с MySQL совместимого программного обеспечения, которое вы используете.
3. измените sql_mode вашего сервера, чтобы избавиться от нового режима ONLY_FULL_GROUP_BY .

Вы можете изменить режим, выполнив команду SET .

должен сделать трюк, если вы сделаете это сразу после того, как ваше приложение подключится к MySQL.

Или вы можете найти файл инициализации в вашей установке MySQL , найти sql_mode= и изменить его, чтобы опустить ONLY_FULL_GROUP_BY , и перезагрузить сервер.

Использование и неправильное использование GROUP BY

покажет строки в таблице, называемой 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 , как это.

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

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

покажет строки в таблице, называемой 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 .

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

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

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

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

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

ANY_VALUE ()

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

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

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

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

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

Источник

How to solve MySQL Syntax error or access violation: 1055 Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column which is not functionally dependent on columns in GROUP BY clause

Carlos Delgado

Learn how to solve the MySQL error 1055 caused by the usage of the sql mode only_full_group_by.

A couple of weeks ago working in a legacy project that needed to be upgraded and moved to a new server got me in the next exception in a couple of modules of the application:

Expression of SELECT list is not in GROUP BY clause and contains nonaggregated column this is incompatible with sql_mode=only_full_group_by.

According to the MySQL documentation, having the only full group by mode in the sql mode will reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

In some cases, as the standard query won’t work with a default configuration in the mysql client, you may like as a good practice to rewrite it in a way that the exception doesn’t appear anymore, however due to time and costs of development, you will need a faster and not so expensive solution. The point of this solution is that the query worked in an older version of MySQL with different settings, so you may run the same queries by simply changing the sql mode of your MySQL client and we’ll show you how to do it in this short article.

1. Locate MySQL my.cnf file

The first thing that you need to do is to find the configuration file of MySQL. For example with many distribution as Plesk, you can find the file at /etc/mysql/my.cnf or at /etc/my.cfn , however this may vary, so you will be able to find it using a linux command like:

This will output the path to the files with such name. Once you find the file, you will be able to change the setting that will remove the exception from appearing.

2. Modify sql mode

Inside the [mysqld] setting block you need to update the value of the sql_mode property to an empty string which will remove the ‘only_full_group_by’ mode:

Edit the my.cnf file using either nano, vi, emacs or via SFTP and save the changes. For example, the my.cnf file would end up like this with our new setting:

Save changes in the file and restart mysql with the cli depending of your os and installation process e.g:

Now the queries with the issue should be able to run now and the exception won’t appear anymore.

Источник

Код ошибки: 1055 несовместим с sql_mode = only_full_group_by

У меня возникли проблемы с переключением на автономную версию бейсбольной базы данных Lahman SQL. Я использовал терминал, встроенный в курс EDX. Эта команда отлично работает в веб-терминале:

На котором работает SQL 5.5.46, но когда я использую автономную версию с 5.7.10, я получаю следующий код ошибки:

Код ошибки: 1055. Выражение № 1 списка SELECT отсутствует в предложении GROUP BY и содержит неагрегированный столбец stats.m.nameFirst, который функционально не зависит от столбцов в предложении GROUP BY; это несовместимо с sql_mode = only_full_group_by

Я читал много решений проблем людей, но в данном случае они не помогли. Такого никогда не случалось раньше, поэтому я думаю, что это либо очень очевидно, либо, может быть, у меня все в порядке с кодированием. Во всяком случае, кто-нибудь знает, как это исправить?

6 ответов

В 5.7 sqlmode по умолчанию установлен на:

Чтобы удалить предложение ONLY_FULL_GROUP_BY, вы можете сделать это:

Это предполагало, что вам нужно сделать эту GROUP BY с неагрегированными столбцами.

Вы можете установить переменные в mysql:

Помните, что NO_AUTO_CREATE_USER не будет работать с mysql 8.

Если это не сработает, просто сделайте:

Если вы сделаете так, как выбранный ответ, @sql_mode может быть таким:

Перед строкой STRICT_TRANS_TABLES стоит запятая.

Просто выполните это —

Также вы можете попробовать следующий опыт,

Я не тестировал, но думаю, может сработать.

Для других случаев использования: отключать ONLY_FULL_GROUP_BY необязательно. В подобном случае, согласно документации mysql, «Этот запрос недействителен, если имя не является первичным ключом t или уникальным столбцом NOT NULL. В этом случае нельзя сделать вывод о функциональной зависимости и возникает ошибка:»

Вместо этого вы можете использовать это ANY_VALUE(‘my_column_name’) my_column_name Цитируя документы mysql: «В этом случае MySQL игнорирует недетерминизм значений адресов в каждой группе имен и принимает запрос». Используйте ANY_VALUE () для ссылки на адрес:

Принятое выше решение не помогло мне в версии 5.7.9, for osx10.9 (x86_64) .

Источник

Русские Блоги

MySQL Error 1055

ERROR 1055:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘skynet_db.crawler_goods_info.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

причина

После MySQL 5.7 only_full_group_by включен в sql_mode по умолчанию, и оператор SQL не проходит семантическую проверку ONLY_FULL_GROUP_BY, поэтому выдается сообщение об ошибке.

Решение

Просмотрите команду параметра sql_model:

временный план:

Повторно открыть соединение mysql

Постоянный план:

Windows

Отредактируйте my.ini, измените и удалите only_full_group_by после sql_mode и перезапустите службу mysql после добавления

Под Linux

Добавьте следующее содержимое в конфигурационный файл, не забывайте, что он находится в [mysqld], а не в [mysqld_safe]. После того, как я ступил на яму, я подумал, что любое добавление вступит в силу. После добавления перезапустите службу mysql

Интеллектуальная рекомендация

Реализация оценки приложения iOS

Есть два способа получить оценку приложения: перейти в App Store для оценки и оценка в приложении. 1. Перейдите в App Store, чтобы оценить ps: appid можно запросить в iTunes Connect 2. Встроенная оцен.

JS функциональное программирование (е)

Давайте рассмотрим простой пример, чтобы проиллюстрировать, как используется Reduce. Первый параметр Reduce — это то, что мы принимаем массив arrayOfNums, а второй параметр — функцию. Эта функция прин.

PWN_JarvisOJ_Level1

Nc первый Затем мы смотрим на декомпиляцию ida Перед «Hello, World! N» есть уязвимая_функция, проверьте эту функцию после ввода Видно, что только что появившийся странный адрес является пе.

Установка и развертывание Kubernetes

На самом деле, я опубликовал статью в этом разделе давным -давно, но она не достаточно подробно, и уровень не является ясным. Когда я развернулся сегодня, я увидел его достаточно (хотя это было успешн.

На стороне многопроцессорного сервера — (2) *

Обработка сигнала Родительский процесс часто очень занят, поэтому вы не можете просто вызвать функцию waitpid, чтобы дождаться завершения дочернего процесса. Затем обсудите решение. Обратитесь .

Источник

Вступление

Недавно новые версии серверов 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. Формально он возвращает непредсказуемое значение.

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

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

Introduction

Recently, new versions of MySQL servers have begun to generate 1055 errors for queries that used to work. This topic explains those errors. The MySQL team has been working to retire the nonstandard extension to GROUP BY, or at least to make it harder for query writing developers to be burned by it.

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'

Using and misusing GROUP BY

 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

will show the rows in a table called item, and show the count of related rows in a table called uses. This works well, but unfortunately it’s not standard SQL-92.

Why not? because the SELECT clause (and the ORDER BY clause) in GROUP BY queries must contain columns that are

1. mentioned in the GROUP BY clause, or
2. aggregate functions such as COUNT(), MIN(), and the like.

This example’s SELECT clause mentions item.name, a column that does not meet either of those criteria. MySQL 5.6 and earlier will reject this query if the SQL mode contains ONLY_FULL_GROUP_BY.

This example query can be made to comply with the SQL-92 standard by changing the GROUP BY clause, like this.

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

The later SQL-99 standard allows a SELECT statement to omit unaggregated columns from the group key if the DBMS can prove a functional dependence between them and the group key columns. Because item.name is functionally dependent on item.item_id, the initial example is valid SQL-99. MySQL gained a functional dependence prover in version 5.7. The original example works under ONLY_FULL_GROUP_BY.

Misusing GROUP BY to return unpredictable results: Murphy’s Law

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

will show the rows in a table called item, and show the count of related rows in a table called uses. It will also show the value of a column called uses.category.

This query works in MySQL (before the ONLY_FULL_GROUP_BY flag appeared). It uses MySQL’s nonstandard extension to GROUP BY.

But the query has a problem: if several rows in the uses table match the ON condition in the JOIN clause, MySQL returns the category column from just one of those rows. Which row? The writer of the query, and the user of the application, doesn’t get to know that in advance. Formally speaking, it’s unpredictable: MySQL can return any value it wants.

Unpredictable is like random, with one significant difference. One might expect a random choice to change from time to time. Therefore, if a choice were random, you might detect it during debugging or testing. The unpredictable result is worse: MySQL returns the same result each time you use the query, until it doesn’t. Sometimes it’s a new version of the MySQL server that causes a different result. Sometimes it’s a growing table causing the problem. What can go wrong, will go wrong, and when you don’t expect it. That’s called Murphy’s Law.

The MySQL team has been working to make it harder for developers to make this mistake. Newer versions of MySQL in the 5.7 sequence have a sql_mode flag called ONLY_FULL_GROUP_BY. When that flag is set, the MySQL server returns the 1055 error and refuses to run this kind of query.

Misusing GROUP BY with SELECT *, and how to fix it.

Sometimes a query looks like this, with a * in the SELECT clause.

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

Such a query needs to be refactored to comply with the ONLY_FULL_GROUP_BY standard.

To do this, we need a subquery that uses GROUP BY correctly to return the number_of_uses value for each item_id. This subquery is short and sweet, because it only needs to look at the uses table.

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

Then, we can join that subquery with the item table.

 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

This allows the GROUP BY clause to be simple and correct, and also allows us to use the * specifier.

Note: nevertheless, wise developers avoid using the * specifier in any case. It’s usually better to list the columns you want in a query.

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

shows the rows in a table called item, the count of related rows, and one of the values in the related table called uses.

You can think of this ANY_VALUE() function as a strange a kind of aggregate function. Instead of returning a count, sum, or maximum, it instructs the MySQL server to choose, arbitrarily, one value from the group in question. It’s a way of working around Error 1055.

Be careful when using ANY_VALUE() in queries in production applications.

It really should be called SURPRISE_ME(). It returns the value of some row in the GROUP BY group. Which row it returns is indeterminate. That means it’s entirely up to the MySQL server. Formally, it returns an unpredictable value.

The server doesn’t choose a random value, it’s worse than that. It returns the same value every time you run the query, until it doesn’t. It can change, or not, when a table grows or shrinks, or when the server has more or less RAM, or when the server version changes, or when Mars is in retrograde (whatever that means), or for no reason at all.

You have been warned.

“Hey, what’s going on with my applications? I installed a newer version of MySQL. I have queries that perfectly run with the older version and now I have a lot of errors.”

This is a question some customers have asked me after upgrading MySQL. In this article, we’ll see what one of the most frequent causes of this issue is, and how to solve it.

We are talking about this error:

Have you ever seen it?

SQL_MODE

As the first thing let me introduce the concept of SQL_MODE.

MySQL can work using different SQL modes that affect the syntax of the queries and validation checks. Based on the configured value of the variable sql_mode, it means that a query can be valid and regularly executes or can receive a validation error and cannot be executed.

The oldest versions of MySQL got users accustomed to writing queries that were not semantically correct because it was designed to work in the “forgiving mode”. Users could write any kind of syntactically valid query regardless of SQL standard compliance or semantic rules. This was a bad habit that was corrected introducing the sql_mode to instruct MySQL to work in a more restrictive way for query validation.

Some users are not aware of this feature because the default value was not so restrictive. Starting from 5.7, the default value is more restrictive and this the reason why some users have problems with unexpected query failures after migration to 5.7 or 8.0.

The sql_mode variable can be set in the configuration file (/etc/my.cnf) or can be changed at runtime. The scope of the variable can be GLOBAL and SESSION, so it can change by the purpose of the mode for any single connection.

The sql_mode variable can have more values, separated by a comma, to control different behaviors. For example, you can instruct MySQL how to deal with dates with zeros as ‘0000-00-00’, to ensure the date be considered as valid or not. In the “forgiving mode” (or if sql_mode variable is empty) you can INSERT such a value without problems.

But this is not the correct behavior as stated by the TRADITIONAL mode. As good programmers know, you have to validate dates into your source code in order to avoid to have incorrect data or incorrect results.

The following is how you can dynamically instruct MySQL to behave in the traditional mode to throw an error instead:

There are many other modes you can use. Covering all the modes is not the goal of the article, so please refer to the official documentation for more details and examples:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

The ONLY_FULL_GROUP_BY issue

Let’s focus on the most frequent cause of errors when migrating to 5.7 or 8.0. As we said, 5.7 has a default SQL mode that is more restrictive than 5.6, and as such it’s for 8.0. This is true when you upgrade MySQL copying the old my.cnf file that doesn’t have a specific setting for the sql_mode variable. So, be aware.

Let’s create a sample table to store the clicks on the webpages of our site. We would like to log the page name and the id of the registered user.

Now we want to issue a query to calculate the most visited pages.

The query works, but it’s not really correct. It is easily understandable that page_url is the column of the grouping function, the value we are most interested in and we want to be unique for counting. Also, the visits column is good, as it’s the counter. But what about user_id? What does this column represent? We grouped on the page_url so the value returned for user_id is just one of the values in the group. In fact, it was not only user number1 to visit the index.html, but even users 2 and 3 visited the page. How can I consider that value? Is it the first visitor? Is it the last one?

We don’t know the right answer! We should consider the user_id column’s value as a random item of the group.

Anyway, the right answer is that the query is not semantically correct, because it has no meaning to return a value from a column that is not part of the grouping function. Then the query is expected to be invalid in the traditional sql.

Let’s test it.

Now we have an error, as expected.

The SQL mode ONLY_FULL_GROUP_BY is part of the TRADITIONAL mode and it is enabled by default starting from 5.7.

A lot of customers had this kind of issue after migration to a recent version of MySQL.

Now we know what the cause of the issue is, but our applications are still not working. What possible solutions do we have to let the applications work again?

Solution 1 – rewrite the query

Since it’s not correct to select a column that is not part of the grouping, we can rewrite the query without those columns. Very simple.

If you have a lot of queries affected by the problem, you have to potentially do a lot of work to retrieve and rewrite them. Or maybe the queries can be part of a legacy application you are not able or you don’t want to touch.

But this solution is the one that forces you to write correct queries and let your database configuration be restrictive in term of SQL validation.

Solution 2 – step back to the forgiving mode

You can change MySQL’s configuration and step back to the “forgiving” mode.

Or you can only drop the ONLY_FULL_GROUP_BY from the default. The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRINCT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER

Solution 3 – use of aggregation functions

If your application absolutely needs to retrieve the user_id field for some valid reason, or it’s too complicated to change your source code,  you can rely on an aggregation function in order to avoid changing the sql mode configuration.

For example we can use MAX(), MIN() or even GROUP_CONCAT() aggregation functions.

MySQL provides even a specific function for solving the problem: ANY_VALUE().

Conclusion

I personally prefer solution number 1 because it forces you to write SQL-92 compliant queries. Following the standards is often considered a best practice.

Solution 2 is good in case you cannot change your application code or if rewriting all the queries is really too complicated. The solution is very good to solve the issues in a matter of seconds, however, I strongly suggest to have a long term plan to rewrite the queries that are not SQL-92 compliant.

For more details: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Photo of sign by Ken Treloar on Unsplash

Learn more about Percona Server for MySQL