[MySQL] sql_mode=only_full_group_by 에러 해결 방법

여러 서비스의 MySQL을 사용하다 보면 MySQL Version(버전)을 이동하면서 사용하게 된다.

이때 5.6 Version / 5.7 Version 도 같이 사용되는 경우가 있거나, 혹은 서비스 데이터베이스(DB)가 5.6에서 5.7 Version으로 업데이트(Update) 하여 서비스하는 경우도 있다.  

근데 동일 GROUP BY 사용하는 Query(쿼리)를 5.6 Version에서는 정상적으로 실행 및 결괏값을 가지고 오나, 5.7 Version에서는 아래와 같은 에러(Error) 메시지가 발생되는 경우가 있다. 

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

분명 5.6 Version에서는 정상적으로 되던 쿼리가 5.7 Version에서는 에러가 발생된다.

관련해서 검색을 하였고, 그중 관련 내용을 자세하게 작성한 내용을 확인하였다.

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

간단히 이야기하면 5.7 Version에서는 sql_mode 항목이 생겼으며 그 옵션 안에 only_full_group_by 내용이 존재함에 따라 발생되는 부분이라고 설명되어 있다. 

그럼 해당 내용을 해결하는 방법을 알아보도록 하자.

1. Test DB Table & Data

Test를 하기 위한 Table 및 Data를 만들어서 해보도록 하자.

# Table Create
CREATE TABLE `tb_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `address` varchar(30) NOT NULL DEFAULT '',
  `age` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# Test Data
INSERT INTO `tb_test` (`id`, `name`, `address`, `age`)
VALUES
	(1,'Lee','Korea','18'),
	(2,'Lee','Korea','20'),
	(3,'Lee','USA','18'),
	(4,'Kim','Korea','20'),
	(5,'Park','Korea','20');

Test Table_Data.sql

0.00MB

 

2. Query(쿼리) 수정

에러가 발생한 Query를 수정하여 결괏값이 나오도록 하자.

# Error Query
SELECT name, address, Max(age) FROM tb_test GROUP BY name;

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drscan_dev.tb_test.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ANY_VALUE() 함수를 사용하여 정상적으로 Query의 결괏값을 받을 수 있다. 

다만, 해당 예제는 매우 단순함에 따라 더 복잡한 Query에서는 동일한 결과가 나오지 않을 수 있다. 

그럴 경우 Sub Query(서브 쿼리)를 사용하거나 3번과 같이 sql_mode를 변경하여 사용하도록 하자.  

# 수정된 Query
SELECT name, ANY_VALUE(address) AS address, Max(age) FROM tb_test GROUP BY name;


name	address	age
Kim	Korea	20
Lee	Korea	20
Park	Korea	20

 

 

3. sql_mode 변경

우선 5.7 Version에서 sql_mode가 사용 중인지 확인하는 방법을 알아보도록 하자.

# sql_mode 확인 Query
SELECT @@sql_mode;


@@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

해당 결과값에 "ONLY_FULL_GROUP_BY" 있다면 위와 같은 에러가 발생된다. 

해당 sql_mode 항목에서 위 옵션을 제거하는 방법은 영구적으로 제거하는 방법과 현재 연결되어있는 Session(세션)에서만 SQL Mode를 설정하는 방법이 있다. 

3.1 현재 연결된 Session(세션) 제거

단순히 Query 테스트만을 위해서 현재 연결된 Session에서만 sql_mode 항목을 변경하기 위해서는 아래와 같이 사용하지 않는 "ONLY_FULL_GROUP_BY"를 제거하여 설정하면 된다.

# 현재 연결된 Session 설정
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';

기본적으로 sql_mode에 설정되어 있는 옵션은 아래와 같다.

• ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

3.2 영구적 제거

영구적으로 제거하기 위해서는 해당 MySQL를 사용하는 시스템에서 "my.cnf" 파일에 수정 후 재기동을 통해서 제거할 수 있다.

# 영구적 제거 설정
$ vi /etc/my.cnf
...
[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
...