[MariaDB] Illegal mix of collations (euckr_korean_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'like'

[Error Message]

Illegal mix of collations (euckr_korean_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation 'like'

 

[해결]

기존 서버와 다르게 세팅되어 있는 부분들을 찾아보았다.

 

정상 서버의 경우

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | euckr                      |
| character_set_connection | euckr                      |
| character_set_database   | euckr                      |
| character_set_filesystem | binary                     |
| character_set_results    | euckr                      |
| character_set_server     | euckr                      |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | euckr_korean_ci            |
| collation_database       | euckr_korean_ci            |
| collation_server         | euckr_korean_ci            |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+
14 rows in set (0.00 sec)

위 에러메세지가 발생하는 서버의 경우

latin1 들이 다름을 확인할 수 있음.

그럼 해당 설정 값을 수정해볼까.

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | euckr                      |
| character_set_connection | euckr                      |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | euckr                      |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | euckr_korean_ci            |
| collation_database       | latin1_swedish_ci          |
| collation_server         | latin1_swedish_ci          |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+

(1) /etc/init.d/my.cnf 를 copy해온 후 db 재시작

scp -l 30000 -rp id@ip:/etc/my.cnf /etc/my.cnf

$ /etc/init.d/mysql restart

그래도 값을 조회하면 동일함

 

(2) 직접 수정

MariaDB [(none)]> set character_set_database = euckr;
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> set character_set_server = euckr;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set collation_server = euckr_korean_ci;
Query OK, 0 rows affected (0.00 sec)

조회하니 수정 되어있음

MariaDB [(none)]> show variables like 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | euckr                      |
| character_set_connection | euckr                      |
| character_set_database   | euckr                      |
| character_set_filesystem | binary                     |
| character_set_results    | euckr                      |
| character_set_server     | euckr                      |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
| collation_connection     | euckr_korean_ci            |
| collation_database       | euckr_korean_ci            |
| collation_server         | euckr_korean_ci            |
| completion_type          | NO_CHAIN                   |
| concurrent_insert        | AUTO                       |
| connect_timeout          | 10                         |
+--------------------------+----------------------------+

그러나 문제는 toad에서 조회해보니 여전하다는것

character_set_client	utf8
character_set_connection	utf8
character_set_database	latin1
character_set_filesystem	binary
character_set_results	utf8
character_set_server	euckr
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/
collation_connection	utf8_general_ci
collation_database	latin1_swedish_ci
collation_server	euckr_korean_ci
completion_type	NO_CHAIN
concurrent_insert	AUTO
connect_timeout	10

어디서 보니 이미 생성된 database는 변경되지 않는다는 것을 봐버림,,,

초기 단계니까 database 드랍했다가 다시 create해야 하나 생각중......................하 ㅠ