[MariaDB] Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation 에러 해결

kyle·2023년 9월 11일

MariaDB utf8mb4_general_ci

DB

목록 보기

12/14

오류 내용

회사 업무를 진행하던 중 특정 쿼리를 수행할 때 아래와 같은 오류가 발생했다.

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

오류 원인

각 테이블, 컬럼마다 UTF8 과 같은 collation 값이 설정되는데,
문제가 발생한 쿼리의 JOIN 절에서 비교한 컬럼 끼리 collation 값이 서로 일치하지 않아서 발생하는 오류였다.

해결

DB에서 사용하기로 약속한 collation 설정 값은 "utf8mb4_general_ci" 이기 때문에
"utf8mb4_0900_ai_ci" 값으로 설정되어 있는 테이블과 컬럼의 collation 값을 수정하였다.

테이블 Collation 조회

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

테이블 Collation 변경

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

컬럼 Collation 조회

SHOW FULL COLUMNS FROM table_name;

컬럼 Collation 변경

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

+ 추가 오류 (1)

위와 같이 수정하고 다시 테스트 해보니 아래와 같이 다른 부분에서 동일한 오류가 발생했다.

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

오류 원인

문제가 되었던 쿼리는 아래의 SELECT 절에서 특정 함수를 통해 값을 가져오는 부분에서 오류가 발생 했다.

select instr(fn_get_board_dir_navi_lang(bd.top_level, bc.comp_id, 'kr'), '>')
from board_category bc
	left join board_directory bd on bc.comp_id = bd.comp_id and bc.cat_seq_no = bd.cat_seq_no

오류 원인은 instr 함수에서 fn_get_board_dir_navi_lang 함수의 결과 값과 비교문자 '>' 를 비교할 때

비교문자 '>' 은 현재 DB의 기본 Collation 값인 "utf8mb4_general_ci" 이지만, 함수의 결과값은 "utf8mb4_0900_ai_ci" 이어서 충돌이 발생한 것이었다.

해결

최초 오류사항을 해결할 때 모든 테이블과 컬럼의 collation 설정 값을 모두 "utf8mb4_general_ci" 으로 맞춰 주어서

해당 테이블과 컬럼을 사용하는 "fn_get_board_dir_navi_lang" 함수도 같이 적용 되었을것이라 생각했지만
확인해보니 함수의 collation은 생성 당시 테이블 , 컬럼의 collation 설정을 따라 가고 있었다.

즉, 함수 생성 당시 내부에 정의된 테이블의 collation이 "utf8mb4_0900_ai_ci" 이었기 때문에
생성 이후에 해당 테이블의 collation 값을 변경한다고 해도 생성 당시의 collation 값인 "utf8mb4_0900_ai_ci" 형태로 결과값을 리턴하고 있엇던 것이다.

해결 방법으로는 해당 함수를 삭제 한 뒤 재생성하였다.