Mysql.connector.errors.DatabaseError: 1267 (HY000): Illegal mix of collations

1 개요[ | ]

mysql.connector.errors.DatabaseError: 1267 (HY000): Illegal mix of collations

Console

Copy

Traceback (most recent call last):
  File "hello.py", line 116, in <module>
    oldrow = query_row('SELECT * FROM articles WHERE name=%s AND repo_id=%s', (name, repo_id) )
  File "hello.py", line 30, in query_row
    rows = query_rows(sql,params)
  File "hello.py", line 25, in query_rows
    mycursor.execute(sql,params)
  File "/root/.local/lib/python3.8/site-packages/mysql/connector/cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/root/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "/root/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.DatabaseError: 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

→ mysql.connector는 MySQL 접속시에 utf8_general_ci을 기본 COLLATION으로 하는데. 비교 대상이 되는 컬럼의 COLLATION인 utf8mb4_general_ci와 맞지 않다.

2 해결[ | ]

COLLATION을 utf8mb4_general_ci으로 명시해주었다.

Python

Copy

import mysql.connector
cnx = mysql.connector.connect(
        host = os.getenv('DB_HOST'),
        port = os.getenv('DB_PORT'),
        user = os.getenv('DB_USER'),
        passwd = os.getenv('DB_PASS'),
        database = os.getenv('DB_DATABASE')
)
cnx.set_charset_collation('utf8mb4', 'utf8mb4_general_ci')

3 같이 보기[ | ]

mysql.connector