ONLY_FULL_GROUP_BY SQL 쿼리 실패

MySQL을 업그레이드 한 후 정상적으로 수행되던 쿼리가 "ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause" 에러와 함께 수행이 되지 않는 경우가 있습니다.

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은 사용자가 "forgiving mode"에서 작동하도록 설계되었기 때문에 의미상 올바르지 않은 쿼리를 작성하는 데 익숙해졌습니다. 사용자는 SQL 표준 준수나 규칙에 관계없이 모든 종류의 구문적으로 유효한 쿼리를 작성할 수 있습니다. 이후 쿼리 유효성 검사를 위해 MySQL이 더 제한적인 방식으로 작동하도록 sql_mode를 도입하여 수정 되었습니다.

5.7부터는 기본값이 더 제한적이며 사용자가 5.7 또는 8.0으로 마이그레이션한 후 예기치 않은 쿼리 실패 문제가 발생하는 이유입니다.

sql_mode의 변수는 구성 파일 (/etc/my.cnf ) 또는 실시간으로 변경 할 수 있습니다. 변수의 범위는 GLOBAL 및 SESSION일 수 있으므로 단일 연결에 대한 목적에 따라 변경할 수 있습니다.

sql_mode의 변수 값을 더 추가 할 수 있고, 다른 동작을 제어하기 위해 콤마로 분리합니다. 예를 들어, 날짜가 유효한지 여부를 확인하기 위해 MySQL에 날짜에 zero인 날짜를 '0000-00-00'으로 처리하는 방법을 지시할 수 있습니다. "forgiving mode"에서(또는 sql_mode 변수가 비어 있는 경우) 문제 없이 이러한 값을 삽입할 수 있습니다.

# set sql mode to "forgiving mode" 
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

 

이 외에도 사용할 수 있는 다른 모드가 많이 있습니다. 모든 모드를 다루는 것이 목표는 아니므로 자세한 내용과 예는 공식 문서를 참조하십시오.

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

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

 

ONLY_FULL_GROUP_BY 문제

5.7 또는 8.0으로 마이그레이션할 때 가장 자주 발생하는 오류 원인에 초점을 맞춰 보겠습니다. 5.7에는 5.6보다 더 제한적인 기본 SQL 모드가 있습니다. 이는 sql_mode 변수에 대한 특정 설정 없이 이전 my.cnf 파일을 복사하여 MySQL을 업그레이드하는 경우에 해당됩니다.

웹 페이지에 대한 클릭을 저장하는 샘플 테이블을 만들어 보겠습니다. 

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 |
+----+--------------------+---------+---------------------+

 

이제 가장 많이 방문한 페이지를 계산하는 쿼리를 실행하려고 합니다.

# sql mode to "forgiving"
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 이 groupby 함수의 열이고 계산을 위해 고유한 값을 원한다는 것은 쉽게 이해할 수 있습니다. 또한 방문 열은 카운터입니다. 하지만 user_id는 열은 무엇을 나타냅니까? user_id 에 대해 반환 된 값이 그룹의 값 중 하나일 수 있도록 page_url로 그룹화했습니다 . 같은 페이지 url을 방문한 것은 1번 사용자뿐 아니라 2번, 3번 사용자도 방문했습니다. 

user_id 열의 값은 그룹의 임의 항목으로 간주해야 합니다 .

어쨌든 정답은 쿼리가 의미적으로 정확하지 않다는 것입니다. group by 함수의 일부가 아닌 열에서 값을 반환하는 의미가 없기 때문입니다. 그럼 다음 쿼리는 기존 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 - 쿼리 다시 작성

group by에 포함되지 않은 열을 조회하는 것은 올바르지 않으므로 해당 열을 제거하여 쿼리를 다시 작성할 수 있습니다.

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 - forgiving mode로 돌아가기

MySQL의 구성을 변경하고 "forgiving mode"로 돌아갈 수 있습니다.

또는 기본값 에서 ONLY_FULL_GROUP_BY 만 삭제할 수 있습니다 . MySQL 5.7의 기본 SQL 모드는 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';

 

해결 방법 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 |
+-------------------+--------------------+--------+

 

결론

SQL-92 호환 쿼리를 작성해야 하기 때문에 해결 방법 1을 선호합니다. 표준을 따르는 것은 종종 모범 사례로 간주됩니다.

해결 방법 2는 응용 프로그램 코드를 변경할 수 없는 경우에 좋습니다. 하지만, 궁극적인 문제를 해결 하기 위해 SQL-92 호환되지 않는 쿼리를 재 작성하는 장기적인 계획을 가지는 것이 좋습니다.