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) 커맨드에도 옵션 삽입