Mysql 데이터정렬 collation 이슈 해결하기

Mysql 데이터정렬 collation 이슈 해결하기

Issue

Springboot + jpa + querydsl 환경의 백엔드 개발 도중 당황스러운 에러가 나타났습니다.

queryFactory.select()     .from(entityA)     .leftJoin(entityB)     .on(entityA.entityBIdString.eq(entityB.entityBId.stringValue()))     .fetch(); 

먼저 간단히 환경은

• entityA와 entityB는 논리상 N : 1 관계
• 데이터베이스 설계상 entityB의 id (entityBId) 컬럼은 bigint이고, entityA의 entityBIdString 컬럼은 varchar 타입
• entityA와 entityB는 jpa entity에서 manyToOne 또는 oneToMany 사용을 하지 않음
  위와 같았고, 쿼리 실행시

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

위와 같은 에러가 나타났습니다.

TroubleShooting

Mysql의 Illegal mix of collations 에러는 비교를 하려고 시도한 컬럼의 collation이 서로 달라 비교가 불가능하다는 내용의 에러입니다.

collation

collation은 문자열을 비교하고 정렬할 때 사용하는 규칙입니다.
Character Set이 문자들의 집합이라면 collation은 대소문자, 악센트 구분, 문자 정렬 순서 등을 이용해 문자를 비교하고 정렬하는 방법을 결정합니다.

• 문자열간의 비교
• ORDER BY 등의 쿼리에서 결과의 정렬 순서를 결정
• 대소문자 구분여부 결정
• ci: case insensitive, 대소문자를 구분하지 않음
• ai: accent insensitive, 악센트가 있는 문자를 악센트가 없는 문자와 같이 취급
• general: 일반적인 목적으로 사용되는 비교규칙
• 0900: Unicode 9.0.0 기준으로 문자 비교, Mysql 8.0에서 도입
• unicode: Unicode 표준을 따름

utf8mb4_unicode_ci는 utf8mb4를 기본 문자 집합으로 사용하며, Unicode 표준을 따르며 대소문자를 비교하지 않습니다.
utf8mb4_0900_ai_ci는 utf8mb4를 기본 문자 집합으로 사용하며, Unicode 9.0.0 를 따르며 악센트를 무시하고, 대소문자를 비교하지 않습니다.

현재 사용하고 있는 Mysql 버전은?

현재 이슈가 나타난 Mysql 버전은 8.0버전입니다.
하지만 현재 사용하고 데이터베이스를 초기 설정할 때 몇가지 상황이 있었는데요.

1. 제가 일하고 있는 회사에서 기존 운영하던 서비스가 있었고, 신규 서비스를 개발하면서 만든 신규 데이터베이스에서 이슈가 나타났습니다.
2. 기존 서비스의 데이터베이스는 5버전 안에서 5.7버전까지 업그레이드하는 과정에서 5.5버전에서 이용하던 utf8mb4_unicode_ci로 데이터베이스와 테이블이 만들어져 있었습니다.
3. 아무 생각 없이 기존 서비스의 데이터베이스 collation이 utf8mb4_unicode_ci인 것을 보고 신규 데이터베이스의 collation도 utf8mb4_unicode_ci으로 설정했습니다.

그러면 왜 단순 형변환된 문자를 비교하지 못했는가

select      from entity_a     left join entity_b         on entity_a.entity_b_id_string = cast(entity_b.entity_b_id as char) 

위 querydsl 코드가 실행되었을 때, 쿼리는 위와 같습니다.

querydsl 의 .stringValue()는 cast( as char) 로 변환되어 값을 char 형태로 변환합니다.
이 때, 숫자를 문자열로 바꾸는 과정에서 collation을 지정하지 않아 mysql 8.0 의 기본 collation인 utf8mb4_0900_ai_ci 을 따르는 문자열로 변환되었고, 비교를 원하던 entity_a.entity_b_id_string 는 테이블에 지정된 utf8mb4_unicode_ci를 따르기 때문에 서로간의 문자열 비교가 불가합니다.

Solution

Mysql 설정을 utf8mb4_unicode_ci으로 변경하는 선택지는 데이터베이스 재시작시 초기화되어 선택지에서 뻇습니다.
결국 기존 utf8mb4_unicode_ci 으로 설정한 데이터베이스의 설정을 변경하였는데, 이 경우 데이터베이스에서 다양한 언어나 특수문자를 다루는 경우에는 문제가 생길 수 있고, collation을 수정하는 시기에 성능문제가 발생할 수 있습니다.
하지만 Mysql 8.0의 기본 collation인 utf8mb4_0900_ai_ci으로 변경하는 것이 추후 개발시 쿼리 정합성에 도움이 되고, 정렬은 별도의 컬럼으로 처리하였기 때문에 안전하다 판단하여 데이터베이스와 테이블의 기본값을 utf8mb4_0900_ai_ci로 변경하는 것으로 결정하였습니다.

데이터베이스 확인/수정 쿼리

Mysql에 설정된 값 확인

SHOW VARIABLES LIKE 'character_set_server';   SHOW VARIABLES LIKE 'collation_server'; 

현재 존재하는 데이터베이스(schema)의 character set, collation 확인

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME   FROM information_schema.SCHEMATA; 

특정 테이블의 컬럼 설정 확인

SELECT COLUMN_NAME, COLLATION_NAME  FROM information_schema.COLUMNS  WHERE TABLE_SCHEMA = '데이터베이스이름'  AND TABLE_NAME = '테이블이름'; 

데이터베이스 collation 변경

ALTER DATABASE 데이터베이스이름 CHARACTER SET utf8mb4 COLLATE 'collate명'; 

테이블 collation 변경

ALTER TABLE 테이블이름 CONVERT TO CHARACTER SET utf8mb4 COLLATE 'collate명'; 

결국 초기 데이터베이스 설정 시기에 collation에 대해 정확하게 알아보지 않고 수정한 것이 (개발 단계였지만) 큰 이슈를 막을 수 있었다 생각했습니다.
Querydsl이 컴파일 단계에서 에러를 잡아준다는 장점이 있지만, 원초적으로 데이터베이스의 설정이 런타임에 에러를 발생시킬 수 있다는 점을 공유하고 싶었습니다.
감사합니다.