migration fails on upgrade

[jxbolt]

Upgraded to latest docker and there's an error about not being able to create a unique index in common_taskhistory for the unique task_id index. There are many duplicates in the table from exists tasks that were processing when I did the upgrade. What should my next step be to resolve the duplicates?

This is the container log when it started up after the upgrade. I tried removing some duplicates but just kept finding more.

s6-rc: info: service s6rc-oneshot-runner: starting
s6-rc: info: service s6rc-oneshot-runner successfully started
s6-rc: info: service fix-attrs: starting
s6-rc: info: service tubesync-init: starting
s6-rc: info: service fix-attrs successfully started
s6-rc: info: service legacy-cont-init: starting
s6-rc: info: service legacy-cont-init successfully started
2025-07-16 19:28:51,739 [tubesync/INFO] Using database connection: postgresql://tubesync:[hidden]@192.168.9.81:5433/tubesync
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: could not create unique index "common_taskhistory_task_id_5601b8bb_uniq"
DETAIL:  Key (task_id)=(85215e51-71e7-4a68-9faa-c04252763843) is duplicated.
CONTEXT:  parallel worker

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/manage.py", line 18, in <module>
    main()
  File "/app/manage.py", line 14, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/base.py", line 416, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/core/management/commands/migrate.py", line 353, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
TUBESYNC_RESET_DOWNLOAD_DIR=True, Resetting /downloads directory permissions
Operations to perform:
  Apply all migrations: admin, auth, background_task, common, contenttypes, sessions, sync
Running migrations:
            ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/migrations/executor.py", line 255, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.11/dist-packages/django/db/migrations/operations/fields.py", line 241, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/base/schema.py", line 912, in alter_field
    self._alter_field(
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/postgresql/schema.py", line 274, in _alter_field
    super()._alter_field(
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/base/schema.py", line 1208, in _alter_field
    self.execute(self._create_unique_sql(model, [new_field]))
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/base/schema.py", line 204, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.11/dist-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.11/dist-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/dist-packages/psycopg/cursor.py", line 97, in execute
    raise ex.with_traceback(None)
django.db.utils.IntegrityError: could not create unique index "common_taskhistory_task_id_5601b8bb_uniq"
DETAIL:  Key (task_id)=(85215e51-71e7-4a68-9faa-c04252763843) is duplicated.
CONTEXT:  parallel worker
s6-rc: warning: unable to start service tubesync-init: command exited 1

[tcely]

See:

• Error: get() returned more than one TaskHistory -- it returned 2 #1220 (comment)

You will need to remove the duplicate rows.
Here is some SQL to do that for you.

Probably works (very slowly) on any SQL database server

DELETE FROM common_taskhistory
WHERE id NOT IN (
    SELECT MAX(id) 
    FROM common_taskhistory 
    GROUP BY task_id
);

Specific examples for each database server

SQLite ¹

DELETE FROM common_taskhistory
WHERE id NOT IN (
    SELECT MAX(id) 
    FROM common_taskhistory 
    GROUP BY task_id
);

PostgreSQL ²

DELETE FROM
    "common_taskhistory" a
        USING "common_taskhistory" b
WHERE
    a.id < b.id
    AND a.task_id = b.task_id;

MySQL ³

DELETE a
  FROM common_taskhistory AS a
    INNER JOIN common_taskhistory AS b
WHERE
    a.id < b.id
    AND a.task_id = b.task_id;

Using Python shell

From the shell inside the TubeSync container you would run this:

$ python3 /app/manage.py shell

Then once the Python shell is ready you can paste this code:

from django import db
from common.logger import log
from common.models import TaskHistory

duplicates = set(
    TaskHistory.objects.values(
        'task_id',
    ).alias(
        count=db.models.Count('id'),
    ).filter(
        count__gt=1,
    ).values_list(
        'task_id',
        flat=True,
    ),
)

def keep_which(task_id):
    th_qs = TaskHistory.objects.all().order_by('-id')
    tqs = th_qs.filter(task_id=task_id)
    return tqs[0].id

log.info(f'{len(duplicates)=}')
for task_id in duplicates:
    keeping = keep_which(task_id)
    log.debug(f'{task_id=}: {keeping=}')
    TaskHistory.objects.filter(
        task_id=task_id,
    ).exclude(
        id=keeping,
    ).delete()


log.info('Done!')

Here is an example of the output when a duplicate is found:

2025-07-17 05:13:25,511 [tubesync/DEBUG] task_id='9b1734e8-38fe-4c7b-b7e0-b7dc49aaf585': keeping=389143

Footnotes

1. https://medium.com/@charlesprime73/removing-duplicate-rows-from-an-sqlite-database-1946f26ea9f6 ↩

2. https://neon.com/postgresql/postgresql-tutorial/how-to-delete-duplicate-rows-in-postgresql ↩

3. https://blog.devart.com/delete-duplicate-rows-in-mysql.html ↩

[jxbolt]

That worked using the postegresql script. thank you VERY much for the quick response.

[meeb]

I'll close this as it seems resolved. Thanks!