mariadb를 사용하면서 Illegal mix of collations 문제를 경험하게 되었습니다. 이 문제를 해결하기 위해 데이터베이스, 테이블, 컬럼, 프로시저, 그리고 커넥션 레벨에서 collation(정렬 방식)을 확인하는 작업을 수행하고 끝끝내 문제를 해결하게 된 소중한 경험을 독자 여러분과 공유하고자 합니다.
mariadb나 mysql에서 해당 에러를 발견하여 어려움을 겪고 계시거나 Collation 개념을 이해하고자 하는 독자 여려분이라면 도움이 되실 내용이라고 생각됩니다.
1. Illegal mix of collations 문제 발견 과정
처음 이 문제를 발견한 것은 다음과 같은 프로시저 호출을 실행했을 때였습니다
CALL SP_ISSUE_RECEIPT(
'test_user1', 1, 1,
'{"items": [{"name": "테스트상품", "price": 10000}]}',
10000, '신용'
);저는 window와 macos 두개의 운영체제를 주로 사용하는데 macos 운영체제 기반으로 mariadb를 실행했을 때 위와 같은 에러가 발생하였습니다. 에러 메시지의 자세한 내용은 다음과 같습니다.
Error: (conn:156, no: 1267, SQLState: HY000) Illegal mix of collations (utf8mb4_uca1400_ai_ci,IMPLICIT)
and (utf8mb4_general_ci,IMPLICIT) for operation '='
처음에는 단순한 Collation 충돌 문제라고 생각했지만, 테이블Collation을 변경해도 해결되지 않았습니다. 이를 해결하기 위해 보다 깊이 있는 원인 분석이 필요했습니다.
2. Illegal mix of collations 문제 원인 분석
에러 메시지의 핵심은 "utf8mb4_uca1400_ai_ci" 와 "utf8mb4_general_ci" 간의 충돌이었습니다. 두 개의 서로 다른 Collation을 가진 값이 비교 연산에 사용되어 충돌 문제가 발생한 것으로 보입니다.
그래서 저는 문제의 원인을 정확히 파악하기 위해 다음과 같은 데이터들을 확인했습니다.
1. 데이터베이스의 Collation 확인
SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'billon_db';+------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+------------+----------------------------+------------------------+
| billon_db | utf8mb4 | utf8mb4_general_ci |
+------------+----------------------------+------------------------+
2. 테이블의 Collation 확인
SELECT TABLE_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'billon_db';+-------------+------------------------+
| TABLE_NAME | TABLE_COLLATION |
+-------------+------------------------+
| user | utf8mb4_general_ai_ci |
| receipt | utf8mb4_uca1400_ai_ci |
| review | utf8mb4_general_ai_ci |
+-------------+------------------------+3. 컬럼의 Collation 확인
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'billon_db';+-------------+-------------+--------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME |
+-------------+-------------+--------------------+------------------------+
| user | user_id | utf8mb4 | utf8mb4_general_ai_ci |
| user | email | utf8mb4 | utf8mb4_general_ai_ci |
| receipt | receipt_body| utf8mb4 | utf8mb4_uca1400_ai_ci |
| review | content | utf8mb4 | utf8mb4_general_ai_ci |
+-------------+-------------+--------------------+------------------------+4. 현재 세션의 Collation 설정 확인
SHOW VARIABLES LIKE 'collation%';+----------------------+------------------------+
| Variable_name | Value |
+----------------------+------------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+------------------------+3. 문제 원인 발견
위에서 확인한 정보를 바탕으로 데이터베이스와 테이블 및 컬럼의 Collation이 서로 다르다는 사실을 확인하였습니다.
- 데이터베이스의 Collation → utf8mb4_general_ci
- 테이블의 Collation → utf8mb4_uca1400_ai_ci
- 컬럼의 Collation → utf8mb4_uca1400_ai_ci
정확히 말하자면 프로시저 내부에서 입력받는 입력값의 정렬 방식과 컬럼의 정렬 방식에 차이가 생기면서 문제가 생긴 것 같습니다.
SQL에서 문자열 리터럴의 기본 COLLATE는 collation_connection 설정값을 따른다. 즉, 리터럴 값의 기본 COLLATE는 현재 세션(SESSION collation_connection)에서 결정된다고 합니다.
위에서 현재 세션의 collation_connection의 정렬 방식은 utf8mb4_general_ci인 것으로 확인되는데 문제는 컬럼의 정렬방식이 utf8mb4_uca1400_ai_ci에 따라 문제가 생기는 것입니다.
따라서 이를 해결할 수 있는 방법은 총 3가지로 좁힐 수 있습니다.
4. 문제 해결 방법
1. collation_connection의 정렬 방식을 컬럼의 정렬 방식으로 맞추기
해결 방법
collation_connection은 세션 내에서 리터럴 문자열(문자 상수)의 COLLATE를 결정하기 때문에 현재 컬럼의 COLLATE가 utf8mb4_uca1400_ai_ci라면, collation_connection도 이를 맞춰 동일하게 설정하면 문제를 해결할 수 있습니다.
SET collation_connection = 'utf8mb4_uca1400_ai_ci';문제점
하지만, collation_connection을 변경하면 이후 실행하는 쿼리의 리터럴 값의 COLLATE는 변경되지만 이미 생성된 테이블, 트리거, 뷰, 프로시저 등의 COLLATE는 변경되지 않으므로 collation_connection이 utf8mb4_uca1400_ai_ci로 변경되더라도, 기존 테이블 및 프로시저가 utf8mb4_general_ci를 사용하고 있다면 여전히 충돌이 발생할 가능성이 있습니다.
따라서 일시적인 해결책으로는 적절하지만, 근본적인 해결책은 되지 않을 수 있기 때문에 권장되는 방법은 아닙니다.
2. CREATE 문에서 COLLATE를 명시하여 데이터베이스 재생성하기
해결 방법
MariaDB와 MySQL에서는 데이터베이스 레벨에서 COLLATE를 설정하면, 해당 DB에서 생성하는 모든 테이블 및 컬럼이 기본적으로 이 COLLATE를 따르므로 DB 생성 시 COLLATE를 명확하게 설정하여 테이블 및 컬럼이 일관된 COLLATE를 가지도록 강제하면 충돌이 발생하지 않아 가장 안전한 방식입니다
DROP DATABASE IF EXISTS billon_db;
CREATE DATABASE billon_db CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
USE billon_db;
모든 테이블이 동일한 COLLATE를 따르도록 보장되고 이후 생성되는 테이블, 뷰, 프로시저, 트리거 등도 동일한 COLLATE를 따르기 때문에 새로운 프로젝트를 시작하거나, 데이터베이스 구조를 변경할 계획이 있다면 가장 강력한 해결책입니다.
다만 기존 데이터를 모두 삭제하고 재생성해야 하기 때문에 이미 데이터베이스가 생성되어 있거나 삭제하고 다시 생성할 수 없는 상황에선 사용할 수 없는 방법입니다.
3. ALTER 문을 사용하여 기존 테이블과 컬럼을 데이터베이스 COLLATE에 맞추기
해결방법
ALTER TABLE을 사용하여 기존 데이터베이스를 유지하면서, 모든 테이블과 컬럼의 COLLATE를 데이터베이스의 COLLATE와 맞추는 방법입니다.
ALTER TABLE user
MODIFY user_id VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci,
MODIFY password VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci,
MODIFY user_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
ALTER TABLE receipt
MODIFY receipt_body LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
ALTER TABLE review
MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;장점은 기존 데이터를 유지하면서도 COLLATE를 변경할 수 있으면서도 운영 환경에서도 무중단으로 적용 가능합니다.
하지만 모든 테이블과 컬럼을 하나씩 변경해야 하고 FK가 설정된 경우 외래 키를 삭제한 후 변경해야 하고 대량의 데이터가 존재하는 경우 ALTER 수행 시 성능 저하 발생 가능성이 있습니다.
따라서 현재 자신이 처한 상황에 따라 어떤 해결 방법을 사용할 것인지에 대해선 고민이 필요하고 필자의 경우엔 새로운 프로젝트 시작하는 경우이기 때문에 CRETAE DATABASE시 COLLATE를 지정하는 것으로 문제를 해결했습니다.
이미 운영중인 DB의 경우엔 해당 방법으론 해결할 수 없으므로 ALTER TABLE로 변경하는 방법을 생각하셔야 될 것 같습니다.
결론
1️⃣ Collation 문제는 데이터베이스, 테이블, 컬럼, 프로시저, 세션 등 여러 곳에서 발생할 수 있음
2️⃣ 운영체제 및 MySQL/MariaDB 버전에 따라 기본 Collation이 다를 수 있음
3️⃣ 처음부터 테이블을 생성할 때 COLLATE를 명시적으로 설정하는 것이 가장 안정적이고 좋은 방법이다
4️⃣ 이미 운영하는 DB가 있다면 ALTER TABLE을 사용하여 모든 테이블과 컬럼을 변경해야 한다.
이번 경험을 통해 MySQL의 Collation 관리의 중요성을 깊이 이해할 수 있었고, 앞으로는 사전에 Collation을 일관되게 설정하여 앞으로 발생할 거대한 문제를 초기에 잡아야 된다는 큰 깨달음을 얻을 수 있는 좋은 경험이 된 것 같습니다.
뿐만 아니라 에러 메시지 해석을 시작으로 관련된 개념을 찾아보는 과정에서 mariadb 및 sql에 대한 깊이있는 공부를 할 수 있는 기회가 되어 좋은 경험이라 생각이 됩니다.
