walkingmask’s development log

IT系の情報などを適当に書いていきます

MENU

Django - MySQL Connector/Python で INSERT IGNORE できない問題

最新のもの使えば起きなかった問題かもしれないが、起きたものは起きたので、一応メモ。

各種バージョンは以下の通り。

エラーは次のようなものが吐き出される。

  File "/usr/local/lib/python3.6/site-packages/mysql/connector/django/base.py", line 176, in _execute_wrapper
    return method(query, args)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 675, in executemany
    self.execute(operation, params)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 485, in _handle_result
    self._handle_noresultset(result)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 455, in _handle_noresultset
    self._warnings[0][1], self._warnings[0][2])
mysql.connector.errors.DatabaseError: 1062: Duplicate entry 'fd0236bd8903d242f222f3f2a72d1618356c34fc6a800de9ee0656bfe29be9b0' for key 'hashed_key'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/app/app/management/commands/command.py", line 203, in exec
    cursor.executemany(self.query, values)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 97, in executemany
    return super(CursorDebugWrapper, self).executemany(sql, param_list)
  File "/usr/local/lib/python3.6/site-packages/django/db/backends/utils.py", line 70, in executemany
    return self.cursor.executemany(sql, param_list)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/django/base.py", line 234, in executemany
    return self._execute_wrapper(self.cursor.executemany, query, args)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/django/base.py", line 194, in _execute_wrapper
    utils.DatabaseError(err.msg), sys.exc_info()[2])
  File "/usr/local/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/django/base.py", line 176, in _execute_wrapper
    return method(query, args)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 675, in executemany
    self.execute(operation, params)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 569, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 485, in _handle_result
    self._handle_noresultset(result)
  File "/usr/local/lib/python3.6/site-packages/mysql/connector/cursor.py", line 455, in _handle_noresultset
    self._warnings[0][1], self._warnings[0][2])
django.db.utils.DatabaseError: Duplicate entry 'fd0236bd8903d242f222f3f2a72d1618356c34fc6a800de9ee0656bfe29be9b0' for key 'hashed_key'

self.query はもちろん INSERT IGNORE INTO... なので、こんなエラーは起きないと思っちゃう。

しかし、IGNORE 文は、MySQL では例外を警告をに変えるものらしい。

https://dev.mysql.com/doc/refman/5.7/en/insert.html

If you use the IGNORE modifier, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors generate warnings instead.

そして、MySQL Connector/Python には次のようなオプションがある。

https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html

  • get_warnings: Whether to fetch warnings. (default: False)
  • raise_on_warnings: Whether to raise an exception on warnings. (default: False)

デフォルトでオフになってるけどもしやと思って、Djangoのsettingsに次のように記述。

DATABASES = {
    'default': {
        'ENGINE'  : 'mysql.connector.django',
        ...,
        'OPTIONS': {
          'get_warnings': False,
          'raise_on_warnings': False,
        },
    }
}

上記エラーが表示されなくなった。

ただし、これだとMySQLが吐き出す他の警告も抑えられてしまうので、望ましい挙動ではないのだけれど、とりあえず原因はわかったのでここまで。