MySQL Only Full Group By 에러 관련

문제

MySQL 관련 DB 인수인계 중

 

this is incompatible with sql_mode=only_full_group_by

 

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

 

관련 에러가 발생하였습니다.

예시

Whitelabel Error Page
This application has no explicit mapping for /error, so you are seeing this as a fallback.

Mon Jul 12 10:50:27 KST 2021
There was an unexpected error (type=Internal Server Error, status=500).
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.I.idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ### The error may exist in file [D:\Work\XXX\XXXadmin\target\classes\kweather\sql\paid\paid_write_mysql.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT paid_name, (SELECT COUNT(*) FROM( SELECT I.idx FROM TB_XXX_PAID_PAY_INFO I GROUP BY I.paid_idx, I.pay_idx) O) AS count, price FROM TB_XXX_PAID_PAY_INFO A JOIN TB_XXX_PAID_MSTR B ON(A.paid_idx = B.idx) GROUP BY paid_idx ### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.I.idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.I.idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.I.idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in file [D:\Work\XXX\XXXadmin\target\classes\kweather\sql\paid\paid_write_mysql.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT    paid_name,    (SELECT COUNT(*)    FROM(     SELECT      I.idx     FROM      TB_XXX_PAID_PAY_INFO I     GROUP BY I.paid_idx, I.pay_idx) O)    AS count,    price   FROM     TB_XXX_PAID_PAY_INFO A   JOIN    TB_XXX_PAID_MSTR B   ON(A.paid_idx = B.idx)   GROUP BY paid_idx
### Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.I.idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

분석

Group By 절에 포함되지 않는 Column (nonaggregated column)을 select 할 경우, 컬럼의 어느 부분에 표시해야 할 지 애매하여 발생하는 에러입니다. (Exception)

MySQL 5.7버전부터 추가된 설정이므로, MySQL 관련 설정을 옮길 때 가능할 수 있습니다.

해결

설정 상의 해결

MySQL 실행 시 only_full_group_by 옵션의 활성화/비활성화를 비활성화 합니다.

 

ex)

1. 
mysql 설정 상태 확인

> select @@sql_mode;


2. 
mysql 설정이 되있음을 확인 (아래 출력 처럼 only_full_group_by이 나오면 설정이 필요)

> | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,                                                                                        DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 


3. 
my.cnf 설정파일의 해당 부분을 추가 (linux 기준 /etc/my.cnf)

[mysqld]에 아래 설정 추가

> sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


4.
mysql 재기동

> service mysqld restart

 

서버 이미지

 

쿼리 상의 해결

1. 쿼리 상의 GROUP BY 절을 따로 분리해 줍니다.

 

ex)

 

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT B FROM C))
FROM F
GROUP BY B



# GROUP BY B 추가

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY B))
FROM F

 

 

 

2. 쿼리 상의 GROUP BY 절에 해당 에러가 발생하는 Column을 추가해줍니다.

 

ex) 

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F
GROUP BY D


# GROUP BY에 B 추가

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F
GROUP BY D, B

 

3. ANY_VALUE 를 사용합니다.

 

ex) 

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT B FROM C GROUP BY D, E))
FROM F
GROUP BY D


# B에 ANY_VALUE 추가

SELECT A,
	   (SELECT COUNT(*) FROM (SELECT ANY_VALUE(B) FROM C GROUP BY D, E))
FROM F
GROUP BY D