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