MySQL + TypeORM - Illegal mix of collations Error
서버에서 발생한 에러 메세지는 아래와 같았습니다.
QueryFailedError: Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='
에러 메세지 내용으로 파악했을 때 이는 mysql table, column 등에서 지정할 수 있는 collation이 불일치하는 오류였고,
문제가 발생한 api에는 raw query 덩어리가 들어가있기 때문에 원인 후보를 줄이고자 오류가 발생한 api와 유사한 기능을 하는 api들을 확인하였습니다.
정상 동작하는 api들과 이슈 리폿 받은 api 포함 문제가 발생하는 두 api의 쿼리를 미루어보아, 특정 view의 한 column이 문제가 된 걸로 추정했습니다.
해당 부분에 비교 기준을 일치시키는 COLLATE 구절을 삽입해 당장 사용할 수 있도록 임시 조치한 뒤 원인 파악에 들어갔습니다.
해당 view의 정의문엔 별다른 명시가 없었고, 이런 경우 mysql 서버의 default를 사용합니다.
mysql 8.0 이상에선 utf8mb4 charset을 사용하는 경우 collation의 default는 utf8mb4_0900_ai_ci입니다. (그 이하에서는 utf8mb4_general_ci)
datagrip 상에서 해당 view 및 관련 table, column의 collation을 확인해보았으나 전부 동일하게 utf8mb4_0900_ai_ci를 사용하고 있었습니다.
문제 쿼리를 그대로 긁어 datagrip에서 실행했을 때 정상적으로 실행되었고, db의 default도 utf8mb4_0900_ai_ci로 설정되어 있었습니다. (⇒ db 설정상으론 이격이 없음을 의미)
show variables like 'collation_connection';
select @@collation_database;
아래는 로컬에서 문제를 재현하던 중 DB의 설정값과 다른, utf8mb4_general_ci가 나온 쿼리입니다.
SELECT COLLATION('${state}'); # 여기서 state는 오류난 column에 들어가는 enum 값을 가진 변수입니다
SHOW VARIABLES LIKE 'collation_connection';오로지 서버에서만 문제가 발생하는 상태이므로, 서버 어딘가에서 collation을 커스텀해서 사용하고 있는 상태로 추정했습니다.
서버의 database 모듈에서 TypeOrmModuleOptions가 포함하고 있는 MysqlConnectionOptions의 charset 항목은 collation을 지정합니다. (하단 참고 링크 참조)
한 종류의 charset이 여러가지의 collation을 가질 수 있고 그 반대는 아니니 collation 명시를 통해 charset 정보도 취득할 수 있긴한데요, TypeORM은 MySQL DB와의 연결에 대한 collation을 지정하는 옵션을 달리 명명된 항목으로 제공하였기 때문에 이를 charset에 대한 설정으로 오인하기 쉽습니다.
서버 코드상에서는 이 옵션을 ‘utfmb4’로 설정하고 있었습니다.
이 charset 항목에 utf8mb4 라고만 넣으면 typeorm 에서 제공하는 default상 서버에서 맺는 connection은 collation으로 utf8mb4_general_ci를 사용합니다. 이는 mysql 5.7 이하에서 utf8mb4의 default collation 값이기도 합니다.
서버에서 지정한 collation이 utf8mb4_general_ci인 셈이기 때문에 collation conflict가 발생했던 것입니다.
쿼리의 대상이 되는 아이템의 수가 1년에 십수개 정도만 증가하는 곳이었기 때문에 성능상 문제가 될 여지는 적었습니다.
하지만 나중에 이 문제를 모르는 사람이 개발하는데에 불필요한 걸림돌이 될 수 있습니다.
결과
charset 항목값을 utf8mb4_0900_ai_ci로 수정하고 배포한 뒤,
캐싱되지 않은 상태에서 5회씩 실행하여 낸 평균 시간을 기준으로 보았을 때 1.4%의 속도 개선이 있었습니다.
collation 커스텀이 필요한 것도 아니고, 사내 타 서비스에선 이 charset 옵션을 설정하지 않음을 확인했으니 타 서비스 기준으로 통일하는게 낫겠습니다.
의문
Q : 다른 table 또는 column에서 터진 건 발견되지 않았는지?
A : 없었습니다. 다른 곳은 string 일부에 대한 검색(쿼리 변수 없는 LIKE), int인 id에 대한 정렬 또는 비교(dropdown 옵션 중 선택)였지만 오류가 있었던 column은 enum으로 한정된 string을 직접적으로 비교하고 있기 때문에 collation의 차이가 잡힌게 아닐까 추정하고 있습니다. 문제시된 operation은 ‘=’ 과 ‘IN’이었습니다.
Q : 그럼 그동안엔 왜 멀쩡히 돌아갔나?
A : 그러게요... 왜 됐었지.. 최근에 해당 view에 손을 댄 건 퇴사자의 계정으로 되어있던 definer를 변경한 것 뿐이니 일단 이걸 파봐야겠습니다.
참고
https://hoing.io/archives/13254
https://m.blog.naver.com/paradox1573/40149093320