UNIQUE constraint failed: events.id

Swallowtail23 commented 1 year ago

Unifi Protect Backup version: 0.8.3
Unifi Protect version: UNVR 3.0.13, Protect 2.6.17
Python version:
Operating System: RH8
Are you using a docker container or native?: Container

Description

Non unique violation errors

What I Did

Strange sequence... Why would it have downloaded/uploaded the same event twice? Presumably at some point something has failed and it is retrying, but the fail/catch/retry is ending up in a traceback, not an error routine.

2022-12-09T11:53:45.673871358+10:00 stderr F 2022-12-09 01:53:45 [   INFO    ] unifi_protect_backup.downloader            : [6392951b00dabf03e40b7a63]  Downloading event: 6392951b00dabf03e40b7a63
2022-12-09T11:53:55.340109434+10:00 stderr F 2022-12-09 01:53:55 [   INFO    ] unifi_protect_backup.uploader              : [6392950f029abf03e40b7a5a]  Uploading event: 6392950f029abf03e40b7a5a
2022-12-09T11:54:12.468989778+10:00 stderr F 2022-12-09 01:54:12 [  WARNING  ] unifi_protect_backup.missing_event_checker :   Adding missing event to backup queue: 6392950f029abf03e40b7a5a (person) (2022-12-09T01-53-16 - 2022-12-09T01-53-31)
2022-12-09T11:54:12.469073497+10:00 stderr F 2022-12-09 01:54:12 [  WARNING  ] unifi_protect_backup.missing_event_checker :   Adding missing event to backup queue: 6392951b00dabf03e40b7a63 (person) (2022-12-09T01-53-28 - 2022-12-09T01-53-39)
2022-12-09T11:54:12.469218014+10:00 stderr F 2022-12-09 01:54:12 [   INFO    ] unifi_protect_backup.downloader            : [6392950f029abf03e40b7a5a]  Downloading event: 6392950f029abf03e40b7a5a
2022-12-09T11:54:13.220555489+10:00 stderr F 2022-12-09 01:54:13 [   INFO    ] unifi_protect_backup.downloader            : [6392951b00dabf03e40b7a63]  Downloading event: 6392951b00dabf03e40b7a63
2022-12-09T11:54:17.424767356+10:00 stderr F 2022-12-09 01:54:17 [   INFO    ] unifi_protect_backup.uploader              : [6392951b00dabf03e40b7a63]  Uploading event: 6392951b00dabf03e40b7a63
2022-12-09T11:54:29.399325998+10:00 stderr F 2022-12-09 01:54:29 [   INFO    ] unifi_protect_backup.uploader              : [6392950f029abf03e40b7a5a]  Uploading event: 6392950f029abf03e40b7a5a
2022-12-09T11:54:52.296019582+10:00 stderr F 2022-12-09 01:54:52 [  WARNING  ] unifi_protect_backup.uploader              : [6392950f029abf03e40b7a5a]  Unexpected exception occurred, abandoning event 6392950f029abf03e40b7a5a:
2022-12-09T11:54:52.296395776+10:00 stderr F 2022-12-09 01:54:52 [   ERROR   ] unifi_protect_backup.uploader              : [6392950f029abf03e40b7a5a]  UNIQUE constraint failed: events.id
2022-12-09T11:54:52.296395776+10:00 stderr F Traceback (most recent call last):
2022-12-09T11:54:52.296395776+10:00 stderr F   File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 59, in start
2022-12-09T11:54:52.296395776+10:00 stderr F     await self._update_database(event, destination)
2022-12-09T11:54:52.296395776+10:00 stderr F   File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 89, in _update_database
2022-12-09T11:54:52.296395776+10:00 stderr F     await self._db.execute(
2022-12-09T11:54:52.296395776+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 184, in execute
2022-12-09T11:54:52.296395776+10:00 stderr F     cursor = await self._execute(self._conn.execute, sql, parameters)
2022-12-09T11:54:52.296395776+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 129, in _execute
2022-12-09T11:54:52.296395776+10:00 stderr F     return await future
2022-12-09T11:54:52.296395776+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 102, in run
2022-12-09T11:54:52.296395776+10:00 stderr F     result = function()
2022-12-09T11:54:52.296395776+10:00 stderr F sqlite3.IntegrityError: UNIQUE constraint failed: events.id
2022-12-09T11:54:52.297514968+10:00 stderr F 2022-12-09 01:54:52 [   INFO    ] unifi_protect_backup.uploader              : [6392951b00dabf03e40b7a63]  Uploading event: 6392951b00dabf03e40b7a63
2022-12-09T11:55:03.975104446+10:00 stderr F 2022-12-09 01:55:03 [  WARNING  ] unifi_protect_backup.uploader              : [6392951b00dabf03e40b7a63]  Unexpected exception occurred, abandoning event 6392951b00dabf03e40b7a63:
2022-12-09T11:55:03.975256744+10:00 stderr F 2022-12-09 01:55:03 [   ERROR   ] unifi_protect_backup.uploader              : [6392951b00dabf03e40b7a63]  UNIQUE constraint failed: events.id
2022-12-09T11:55:03.975256744+10:00 stderr F Traceback (most recent call last):
2022-12-09T11:55:03.975256744+10:00 stderr F   File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 59, in start
2022-12-09T11:55:03.975256744+10:00 stderr F     await self._update_database(event, destination)
2022-12-09T11:55:03.975256744+10:00 stderr F   File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 89, in _update_database
2022-12-09T11:55:03.975256744+10:00 stderr F     await self._db.execute(
2022-12-09T11:55:03.975256744+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 184, in execute
2022-12-09T11:55:03.975256744+10:00 stderr F     cursor = await self._execute(self._conn.execute, sql, parameters)
2022-12-09T11:55:03.975256744+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 129, in _execute
2022-12-09T11:55:03.975256744+10:00 stderr F     return await future
2022-12-09T11:55:03.975256744+10:00 stderr F   File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 102, in run
2022-12-09T11:55:03.975256744+10:00 stderr F     result = function()
2022-12-09T11:55:03.975256744+10:00 stderr F sqlite3.IntegrityError: UNIQUE constraint failed: events.id
2022-12-09T11:58:23.248944369+10:00 stderr F 2022-12-09 01:58:23 [   INFO    ] unifi_protect_backup.downloader            : [6392963400bebf03e40b7bf6]  Downloading event: 6392963400bebf03e40b7bf6
2022-12-09T11:58:24.470889039+10:00 stderr F 2022-12-09 01:58:24 [   INFO    ] unifi_protect_backup.uploader              : [6392963400bebf03e40b7bf6]  Uploading event: 6392963400bebf03e40b7bf6
2022-12-09T13:25:26.660186610+10:00 stderr F 2022-12-09 03:25:26 [   INFO    ] unifi_protect_backup.downloader            : [6392aa83002dbf03e40b9890]  Downloading event: 6392aa83002dbf03e40b9890
2022-12-09T13:25:27.780016512+10:00 stderr F 2022-12-09 03:25:27 [   INFO    ] unifi_protect_backup.uploader              : [6392aa83002dbf03e40b9890]  Uploading event: 6392aa83002dbf03e40b9890

It would also be useful to tag an event like this with the filename that was to be written, so it can be checked for successful upload.

ep1cman commented 1 year ago

Ok I think I know whats going on here. Currently the way the list of "missing" events is generated is as follows:

Fetch all events within the retention period from unifi protect
Remove any events which are already in the database
Remove any events which are currently in the download queue
Remove any events which are not one of our currently wanted event types

What has happened in your case, event 6392950f029abf03e40b7a5a has been popped off the download queue and is currently being uploaded and while thats happening the periodic missing check was triggered and it was not in the queue so was considered missing and re-added.

I will make a pass through this logic to make it more robust

ep1cman commented 1 year ago

Should be fixed in v0.8.4, feel free to re-open if you still get issues.

Container build should be available shortly.

As for your comment about file path being shown. If you run with -v it will be shown.

Swallowtail23 commented 8 months ago

I haven't updated to the new .10 versions yet, but I'm getting unique constraint issues again:

ERROR: 2023-11-04 09:19:04 [   ERROR   ] unifi_protect_backup.uploader              : [65457fce036fb703e401cc26]  Unexpected exception occurred, abandoning event 65457fce036fb703e401cc26:
Traceback (most recent call last):
  File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 78, in start
    await self._update_database(event, destination)
  File "/usr/lib/python3.10/site-packages/unifi_protect_backup/uploader.py", line 108, in _update_database
    await self._db.execute(
  File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 184, in execute
    cursor = await self._execute(self._conn.execute, sql, parameters)
  File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 129, in _execute
    return await future
  File "/usr/lib/python3.10/site-packages/aiosqlite/core.py", line 102, in run
    result = function()
sqlite3.IntegrityError: UNIQUE constraint failed: events.id

Unifi Protect Backup version: 0.9.3 Unifi Protect version: UNVR 3.1.16, Protect 2.9.33 Are you using a docker container or native?: Container

ep1cman / unifi-protect-backup

UNIQUE constraint failed: events.id #63

Description

What I Did