MySQL collation 관련 오류

참고

https://medium.com/oldbeedev/mysql-utf8mb4-character-set-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0-da7624958624

https://blog.lael.be/post/917

QA를 진행하면서 실서버에서는 문제가 생기지 않았는데, 특정 개발 단계의 서버에서 다음과 같은 문제가 발생했다.

# stack_trace의 일부
...
Caused by: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
...
Caused by: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

could not extract ResultSet라는 메시지만 보았을 때에는 스프링 단에서 테이블을 제대로 참조하지 못하고 있을 것(엔티티로 매핑한 테이블의 이름과 일치하지 않다거나 컬럼의 이름이 일치하지 않는다거나..)이라는 생각을 하고 문제가 발생한 테이블의 컬럼을 살펴보았는데 잘못 판단했다.

 

could not extract ReulstSet이라는 메시지가 발생하게 된 근본적인 원인은 바로 다음에 나오는 Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='이라는 예외 때문이었다.

 

데이터베이스를 사용할 때 charset과 collation을 설정할 수 있는데, 전자의 경우는 문자열의 인코딩 타입을, 후자의 경우는 문자열의 비교 방식에 대한 정의하는 설정값이라고 보면 된다. 그리고 위의 예외에서 괄호 안에 들어있는 utf8_general_ci와 utf8mb4_general_ci가 바로 collation에 들어갈 수 있는 값이다.

문제 상황

영문자나 한글과 같은 문자의 경우 utf8 인코딩을 사용해서 표현할 수 있다. 비단 영어나 한글 외에도 세상에 존재하는 오만가지 언어들을 21비트의 자료형으로 표현할 수 있었기 때문에 MySQL에서는 utf8이라는 인코딩 타입을 3바이트의 크기로 지정하여 사용했다.

 

그러나 이모지와 같은 최근에 나온 문자열들 중에는 크기가 4바이트가 되는 것들이 존재했는데, MySQL에서는 이를 수용할 수 있는 새로운 자료형인 utf8mb4를 내놓게 된다. 과거부터 상용화된 서비스 중에서 MySQL을 데이터베이스로 사용하는 경우에는 기본적인 charset이 utf8, collation이 utf8_general_ci로 설정되어 있는 경우가 자주 있는데, 이런 경우 이모지와 같은 utf8mb4 타입의 데이터를 추가하는 경우에 위와 같은 예외 상황이 발생하게 된다.

해결하기

결과적으로 utf8 타입의 문자열과 utf8mb4 타입의 문자열을 비교하여 발생하는 문제이므로, 이를 해결하기 위해서는 4바이트의 인코딩 타입도 수용할 수 있는 utf8mb4 타입으로 테이블이나 컬럼의 charset을 변경해주면 된다.

# 시스템의 charset 및 collation의 name:value 확인
show variables LIKE 'c%';

MySQL의 show variables 명령어는 MySQL에서 사용하고 있는 시스템 변수의 값을 확인하고 싶을 때 사용한다. LIKE 'c%'를 사용하면 charset 및 collation에 대한 시스템 변수를 확인할 수 있다.

• character_set_server : DB 서버의 charset이다. 설정 파일(my.cnf)에서 명시한 값을 사용한다.
• character_set_database : 현재 사용중인 데이터베이스의 charset이다.
• character_set_client, character_set_connection, character_set_results : 클라이언트는 DB 서버와의 커넥션을 위해 자신이 사용할 수 있는 charset을 알려주는데, 이를 통해 서버에서 해당 시스템 변수의 값을 설정한다.
• collation_server : DB 서버의 collation이다. 설정 파일(my.cnf)에서 명시한 값을 사용한다.
• collation_database : 현재 사용중인 데이터베이스의 collation이다.
• collation_connection : 명시적인 설정값이 없다면 character_set_connection 설정에 따라 기본 collation이 설정된다.

시스템 변수 값에 대한 설명을 보면 DB 서버에서 설정하는 값이 전역 설정이며, 각 데이터베이스에 설정된 설정값이 지역 설정으로 작동한다는 것을 알 수 있다. 이는 데이터베이스와 테이블의 관계에서도 마찬가지로 적용되어 데이터베이스의 설정값이 있더라도 테이블에 별도로 설정값을 부여하면 해당 테이블에서는 지역 설정값을 따라가게 된다.

 

일단은 명령어를 사용해서 작업을 하려고 하는 데이터베이스에서 사용하는 charset을 확인하면 된다. 나의 경우에는 utf8mb4_general_ci를 사용하고 있었다.

# 현재 사용중인 db의 테이블 status 확인
show table status like 'channels';

# 현재 사용중인 db의 테이블 컬럼 상세 정보 확인
show full columns from channels;

그 뒤 문제가 발생한 테이블의 상태를 확인할 필요가 있다. 인코딩으로 인한 오류가 발생했기 때문에 해당 테이블의 charset이나 collation을 확인해야 하는데, 이 때 사용할 수 있는 SQL이 바로 위의 쿼리들이다.

 

테이블의 status를 확인했을 때, collation이 utf8 관련 값으로 설정되어 있었고(utf8_general_ci), 세부 컬럼 정보 역시 utf8로 설정되어 있었다. 따라서 utf8로 설정된 테이블 정보들을 utf8mb4를 사용하도록 변경하는 작업을 진행하면 된다.

# 테이블의 charset 및 collation 변경
alter table channels convert to character set utf8mb4 collate utf8mb4_general_ci;

# 테이블의 컬럼 charset 및 collation 변경
ALTER TABLE channels MODIFY token VARCHAR(1024) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE channels MODIFY type VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci;

테이블과 컬럼의 charset을 변경하는 SQL은 위와 같다. channels라는 테이블의 charset과 collation을 utf8mb4로 변경하는데, 시스템 변수를 통해 확인했을 때 DB 서버에서 collation을 utf8mb4_general_ci로 설정되어 있었기 때문에 collation을 이와 동일하게 맞추어 줬다. 이렇게 되면 channels라는 테이블에서 문자열을 저장하는 컬럼들의 charset과 collation이 모두 변경된다. show full columns from channels를 사용해보면 이를 확인할 수 있다.

 

그리고 나서 utf8mb4가 필요없는 몇 개의 컬럼들에게는 따로 기존 charset, collation을 사용하도록 변경해주었다. 다시 테스트를 해보니 정상적으로 작동하는 것을 확인할 수 있었다.