[Spring/MySQL] sql_mode=only_full_group_by 오류 해결

스프링과 MySQL을 연동해 사용하다가 sql_mode=only_full_group_by 오류가 발생했다..

ERROR 27180 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'd.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ERROR 27180 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

첨 보는 오류라 어떤 오류인지 찾아보았다.

 

알아보니 MySQL 5.7 이상 버전에서 새로 생긴 오류라고 한다.

 

쿼리는 다음과 같았다.

@Query("SELECT m, count(w) from Member m join fetch m.works w where w.date = :date and m.name = :name group by m")

오류가 발생하는 이유는 group by 절에 포함되지 않는 열을 select 하려해 그렇다.

join fetch를 하게 되면 member와 맵핑이 되어 있는 works 엔티티도 함께 select하게 되는데

Member로만 group by를 하는데 works 엔티티도 select하려 하지만 어떤 컬럼을 표시해야하는지 모호해 오류가 발생한다.

 

반응형

 

해결방법

---

1. 모드 변경

mySQL에 접속해 아래 명령어를 치면 활성화된 모드들이 나온다.

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가 있는것을 확인할 수 있다.

 

mySQL 모드에 관리자로 로그인해 ONLY_FULL_GROUP_BY를 비활성화해주면 된다.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

하지만 쿼리를 수정해 오류를 해결하고 싶어 다른 방법을 사용했다.

 

2. ANY_VALUE

오류가 나는 SELECT 열에 ANY_VALUE를 해준다.

SELECT Member.name , ANY_VALUE(Member.number)
FROM Member
GROUP BY Member.name;

GROUP BY를 name만 이용해 하지만 number에 ANY_VALUE를 사용해 오류가 발생하지 않는다.

예시 코드를 갑자기 다르게 한 이유는 나는 fetch join으로 인해 w를 자동으로 select 하려해 발생한 오류라 ANY_VALUE를 사용해도 오류를 수정할 수 없었다.

 

3. 쿼리 수정

@Query("SELECT m, count(w) from Member m join fetch m.works w where w.date = :date and m.name = :name group by m, d")

단순하게 group by에 d를 추가시켜주어도 오류가 해결된다.

하지만 이렇게 해결하면 의도한 대로 결과가 나오지 않을 수 있어 쿼리에 따라 다르다.

나는 이렇게 수정하면 결과가 다르게 나와 다른 방법으로 오류를 수정했다.

 

4. 네이티브 쿼리 사용

    @Query(value = "SELECT user_id as UserId, user_name as Username, COUNT(number) as Count" +
            " FROM (SELECT m.user_id as user_id, name as user_name, w.number as number" +
            " from Member m" +
            " inner join works w on m.user_id = w.user_id" +
            " where w.date = :date and m.name = :name) as q" +
            " GROUP BY user_name, user_id", nativeQuery = true)

그래서 Native Query와 Sub Query를 사용해 오류를 수정했다.

 

jpql을 사용해서도 서브 쿼리를 잘 사용한다면 오류를 해결할 수도 있을것 같은데 계속 실패했다.

시간이 남을때 수정해봐야 겠다.