MySQL sql_mode ONLY_FULL_GROUP_BY 에러 쿼리 수정으로 해결하기

MySQL sql_mode ONLY_FULL_GROUP_BY 에러

죽지도 않고 또 다시 온 MySQL sql_mode ONLY_FULL_GROUP_BY 에러🤯 매번 sql_mode를 해제하는 방식으로 해결해왔지만 이번에는 근본적인 문제를 이해하고 쿼리를 수정해보기로 했다.

에러의 전문은 다음과 같다.

ERROR [ExceptionsHandler] Expression #12 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'workerbench.purposeTag.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

해석해보면 GROUP BY에 명시되지 않은 컬럼을 SELECT 하기 때문에 발생하는 문제라는 것을 알 수 있다. 이 문제는 MySQL 5.x 버전 이상부터 강화된 sql_mode=only_full_group_by 옵션 때문에 발생한다.

해결 방법 1: MySQL sql_mode 해제

사실 가장 단순한 해결 방법은 해당 옵션을 MySQL 관리자 콘솔에서 끄는 방법이다.
(이 방법으로 해결한 적이 이미 몇 번 있다 → https://velog.io/@jiumn/nbc-til-230316)

그런데 사실 sql 모드를 끄면 GROUP BY에 해당하지 않는 컬럼이 어떻게 SELECT될 지 예측할 수 없어 보안 문제가 발생할 수 있다고 한다.

해결 방법 2: sql 쿼리 수정

sql 모드를 해제하지 않고 쿼리를 수정하기 위해 기존 코드를 살펴보자.

  async searchWorkshops(
    category: string,
    memberCnt: number,
    location: string,
    purpose: string,
    genre: string,
  ) {
    const queryBuilder = this.workshopRepository
      .createQueryBuilder('workshop')
      .innerJoinAndSelect('workshop.GenreTag', 'genre') // workshop - GenreTag 테이블 조인
      .innerJoinAndSelect('workshop.PurposeList', 'purpose') // 조인한 결과에 PuposeList 테이블 조인
      .innerJoinAndSelect('purpose.PurPoseTag', 'purposeTag') // 조인한 결과에 PurPoseTag 테이블 조인
      .select([
        'workshop.id',
        'workshop.title',
        'workshop.category',
        'workshop.thumb',
        'workshop.location',
        'workshop.price',
        'workshop.min_member',
        'workshop.max_member',
        'workshop.total_time',
        'workshop.updatedAt',
        'genre.name',
        'purposeTag.id',
        'purposeTag.name',
        'GROUP_CONCAT(purposeTag.name) AS purposeTag_name',
      ])
      .where('workshop.status = :status', { status: 'approval' })
      .groupBy('workshop.id')
      .orderBy('workshop.updatedAt', 'DESC');

workshop.id로 groupBy를 하고 있는데 에러에서 명시한 purposeTag.id의 경우, 하나의 워크샵이 여러 개를 가질 수 있게 만들어놨기 때문에 여러 개의 purposeTag.id 행 중 어떤 걸 가져와야 할 지 몰라서 생기는 문제라고 할 수 있다.

어차피 'GROUP_CONCAT(purposeTag.name) AS purposeTag_name'로 그룹핑을 한 후 하나로 합쳐주고 있기 때문에 select 문에서 'purposeTag.id', 'purposeTag.name'
2개의 조건을 지워주면 된다.

프로젝트 당시 에러를 만났을 때는 어렵게 생각하고 빨리 해결하기 위해 sql_mode를 무작정 껐는데, 이번 기회에 에러의 원인을 더 정확하게 알 수 있었다. 끝까지 생각하면 이해 못하는 것은 없다!