1. 현상
mysql 커맨드를 이용하여 데이터를 import 할 때,
- ERROR 1273 (HY000) at line 2116: Unknown collation: 'utf8mb4_0900_ai_ci'
- ERROR 1253 (42000) at line 2116: COLLATION 'utf8mb3_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'
2. 해결책
2.1. Parameter Group 설정
MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8mb3 | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--------------------------+-------------------+ 10 rows in set (0.008 sec)
다음 내용을 갖는 Parameter Group을 생성하고 MarIaDB 인스턴스에 적용한다.
character_set_client = utf8mb4 character_set_connection = utf8mb4 character_set_database = utf8mb4 character_set_filesystem = utf8mb4 character_set_results = utf8mb4 character_set_server = utf8mb4 collation_server = utf8mb4_general_ci collation_connection = utf8mb4_general_ci
DB를 다시 시작하고 내부 정보를 조회한다.
MariaDB [(none)]> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | utf8mb4 | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb3 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +--------------------------+--------------------+ 10 rows in set (0.009 sec)
2.2. mysql 커맨드 라인 옵션 삽입
-
mysql 커맨드 실행 시
--default-character-set=utf8mb4
삽입 - export / import 시에는 export (mysqldump) 커맨드에도 옵션 삽입