[MariaDB] Illegal mix of collations 에러 해결

개요

회사 업무를 진행하던 중 특정 쿼리를 수행할 때 마다 아래와 같은 Collations 관련 오류가 연달아 3번이나 발생했다.

Illegal mix of collations (~~) and (~~) for operation '=’

 

Collation이란 ?? 

 

문자열을 비교, 정렬하기 위해서 정의된 규칙들의 집합이다 

(Character Set은 글자 자체의 인코딩에  대한 정의)

 

이 Collation 오류에 대한 히스토리와 해결 방법을 공유하고자 한다.

 

첫번째 오류

가장 처음 발생한 Collation 오류 메시지는 다음과 같다 

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=’

 

원인 파악

해당 오류는 구글링을 통해 비교적 쉽게 원인 파악이 되었다.

 

오류 원인은 쿼리 JOIN절에서 컬럼간 비교하는데 서로 Collation 값이 서로 일치하지 않았고, 이로 인해 오류가 발생한 것이었다. 

 

아래와 같이 비교하는 부분에서 A 테이블의 컬럼은 utf8mb4_general_ci 로, B 테이블의 컬럼은 utf8mb4_0900_ai_ci 로 되어있다는 뜻이다.

on A.column = B.coulmn 

 

DB 내에 약속된 collation 값이 "utf8mb4_general_ci" 이었는데, 어째서인지 내가 관리하고 있는 일부 테이블과 컬럼 의 Collation 값이 "utf8mb4_0900_ai_ci" 값으로 되어 있었다.

 

해결

"utf8mb4_0900_ai_ci" 값으로 되어있는 컬럼과 테이블의 Collation 설정을 전부  "utf8mb4_general_ci" 으로 변경시켜 주었다.

 

1. 테이블 Collation 조회

SHOW TABLE STATUS WHERE NAME LIKE <테이블명>;

 

2. 테이블 Collation 변경 

ALTER TABLE <테이블명> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

 

3. 컬럼 Collation 조회

SHOW FULL COLUMNS FROM table_name;

 

4. 컬럼 Collation 변경

ALTER TABLE <테이블명> MODIFY COLUMN <컬럼명> <컬럼타입> COLLATE utf8mb4_general_ci

 

두번째 오류

첫번째 오류를 수정하고 나서 얼마 지나지 않아 쿼리의 다른 부분에서 Collation 오류가 또 발생했다. 

Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation 'instr’

 

문제가 되었던 쿼리는 아래의 SELECT 절로, stored function 결과값에 '>' 문자의 위치를 찾는 instr 함수에서 발생했다

select instr(get_dir_navi(d.category_id), '>')
from category c
	left join directory d on c.caregory_id = d.caregory_id

 

원인 파악

오류 원인은 stored function 반환 값의 collation 값이 "utf8mb4_0900_ai_ci" 반환되고 있었고, 

비교문자 '>' 은 현재 DB의 기본 Collation 값인 "utf8mb4_general_ci" 이었기 때문에 충돌이 나는 것으로 확인됐다.

 

해결

첫번째 오류가 발생했을 때 Stored Function에서 사용되고 있는 테이블과 컬럼의 collation 값을 모두 변경해주었지만,

이미 만들어진 Stored Function에는 반영되지 않고 최초 생성 당시의 테이블과 컬럼의 Collation 값을 사용하고 있었다. 

 

Stored Function을 삭제하지 않고 변경하는 방법을 찾아보려 했지만 찾지 못해서, 그냥 해당 함수를 삭제하고 재생성했다. 

 

세번째 오류

두번쨰 내용까지 수정하고 배포한 뒤 실서버로 확인 했을때는 오류가 더이상 발생하지 않았다.

 

하지만 이상하게 로컬에서는 두번째 오류가 발생한 부분에서 조금 다른 형태로 오류가 발생하고 있었다. 

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 'instr’

 

원인 파악

오류 내용을 읽어보니 두번째 오류의 원인이었던 stored function의 결과값은 utf8mb4_general_ci 값으로 잘 변경되었는데,  이번엔 비교 문자인 '>' 의 collation 값이 반대로 utf8mb4_0900_ai_ci 으로 나오고 있었다. 

 

실서버와 로컬에서 동일한 DB에 접근하는데 DB의 기본 Collation이 달라지는 기이한 현상이었다.

 

이것저것 삽질을 통해 알아낸 결론은 인텔리제이 DB 툴을 통해 DB에 연결할 때 Collation 설정하는 부분이 있는데, 따로 설정을 하지 않고 접속했더니 utf8mb4_0900_ai_ci 설정이 바뀐 것 이었다.

 

해결

IntelliJ의 DB 툴을 통해 연결 설정 부분의 Collation 값을 수정한 뒤 재연결 해서 오류를 해결했다.

 

1. 해당 DB의 접속 설정을 위해 [데이터 소스] 설정 화면 진입

 

2. [데이터 소스] > [고급] > connectionCollation을 "utf8mb4_general_ci" 으로 입력