MySQL collation이 섞여 조회가 깨진 날

혼합 콜레이션 JOIN이 푸시 토큰 조회를 깨뜨린 날

클라우드 앱의 FCM 토큰 조회는 테이블마다 콜레이션이 다른 설치에서 Illegal mix of collations 오류로 깨질 수 있었다. 문자열 JOIN에 서로 다른 콜레이션이 섞이는 구조가 원인이었다. 기존 테이블을 바꾸지 않고 비교식과 계정 ID 매칭을 수정했다.

콜레이션이 섞인 설치에서 푸시 토큰 조회가 위험했다

사내 인증 서비스는 클라우드 앱 알림에 필요한 FCM 토큰을 계정별로 조회했다. 기존 설치마다 테이블 기본 콜레이션이 다를 수 있다는 점 때문에 문자열 JOIN 조건을 살펴봤다.

오류의 핵심은 Illegal mix of collations였다. 세션 테이블과 푸시 토큰 테이블의 문자열 비교가 서로 다른 콜레이션으로 묶인 상태였다.

한쪽에는 MySQL 8의 utf8mb4_0900_ai_ci가 적용될 수 있었다. 반면 인증 관련 테이블에는 utf8mb4_unicode_ci를 사용하는 설치가 있었다.

계정 ID로 활성 토큰을 찾는 조회에서 문제가 드러났다. 토큰 취소 여부와 만료 시각, 클라이언트 및 audience 조건은 변경하지 않았다.

테이블 전체 변경 대신 비교식만 수정했다

MySQL 8에서 utf8mb4의 기본 콜레이션은 utf8mb4_0900_ai_ci다. 하지만 기존 인증 테이블은 utf8mb4_unicode_ci를 사용하고 있을 수 있었다.

기존 테이블의 콜레이션을 일괄 변경하는 방법도 고려할 수 있었다. 이번에는 운영 데이터를 재작성하지 않는 방향으로 수정 범위를 정했다.

기존 사용자 매칭은 CONCAT으로 접두사를 붙인 문자열을 비교했다. 이 방식도 서로 다른 콜레이션이 만나면 비교 오류가 발생할 수 있었다.

따라서 테이블 정의를 통째로 바꾸는 대신 조회 SQL을 수정했다. 문자열 비교 규칙을 명시하고 사용자 매칭은 숫자 ID로 처리하기로 했다.

원인은 JOIN의 문자열 비교 규칙이었다

서로 다른 콜레이션이 사용된다고 모든 비교가 실패하는 것은 아니다. MySQL은 각 표현식의 콜레이션 우선순위를 기준으로 비교 규칙을 결정한다.

이번에는 문자열 JOIN에서 비교 규칙이 충돌하는 문제가 있었다. MySQL 공식 문서의 Collation Coercibility에서도 이런 충돌 조건을 설명한다.

SQL 표현식에 COLLATE를 명시하면 적용할 비교 규칙을 지정할 수 있다. 기존 컬럼의 저장 콜레이션을 바꾸지 않고도 해당 비교를 조정할 수 있다.

운영 테이블을 재작성하지 않는 것이 이번 수정의 중요한 조건이었다. JOIN 비교만 unicode_ci로 맞추고, 사용자 매칭은 세션 신원 문자열에서 숫자 ID만 꺼내 CAST하는 쪽이 안전했다.

COLLATE 조인과 숫자 ID 매칭으로 수정했다

2026년 10월 2일 변경에서는 스키마 정의와 토큰 조회 SQL을 함께 수정했다. 회귀 테스트도 추가해 변경된 조회 구조를 확인하도록 했다.

1. 새로 생성하는 푸시 토큰 및 푸시 이벤트 테이블에 COLLATE=utf8mb4_unicode_ci를 명시했다. 기존 CHARSET=utf8mb4만 있던 테이블 생성문을 보완했다.
2. 토큰 조회의 세션 JOIN에 COLLATE utf8mb4_unicode_ci를 추가했다. 서로 다른 기본 콜레이션을 가진 문자열 키를 같은 비교 규칙으로 처리했다.
3. 사용자 매칭에서는 CONCAT으로 접두사를 붙이는 방식을 제거했다. 세션의 신원 문자열은 10자짜리 고정 접두사 뒤에 숫자 ID가 붙는 형식이라, SUBSTRING(..., 11)로 11번째 글자부터 숫자 부분을 꺼낸 뒤 CAST(... AS UNSIGNED)로 변환해 계정 ID와 비교했다. 이 형식은 코드의 신원 문자열 검증 정규식과 같고, 조회 자체도 요청한 신원 문자열과 정확히 일치하는 세션으로 한정했다.
4. 혼합 콜레이션 조회를 위한 회귀 테스트를 추가했다. 가짜 DB 풀로 실행되는 SQL 문자열을 잡아, JOIN의 COLLATE, 숫자 ID 처리, CONCAT 제거, 취소·만료 조건, 스키마 콜레이션 지정과 토큰 중복 제거를 확인하는 구조다. 실제 MySQL에 붙어 실행하는 테스트는 아니다.

조회 변경의 핵심을 일반화한 의사 SQL은 다음과 같았다. 실제 테이블명과 반환 컬럼, 일부 조건은 공개 예시에서 생략했다.

-- 설명용 의사 SQL: 실제 실행용 전체 쿼리는 아님
SELECT ...
FROM push_tokens AS p
JOIN sessions AS s
  ON s.id = p.session_id COLLATE utf8mb4_unicode_ci
JOIN users AS u
  ON u.id = CAST(
    SUBSTRING(s.identity_id, 11) AS UNSIGNED  -- 10자 접두사 뒤 숫자 ID
  )
WHERE s.identity_id = ?
  AND s.revoked_at IS NULL
  AND s.refresh_expires_at > ?;

-- 클라이언트 ID 및 audience 조건은 생략
-- 토큰 중복 제거는 애플리케이션 코드에서 처리

기존 조회에서 사용하던 토큰 취소 여부와 만료 시각 조건은 유지했다. 클라우드 앱을 구분하는 클라이언트 ID와 audience 조건도 그대로 뒀다.

패키지의 패치 버전도 한 단계 올렸다. 설치마다 다를 수 있는 콜레이션 문제를, COLLATE 조인·숫자 ID·스키마 기본값·테스트라는 네 가지 변경으로 옮긴 셈이다.

이번 수정에서 배운 것

• 같은 utf8mb4 문자 집합이라도 서로 다른 콜레이션이 충돌할 수 있었다. 비교 오류 여부는 각 표현식의 콜레이션과 우선순위에 따라 달라졌다.
• 기존 인증 테이블을 재작성하는 대신 필요한 JOIN의 비교 규칙을 명시했다. 사용자 매칭도 숫자 ID로 변경해 문자열 접두사 비교를 없앴다.
• CREATE TABLE에 문자 집합만 지정하면 해당 문자 집합의 기본 콜레이션이 선택된다. 이번에는 새 테이블의 콜레이션까지 명시해 생성 환경에 따른 차이를 줄였다.
• 회귀 테스트에서는 SQL과 스키마 정의의 핵심 조건을 확인하도록 했다. 다만 테스트는 SQL 문자열을 검사하는 수준이라, 배포 후 운영 DB에서 실제로 조회가 성공했는지와 성능 영향은 별도로 확인해야 한다.

자주 묻는 질문

Q. 모든 테이블을 같은 콜레이션으로 바꾸면 되지 않을까? 일괄 변경도 선택지지만 이번 수정에서는 기존 인증 테이블을 변경하지 않았다. 필요한 JOIN 비교식과 사용자 ID 매칭 방식만 조정했다.

Q. JOIN에 COLLATE를 추가하면 인덱스는 어떻게 될까? 이번 변경 기록에는 실제 실행 계획이나 인덱스 활용을 측정한 결과가 없다. 성능 영향은 운영 데이터와 EXPLAIN 결과를 통해 별도로 확인해야 한다.

Q. 왜 CONCAT을 이용한 사용자 매칭을 없앴을까? 접두사를 붙여 만든 문자열도 콜레이션 비교 문제를 만날 수 있었다. 숫자 ID로 변환해 비교하면 사용자 매칭에서 문자열 콜레이션 충돌을 피할 수 있었다.

문자열 비교 규칙과 테이블 생성 기본값은 MySQL 8 공식 문서를 참고했다. Using COLLATE in SQL Statements와 Table Character Set and Collation에서 관련 동작을 확인할 수 있다.