duplicate key value violates unique constraint "exercises_deletionlog_uuid_2b4f5ae1_uniq"

[bbkz]

Hi,

i don't know how but i think the cause was my mistake, but i ended up with some problems with the exercises data.

duplicate key value violates unique constraint "exercises_deletionlog_uuid_2b4f5ae1_uniq"

Is there a way that i can clean these records properly?

wger@wger-app-7f8668d67f-5lvjs:~/src$ python3 manage.py exercises-health-check
INFO 2024-03-08 10:25:12,477 apps AXES: BEGIN version 6.3.0, blocking by ip_address
System check identified some issues:

WARNINGS:
?: (wger.W002) exercises without translations
        HINT: There are 6 exercises without translations, this will cause problems! You can output or delete them with "python manage.py exercises-health-check --help"
Exercise 66aba891-6433-4b67-a17f-5e5e14f22bf5 has no translations!
Exercise 66aba891-6433-4b67-a17f-5e5e14f22bf5 has no English translation!
Exercise 574de2e7-e3d8-4e9d-afcd-0e3a56ce6f9d has no translations!
Exercise 574de2e7-e3d8-4e9d-afcd-0e3a56ce6f9d has no English translation!
Exercise 4f92c5d1-bba7-47aa-a4fe-c03176dfb70e has no translations!
Exercise 4f92c5d1-bba7-47aa-a4fe-c03176dfb70e has no English translation!
Exercise e14e0049-6167-468c-a09e-3445ae87b9d4 has no translations!
Exercise e14e0049-6167-468c-a09e-3445ae87b9d4 has no English translation!
Exercise d48d1597-9e38-4219-81e0-61f46715a474 has no translations!
Exercise d48d1597-9e38-4219-81e0-61f46715a474 has no English translation!
Exercise 0c8105b0-6022-4aa8-b847-c0754185361c has no translations!
Exercise 0c8105b0-6022-4aa8-b847-c0754185361c has no English translation!

wger@wger-app-7f8668d67f-5lvjs:~/src$ python3 manage.py exercises-health-check --delete-untranslated
INFO 2024-03-08 10:14:22,302 apps AXES: BEGIN version 6.3.0, blocking by ip_address
System check identified some issues:

WARNINGS:
?: (wger.W002) exercises without translations
        HINT: There are 6 exercises without translations, this will cause problems! You can output or delete them with "python manage.py exercises-health-check --help"
Exercise 66aba891-6433-4b67-a17f-5e5e14f22bf5 has no translations!
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/dist-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "exercises_deletionlog_uuid_2b4f5ae1_uniq"
DETAIL:  Key (uuid)=(66aba891-6433-4b67-a17f-5e5e14f22bf5) already exists.


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

Traceback (most recent call last):
  File "/home/wger/src/manage.py", line 24, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.10/dist-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.10/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.10/dist-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.10/dist-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/home/wger/src/wger/exercises/management/commands/exercises-health-check.py", line 92, in handle
    self.handle_untranslated(base, delete_untranslated)
  File "/home/wger/src/wger/exercises/management/commands/exercises-health-check.py", line 105, in handle_untranslated
    base.delete()
  File "/home/wger/src/wger/exercises/models/base.py", line 265, in delete
    log.save()
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/base.py", line 814, in save
    self.save_base(
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/base.py", line 877, in save_base
    updated = self._save_table(
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/base.py", line 1020, in _save_table
    results = self._do_insert(
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/base.py", line 1061, in _do_insert
    return manager._insert(
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/query.py", line 1805, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/usr/local/lib/python3.10/dist-packages/django/db/models/sql/compiler.py", line 1822, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/dist-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/local/lib/python3.10/dist-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/dist-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/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.10/dist-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "exercises_deletionlog_uuid_2b4f5ae1_uniq"
DETAIL:  Key (uuid)=(66aba891-6433-4b67-a17f-5e5e14f22bf5) already exists.

[rolandgeider]

hey! Mhhm, the UUID should be unique in the exercise table as well, why hasn't it made trouble there as well? In this case you can just delete the entry from the deletion log (this is basically only used when other servers sync from your instance):

docker compose exec db psql wger -U wger -c "DELETE FROM exercises_deletionlog WHERE uuid = '66aba891-6433-4b67-a17f-5e5e14f22bf5';"

[bbkz]

Hoi, thank you! Just removed the record, now it works just fine.

[rolandgeider]

awesome! but it's still something that shouldn't happen 😅 How exactly did it break in the first place? Just by synchronizing?

[bbkz]

I don't know... i had it a long time but never cared to have a look at it. From what i remember i had two major things i did with the data, that could fit into that time.

• upgrade from postgres 14 -> 15, here i missed dumping and started 15 with the data then dumped with 14 and imported to 15
• i remember that several times the exercise sync crashed as well

[bbkz]

Sorry i have to reopen the issue.

I can now reproduce the problem. After removing the records in exercises_deletionlog, i have a clean

python3 manage.py exercises-health-check
#after using
#python3 manage.py exercises-health-check --delete-untranslated

But when i run a

python3 manage.py sync-exercises

i start having 10 (now 11) exercices without english or translation

Exercise 0c8105b0-6022-4aa8-b847-c0754185361c has no translations!
Exercise 0c8105b0-6022-4aa8-b847-c0754185361c has no English translation!
...

these (but not all i think), then start having the constraint problem

django.db.utils.IntegrityError: duplicate key value violates unique constraint "exercises_deletionlog_uuid_2b4f5ae1_uniq"
DETAIL:  Key (uuid)=(0c8105b0-6022-4aa8-b847-c0754185361c) already exists

This also happens when celery is making the sync.

[rolandgeider]

the problem that you get exercises without translations is fixed now (these already didn't have any translations on wger.de and were being correctly sync'ed, this is probably due to a bug in the submission page). I guess we could add some checks so that if the UUID is already in the deletion log, we don't try to add it again, but ideally we wouldn't try to add it twice