pynab:update - duplicate key value violates unique constraint "releases_uniqhash_key"
JameZUK opened this issue · 2 comments
I think this is a bug as it is the second time it has occurred. The bug stops the update service from running and constantly sends the following error to stderr.
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py:3779: SAWarning: Textual SQL expression 'rownum % 10000=1' should be explicitly declared as text('rownum % 10000=
1') (this warning may be suppressed after 10 occurrences)
{"expr": util.ellipses_string(element)})
Traceback (most recent call last):
File "/opt/pynab/scan.py", line 206, in
main(mode=mode, group=arguments[''], date=arguments['--date'])
File "/opt/pynab/scan.py", line 161, in main
process()
File "/opt/pynab/scan.py", line 76, in process
pynab.releases.process()
File "/opt/pynab/pynab/releases.py", line 366, in process
nzb = pynab.nzbs.create(release.search_name, parent_categories[release.category_id], binary)
File "/opt/pynab/pynab/nzbs.py", line 150, in create
for segment in part.segments:
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/attributes.py", line 237, in get
return self.impl.get(instance_state(instance), dict_)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/attributes.py", line 583, in get
value = self.callable_(state, passive)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/strategies.py", line 532, in _load_for_state
return self._emit_lazyload(session, state, ident_key, passive)
File "", line 1, in
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/strategies.py", line 602, in _emit_lazyload
result = q.all()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2588, in all
return list(self)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/query.py", line 2735, in iter
self.session._autoflush()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 1315, in _autoflush
util.raise_from_cause(e)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 183, in reraise
raise value
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 1305, in _autoflush
self.flush()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2027, in flush
self._flush(objects)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2145, in _flush
transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py", line 60, in exit
compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 183, in reraise
raise value
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2109, in _flush
flush_context.execute()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
rec.execute(self)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
uow
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
mapper, table, insert)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 800, in _emit_insert_statements
execute(statement, params)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 189, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 182, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
context)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/engine/default.py", line 450, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (psycopg2.Integr
ityError) duplicate key value violates unique constraint "releases_uniqhash_key"
DETAIL: Key (uniqhash)=(458d58a38b259e05147a1eed5a1eae7a8b6d01d9) already exists.
[SQL: 'INSERT INTO releases (uniqhash, added, posted, name, search_name, original_name, posted_by, status, grabs, size, passworded, unwanted, group_id, category_id, regex_id, tvs
how_id, tvshow_metablack_id, movie_id, movie_metablack_id, nzb_id, rar_metablack_id, nfo_id, nfo_metablack_id, sfv_id, sfv_metablack_id, episode_id, pre_id) VALUES (%(uniqhash)s,
now(), %(posted)s, %(name)s, %(search_name)s, %(original_name)s, %(posted_by)s, %(status)s, %(grabs)s, %(size)s, %(passworded)s, %(unwanted)s, %(group_id)s, %(category_id)s, %(reg
ex_id)s, %(tvshow_id)s, %(tvshow_metablack_id)s, %(movie_id)s, %(movie_metablack_id)s, %(nzb_id)s, %(rar_metablack_id)s, %(nfo_id)s, %(nfo_metablack_id)s, %(sfv_id)s, %(sfv_metabl
ack_id)s, %(episode_id)s, %(pre_id)s) RETURNING releases.id'] [parameters: {'posted_by': 'moovee@4u.tv (moovee)', 'movie_id': None, 'sfv_id': None, 'size': 14928144000, 'search_na
me': '155441', 'status': None, 'rar_metablack_id': None, 'sfv_metablack_id': None, 'original_name': '155441', 'pre_id': None, 'category_id': 8010, 'unwanted': False, 'nfo_metablac
k_id': None, 'passworded': 'UNKNOWN', 'nzb_id': None, 'uniqhash': '458d58a38b259e05147a1eed5a1eae7a8b6d01d9', 'tvshow_id': None, 'nfo_id': None, 'grabs': 0, 'episode_id': None, 'r
egex_id': 2334, 'movie_metablack_id': None, 'tvshow_metablack_id': None, 'name': '155441', 'posted': datetime.datetime(2016, 2, 1, 17, 2, 29), 'group_id': 36}]
/usr/local/lib/python3.4/dist-packages/sqlalchemy/sql/elements.py:3779: SAWarning: Textual SQL expression 'rownum % 10000=1' should be explicitly declared as text('rownum % 10000=
1') (this warning may be suppressed after 10 occurrences)
I have managed to fix the problem by manually removing the duplicate entry from the releases table.
Please let me know if you need anything further,
Thanks
I'll look into this. The uniqhash is based off binary name, group and date posted, so it's extremely unlikely to get duplicates unless you're re-scanning groups that you've already scanned over (in which case it shouldn't try to add the releases again, either).
Cheers, this does appear to be happening on a regular basis for me. Maybe its my news provider? When this happens, the indexer stops adding any releases. If I delete the entry from the DB, indexing starts again. If I do not remove the entry, even restarting pynab has no effect and indexing does not work.