[MySQL, MariaDB] sql_mode 문제 (ONLY_FULL_GROUP_BY 비활성화)

문제 확인

CodeIgniter3 로 만들어진 기존 프로젝트가 이상하게 내 local DB와 연결할 때 제대로 안켜지는 문제를 겪었다. 

 

계속 헤매다 로그도 제대로 출력이 안되고 있어서 확인해봤더니 모든 로그를 출력하지 않는게 기본 설정이었음

 

application/config/config.php

// $config['log_threshold'] = 0;
$config['log_threshold'] = 4;

0은 로그 비활성화

4로 설정하면 모든 메시지 기록이다. 

출처: https://extbrain.tistory.com/108

 

로그 확인

ERROR - 2023-08-16 03:26:49 --> Severity: error --> Exception: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_total.gcat_cnt_idx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (경로)/www/system/database/drivers/mysqli/mysqli_driver.php 301

확인해보니 정상적으로 열릴 때와 다르게 이런 로그가 찍히고 있었는데 

오래된 DB에서 문제없이 동작하는 쿼리에 대해 최신 버전 DB에서 문제가 되는 상황이었다. 

 

only_full_group_by라는 sql_mode를 비활성화 해줘야 한다는듯.

 

실제로 DB의 sql_mode를 확인해봤더니 아래와 같았다. 

 

sql_mode 확인 sql

select @@sql_mode;

 

정상 연결 DB

IGNORE_SPACE,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION

 

내 DB

IGNORE_SPACE,
ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ENGINE_SUBSTITUTION

다른 부분이 몇개 더 있었는데 일단 로그상에선 ONLY_FULL_GROUP_BY가 문제라고 하니 이거부터 없애보았다. 

 

ONLY_FULL_GROUP_BY 비활성화

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

다시 select @@sql_mode; 로 확인해보면 여전히 ONLY_FULL_GROUP_BY가 같이 나오고 있는데

실제 문제 상황을 재연출해보면 이전과 같이 에러 상황이 생기지 않고 정상작동하는 것을 확인했다. 

(이 부분이 이해가 안가긴 한다.)

 

정리

내가 개발을 배우기 시작했을 때부터 접하는 MySQL, MariaDB들은 해당 문제 상황을 넘어선 버전이었어서 그런지

겪어보지도 않은 문제라 당황했고 일단 위와 같은 방법으로 해결하게 되었는데

만약 서버를 실제로 구축하게 된다면 두가지 선택 사항이 존재할 것 같다. 

 

일단 왜 두 DB 회사에서 최신버전들에 대해 ONLY_FULL_GROUP_BY 활성화를 기본 설정으로 했느냐.

https://goodteacher.tistory.com/563

[mysql 설정] ONLY_FULL_GROUP_BY

위 블로그에서 좋은 예시를 들고 있는데, 

예전 DB에서는 일반 단일 컬럼과 group by 함수를 그냥 같이 사용하는 경우에 대해 고려가 안된 것인지

실행도 되고 에러 상황도 아니었다. 

하지만 고도화되면서 이런 문제에 대해 말이 안되니 애초에 DB 자체 기능적으로 막게 되었다고 본다. 

그게 5.7 버전을 기점이지 싶다. 

 

때문에 이렇게 설정을 바꾸기 보단 코드에 호환되는 옛날 버전으로 설치해서 이외 다른 문제 상황들도 피하는 방법이 첫 번째 방법이 될거고

위에서 내가 했던 것처럼 직접 설정을 바꾸는 것이 두 번째 방법이 되겠다. 

 

다른 테이블에도 영향이 가지만 문법상의 경고지 크리티컬한 사항이 아니라서

여러 보안 이슈 등의 문제가 있는 옛날 버전을 사용하기보단
최신 버전을 받아 위 설정만 변경해주는게 더 좋은 방법이지 않을까 개인적으로 생각한다. 

 

버전 확인 sql

SELECT VERSION();

정상 연결 DB

10.4.24-MariaDB-1:10.4.24+maria~focal-log

내 DB

8.0.33

참고로 DB의 버전을 확인해봤더니 위처럼 나왔다. 내 DB는 MySQL이라 나오는 형태부터 달랐는데

만약 새로 환경을 구축할 때 참고할 상황이 생길 것 같아 기록해둔다.