[Etc.]sql_mode=only_full_group_by 에러 해결하기(Mac, mySQL)

문제 상황

@Query("select m, mi, avg(coalesce(r.grade,0)),  count(distinct r.reviewnum) " +
            "from Movie m " +
            "left outer join MovieImage mi on mi.movie = m "+
            "left outer join Review r on r.movie = m " +
            "group by m")

해당 SQL문 수행 중 자꾸 Group by 오류가 발생했다.
책에 나온 그대로 했는데 혹시 오타가 있나 내가 뭐 잘못했나 계속 지우고 다시 쓰고를 반복하다가 걍 던져두고 다른 공부를 햇다.

3일 지나고 다시봐도 해결 못하겠어서 결국 카페 가입하고 물어보니 Group By 문제라는 것을 알게 되었다..

문제는 MariaDB와 mySQL의 차이로 책의 예제 SQL문이 안되는 거였다ㅡ___ㅡ

---

오류 문장

could not extract ResultSet; SQL [n/a]; 
nested exception is org.hibernate.exception.SQLGrammarException: 
could not extract ResultSet

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

---

오류 분석

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

무튼 내가 지금 사용 중인 mysql 버전은 8.0.31 인데 5.7부터 sql_mode 항목이 생겼고 그 옵션 안에 only_full_group_by 내용이 존재해 발생한다고 한다.

MySQL 5.7.5 이상에서는 기능 종속성 감지를 구현합니다. ONLY_FULL_GROUP_BYSQL 모드가 활성화된 경우(기본적으로 활성화됨) MySQL은 선택 목록, 조건 또는 목록이 절에 이름이 지정되지 않았거나 기능적으로 종속되지 않은 집계되지 않은 열을 참조하는 쿼리 를 HAVING거부 합니다

Group By를 안쓰는 쿼리로 바꾸거나 복잡하게 수정하는 건 싫으니 설정 정보를 바꿔주었다.

---

문제 해결

1 sql_mode항목을 사용하는지 조회하기

select @@sql_mode;

workbench에서 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조회 시 저 항목이 있다면 나처럼 위의 오류가 발생한다.
저걸 이제 제거하면 되는데 제거하는 방법으로는

1. 현재 연결된 세션에서만 제거
2. 영구 제거(괘씸하니깐 추천)

이렇게 두 가지 방법이 있다.

2 반 영구 제모할지 영구 제모할지 선택하기

2-1) 현재 연결된 세션에서만 제거

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';

단순히 쿼리 테스트를 위해 현재 연결된 세션에서만 항목을 변경하고자 한다면
위의 문구를 봍해서 실행하면 된다.

하지만 나는 영구제거 할거야

2-2) 영구 제거

영구적으로 제거를 하려면 mySQL을 사용하는 시스템에서 'my.cnf'파일을 수정해야한다.

3 mac에서 my.cnf 찾기

나는 brew를 통해 mysql을 설치하였기 때문에 그 경로를 직접 찾아서 타고 들어갔다.

(base) 나@pizzaBook / % ls
Applications	Volumes		etc		sbin
Library		bin		home		tmp
System		cores		opt		usr
Users		dev		private		var

(base) 나@pizzaBook / % cd opt/homebrew/etc

(base) 나@pizzaBook etc % ls
bash_completion.d	gnutls			pkcs11
ca-certificates		my.cnf			unbound
gitconfig		openssl@1.1

(base) 나@pizzaBook etc % vim my.snf

i를 눌러 INSERT 모드로 바꾸고

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
.
.

이걸 복붙해서 적고 ESC+:wq하면 된다!

4 재실행

이렇게 설정 정보를 바꾸고 mysql.server start를 다시하면
드디어 나도 Group by 쌉가능