[MySQL] ONLY_FULL_GROUP_BY 에러

데이터를 가져오기 위해서 GROUP BY를 사용하는 Query에서 아래와 같은 에러가 발생했다.

 

"Expression #20 of SELECT list is not in GROUP BY clause and contains nonaggregated column '컬럼명' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

 

검색을 해보니 MySQL 5.7 Version 부터 SQL_MODE가 생겼다고 한다. (참고)

SQL_MODE는 MySQL에 저장될 데이터에 대한 유효성 검사(validation check) 범위를 설정하는 시스템 변수이다.

 

에러는 SQL_MODE의 옵션 중 하나인 ONLY_FULL_GROUP_BY와 관련된 것이었다.

MySQL 8.0에서는 기본적으로 ONLY_FULL_GROUP_BY 옵션이 활성화되어 있다고 하는데, OS의 차이인지 내 개발환경에는 적용되어 있지 않았다. (아마 Windows OS여서 그런 것이 아닐까 추측)

그래서 로컬에서는 제대로 돌아가던 기능이 서버에 올렸을 때 ONLY_FULL_GROUP_BY 옵션에 걸려서 먹통이 된 것이다.

개발환경을 통일하기 위해 SQL_MODE의 옵션을 추가하고 쿼리를 수정해서 문제를 해결했다.

 

 

 

쿼리 수정하기

GROUP BY절을 사용할 때 SELECT 해와야 하는 컬럼을 모두 나열해주어야 한다. GROUP BY에 선언되지 않은 컬럼을 가져오려고 하면 오류가 나는 것이라고 한다.

 

SELECT name, ANY_VALUE(email), Max(age) 
FROM MEMBER
GROUP BY name;

 

GROUP BY로 묶어주지 않는 컬럼에 ANY_VALUE() 함수를 사용하여 쿼리하면 정상적으로 동작하는 것을 확인할 수 있다.

 

 

 

SQL_MODE 옵션 추가하기

현재 적용되어 있는 SQL_MODE를 조회

 

MySQL 서버에 접속(mysql -u root -p) 후 SELECT @@SQL_MODE; 명령어로 현재 적용되어 있는 SQL_MODE 옵션을 조회한다. 

 

SELECT @@SQL_MODE;

 

SQL_MODE를 영구적으로 변경하고 싶다면 my.ini (Windows OS) 파일에 SQL_MODE 속성을 추가해줘야 한다.

 

** my.ini 파일의 경로를 찾는 방법

1. 실행창(윈도우키 + R)에서 services.msc 명령어를 통해 서비스창 열기

2. MySQL80의 속성에서 실행파일 경로를 확인 (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini)

 

 

 

 

설정 추가하기

my.ini 파일에서 [mysqld] 구간에 sql_mode 속성의 필요한 옵션을 추가해준다.

 

sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

 

** my.ini 파일의 수정 권한 변경하기

권한이 없어 파일을 수정할 수 없다는 알림창이 뜨는 경우가 있다. 그럴 때는 사용 권한을 변경해주어야 한다.

my.ini 오른쪽 클릭-속성-보안 탭에서 사용자의 권한을 허용해주면 수정이 가능해진다.

 

 

 

참고
https://info-lab.tistory.com/274

https://velog.io/@heumheum2/ONLYFULLGROUPBY

https://blog.jiniworld.me/64