본문 바로가기
카테고리 없음

[mariaDB] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

by 언제나초심. 2021. 11. 25.
반응형

개요

MariaDB 를 'utf8mb4_unicode_ci'로 생성을 했고 (정확히는 도커 컨테이너로 생성), 클라이언트 툴로 접속을 했는데. Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='라는 오류를 자꾸 만났다.

분석

조인 조건에 있는 문자셋이 달라서 발생한다고 하는 말이 있다. (stackoverflow.com 에서 찾음. 링크는 저장해두지않아서 없음..)

그러면, 문자셋을 확인해보자...

show variables like 'char%';
show variables like 'collat%';
show variables like 'init%';

결과

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database    utf8mb3
character_set_filesystem    binary
character_set_results    utf8mb4
character_set_server    utf8mb4
character_set_system    utf8mb3

collation_connection    utf8mb4_general_ci
collation_database    utf8mb3_general_ci
collation_server    utf8mb4_unicode_ci

collation_connection 이 utf8mb4_general_ci 로 되어 있다. 음... 데이터베이스 생성시에는 utf8mb4_unicode_ci 로 했는데. 어떤 상황에서 general_ci 가 있어서 혼란이 발생한 것으로 추측된다.

찾아보자..

음?

collation_connection 이 utf8mb4_general_ci로 되어있다. (collation_database 는 나중에 생각해보기로 하고)

쿼리 로그를 살펴보면. 

SELECT user as user, host as host, plugin, authentication_string, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, super_priv, max_user_connections, default_role FROM mysql.user WHERE is_role = 'N' ORDER BY user

Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE) and (utf8mb4_general_ci,COERCIBLE) for operation '='

여기서 발생했다.

즉, 사용중이던 클라이언트 툴로 접속했는데, 접속하자마자 user 를 확인하는 부분에서 문제가 생긴 것.

왜?

여기서 조인 조건은 is_role = 'N'인데, 여기서 이상이 생겼을 것으로 짐작이 된다.

줄여서 보면

SELECT user as user, host as host FROM mysql.user WHERE is_role = 'N'; 이 부분인 것.

스키마를 확인해보자. 뭔가 쎄하다. 이럴리가 없는데?

-- 스키마 확인
SELECT default_character_set_name, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA 
WHERE schema_name = "mysql";


-- 테이블 확인
SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "mysql"
  AND T.table_name = "user";


-- 테이블 확인. 간단히.
SELECT * FROM information_schema.TABLES
WHERE TABLE_NAME = 'user';

음... 없네. 테이블이... (덤덤..) 어? 테이블이 없어?

mariaDB 10.4 이후로는 mysql.user 가 테이블이 아니고 view 라고 한다. https://gilchris.tistory.com/28

클라이언트 도구로 view 부분을 보니 'user'가 있다.

'user'라는 view를 생성하는 구문을 살펴보면

생성 구문은 다음과 같다. (mariadb 10.6)

select `global_priv`.`Host` AS `Host`,`global_priv`.`User` AS `User`,if(json_value(`global_priv`.`Priv`,'$.plugin') in ('mysql_native_password','mysql_old_password'),ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),''),'') AS `Password`,if(json_value(`global_priv`.`Priv`,'$.access') & 1,'Y','N') AS `Select_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2,'Y','N') AS `Insert_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4,'Y','N') AS `Update_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8,'Y','N') AS `Delete_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16,'Y','N') AS `Create_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32,'Y','N') AS `Drop_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 64,'Y','N') AS `Reload_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 128,'Y','N') AS `Shutdown_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 256,'Y','N') AS `Process_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 512,'Y','N') AS `File_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1024,'Y','N') AS `Grant_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2048,'Y','N') AS `References_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4096,'Y','N') AS `Index_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8192,'Y','N') AS `Alter_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16384,'Y','N') AS `Show_db_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 32768,'Y','N') AS `Super_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 65536,'Y','N') AS `Create_tmp_table_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 131072,'Y','N') AS `Lock_tables_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 262144,'Y','N') AS `Execute_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 524288,'Y','N') AS `Repl_slave_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 1048576,'Y','N') AS `Repl_client_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 2097152,'Y','N') AS `Create_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 4194304,'Y','N') AS `Show_view_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 8388608,'Y','N') AS `Create_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 16777216,'Y','N') AS `Alter_routine_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 33554432,'Y','N') AS `Create_user_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 67108864,'Y','N') AS `Event_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 134217728,'Y','N') AS `Trigger_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 268435456,'Y','N') AS `Create_tablespace_priv`,if(json_value(`global_priv`.`Priv`,'$.access') & 536870912,'Y','N') AS `Delete_history_priv`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.ssl_type'),0) + 1,'','ANY','X509','SPECIFIED') AS `ssl_type`,ifnull(json_value(`global_priv`.`Priv`,'$.ssl_cipher'),'') AS `ssl_cipher`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_issuer'),'') AS `x509_issuer`,ifnull(json_value(`global_priv`.`Priv`,'$.x509_subject'),'') AS `x509_subject`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_questions'),0) as unsigned) AS `max_questions`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_updates'),0) as unsigned) AS `max_updates`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_connections'),0) as unsigned) AS `max_connections`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_user_connections'),0) as signed) AS `max_user_connections`,ifnull(json_value(`global_priv`.`Priv`,'$.plugin'),'') AS `plugin`,ifnull(json_value(`global_priv`.`Priv`,'$.authentication_string'),'') AS `authentication_string`,if(ifnull(json_value(`global_priv`.`Priv`,'$.password_last_changed'),1) = 0,'Y','N') AS `password_expired`,elt(ifnull(json_value(`global_priv`.`Priv`,'$.is_role'),0) + 1,'N','Y') AS `is_role`,ifnull(json_value(`global_priv`.`Priv`,'$.default_role'),'') AS `default_role`,cast(ifnull(json_value(`global_priv`.`Priv`,'$.max_statement_time'),0.0) as decimal(12,6)) AS `max_statement_time` from `global_priv`

살펴보면.. global_priv.priv 를 json_value 로 값을 가져오는 부분이 많은 듯.

이거는 mysql.global_priv 테이블을 말하는 듯. 여기에 priv 컬럼을 보면 access, plugin:"mysql_native_password", authentication_string, password_last_changed, version_id 값이 json 형태로 들어있다.
그러면 $.access 로 json_value 를 했을 때, access 값을 가져올 듯 하고. 이 함수에서 뭔가 조치를 더 하는 거 아닐까 싶다. &1, & 128 이런식으로 하는 걸로 봐서는... 믹싱한 후에 하나의 값으로 입력된 듯하다.. (헐..)

elt( ifnull( json_value( global_priv.Priv, '$.is_role' ), 0 ) + 1, 'N', 'Y' ) AS is_role, 로 되어있는데.. 음...

  • global_priv.Priv 컬럼에서 json 값 중에 'is_role' 값을 가져옴. 없으면 0 값.
  • 여기서 1을 더함. 없으면 1이고 있으면 아마도 2가 나오게 될 듯.
  • elt 로 없거나 0이었으면 'N', 1이었다면 'Y' 출력.

elt 함수는

json_value 함수는

global_priv 테이블은 컬럼마다 다르게 collation이 되어있는데 Priv 컬럼은 'utf8mb4_bin'으로 되어 있음. 그러면 뭐가 문제인가?

원인의 예상

connection에서 사용하고 있는 charset의 영향을 받는 것이 아닐까 생각된다. json_value 함수를 통과해야하는데, 여기서 영향이 있는 것이 아닐까 짐작됨.

사실은 아마도 큰 차이는 아닐 것이고, 그냥 조인절에서 다른 charset 끼리 하고 있다고 경고를 날리는 것이 아닐까 생각됨.

해결점

혹시, 콘솔에서 SET collation_connection = utf8mb4_unicode_ci; 를 하면 좀 달라지나? => 된다.

해결책 1. 콘솔에서 'SET collation_connection = utf8mb4_unicode_ci;'을 먼저 실행한다.

해결책 2. ini 설정을 건드려본다.

[mysqld] 
character-set-server=utf8mb4 
collation-server=utf8mb4_unicode_ci 
init_connect=SET collation_connection = utf8mb4_unicode_ci 
# init_connect=SET NAMES utf8mb4

해결책 3. 실행 옵션을 건드려본다.
'''
--init-connect='SET collation_connection=utf8mb4_unicode_ci'
'''

해결책 4. 도커에서 커맨드를 건드려본다. (이거 되던가? 기억이 안 남.. 다른 식으로 해결봤기 때문에..)

    command: 
      - --character-set-server=utf8mb4 
      - --collation-server=utf8mb4_unicode_ci 
      - --skip-character-set-client-handshake
      - --init-connect='SET collation_connection=utf8mb4_unicode_ci'

엉뚱한 해결책

그냥 클라이언트 툴에서 접속 옵션 같은 곳에서 '65001 (UTF-8)'로 설정할 수 있다면, 설정을 하면 해결이 된다. (어...?)

알게된 점

  1. mariaDB 에서 'user'테이블은 테이블이 아니고 'view'로 대체되었다.
  2. elt, json_value 함수
  3. 마리아DB의 옵션(https://mariadb.com/kb/en/mysqld-options/)을 확인해서 적용할 수 있다.
    • commaindline이라고 표시된 것만 사용 가능하다. 이 항목이 없으면, 설정파일에서는 사용할 수 있고, 명령어 옵션으로는 안 되는 듯.
    • init-connect 명령어 옵션은 여러모로 유용할 듯 하다.
반응형
저작자표시 비영리

티스토리툴바