오류 내용

MariaDB에서 아래와 같은 이슈사항이 발생했다.

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

위의 내용 같은 경우에는 JOIN 절에서 비교하는 컬럼끼리 collation 설정이 맞지 않아서 발생하는 오류였다.

해결

해당 컬럼을 현재 사용하는 DB의 기본 설정값인 "utf8mb4_general_ci" 으로 수정하였다.

• 특정 테이블의 컬럼 별 Collation 보는 방법

SHOW FULL COLUMNS FROM table_name;

• 특정 컬럼 Collation 설정 변경

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

추가로 JOIN 하는 테이블의 collation이 utf8mb4_0900_ai_ci으로 되어 있어서 이부분도 수정해주었다.

• 테이블 Collation 설정 보는 방법

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

• 테이블 Collation 설정 변경

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

---

추가 오류 발생 (1)

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

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

문제가 되었던 쿼리는 아래의 SELECT 절로, function을 통해 게시판의 경로를 가져오는 쿼리에서 오류가 발생 했다.

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의 비교문자 '>' 를 비교할 때 collation 설정이 맞지 않다는 뜻이었고,
함수의 결과값이 "utf8mb4_0900_ai_ci" 라는 뜻이었다.

첫번째 오류의 조치사항으로 해당 테이블과 해당 테이블의 컬럼의 collation을 모두 "utf8mb4_general_ci" 으로 맞춰 주어서

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

즉, 생성 당시 내부에 정의된 board_directory 테이블의 collation이 "utf8mb4_0900_ai_ci" 이었기 때문에, 생성 이후 board_diretocy 테이블의 collation을 변경해도, 리턴값이 "utf8mb4_0900_ai_ci" 형태로 리턴되는 것 이었다.

이부분은 단순하게 해당 함수를 삭제하고 재생성해서 해결했다.

---

추가 오류사항 (2)

위에 내용까지 수정하고 실 서버로 확인 했을때는 오류가 더이상 발생하지 않았다.
하지만 이상하게 로컬에서는 동일한 쿼리에서 오류가 발생하고 있었다.

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

함수의 리턴값은 utf8mb4_general_ci 타입이지만, 비교 연산자 역할을 하는 '>' 값이 utf8mb4_0900_ai_ci 타입이었다.

이것저것 찾아봤는데 결론은 DB 연결 할 때, 설정값에서 collation 설정을 따로 하지 않았더니
collation 값이 utf8mb4_0900_ai_ci 으로 기본 설정되어서 발생한 오류였다.

해결

나의 경우에는 IntelliJ의 DB 툴을 사용하고 있었기 때문에,
IntelliJ DB 툴의 접속 설정을 수정해주었다.

수정 방법은 아래 순서대로 진행하면 된다.

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

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