[sync] IntegrityError: duplicate key value violates unique constraint "entity_locale_active"

[mathjazz]

This issue was created automatically by a script.

Bug 1703666

Bug Reporter: @mathjazz CC: @flodolo

An IntegrityError can appear during sync, which prevents the sync job to complete.

The only way to resolve the issue it to manually remove the conflicting active translation from Pontoon (by either deleting, rejecting or unapproving it).

Traceback (most recent call last):
File "/app/.heroku/python/lib/python3.8/site-packages/celery/app/trace.py", line 385, in trace_task
  R = retval = fun(*args, **kwargs)
File "/app/.heroku/python/lib/python3.8/site-packages/newrelic/hooks/application_celery.py", line 84, in wrapper
  return wrapped(*args, **kwargs)
File "/app/.heroku/python/lib/python3.8/site-packages/celery/app/trace.py", line 648, in __protected_call__
  return self.run(*args, **kwargs)
File "/app/pontoon/sync/core.py", line 68, in wrapped_func
  return func(self, *args, **kwargs)
File "/app/pontoon/sync/tasks.py", line 101, in sync_project
  sync_translations(
File "/app/pontoon/sync/tasks.py", line 280, in sync_translations
  changeset.execute()
File "/app/pontoon/sync/changeset.py", line 149, in execute
  Entity.objects.filter(
File "/app/pontoon/base/models.py", line 2451, in reset_active_translations
  translations.filter(Q(approved=True) | Q(fuzzy=True)).update(active=True)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/models/query.py", line 784, in update
  rows = query.get_compiler(self.db).execute_sql(CURSOR)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1522, in execute_sql
  cursor = super().execute_sql(result_type)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1156, in execute_sql
  cursor.execute(sql, params)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/backends/utils.py", line 66, in execute
  return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
  return executor(sql, params, many, context)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
  return self.cursor.execute(sql, params)
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/utils.py", line 90, in __exit__
  raise dj_exc_value.with_traceback(traceback) from exc_value
File "/app/.heroku/python/lib/python3.8/site-packages/django/db/backends/utils.py", line 84, in _execute
  return self.cursor.execute(sql, params)
File "/app/.heroku/python/lib/python3.8/site-packages/newrelic/hooks/database_psycopg2.py", line 34, in execute
  return super(CursorWrapper, self).execute(sql, parameters, *args,
File "/app/.heroku/python/lib/python3.8/site-packages/newrelic/hooks/database_dbapi2.py", line 24, in execute
  return self.__wrapped__.execute(sql, parameters,
django.db.utils.IntegrityError: duplicate key value violates unique constraint "entity_locale_active"
DETAIL:  Key (entity_id, locale_id, active)=(166302, 317, t) already exists.

[mathjazz]

Comment Author: @flodolo

Looks like we're hitting this bug again, with sync stuck for Firefox.

 May 23 13:35:47 mozilla-pontoon app/worker.1 psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "entity_locale_active"
May 23 13:35:47 mozilla-pontoon app/worker.1 DETAIL:  Key (entity_id, locale_id, active)=(69308, 317, t) already exists.

[mathjazz]

Comment Author: @flodolo

It doesn't look like the string wasn't touched in the past 4 years https://pontoon.mozilla.org/ia/firefox/dom/chrome/accessibility/AccessFu.properties/?search=toolbar&string=69308

It's worth noting that this happened twice so far, and always with Interlingua (locale id 317). We had issues with Interlingua a while ago, when we migrated from BitBucket to hg.m.o, so maybe we're still seeing issues caused by that https://bugzilla.mozilla.org/show_bug.cgi?id=1409962

[mathjazz]

Comment Author: @mathjazz

There were dozens of duplicate translations in the ia Firefox localization, which I've identified with the help of the code below and removed.

translations = Translation.objects.filter(
    entity__resource__project__slug="firefox",
    locale__code="ia",
)

unique_entity_string_combination = (
    translations
    .values("entity", "string")
    .annotate(count=Count('entity'))
)

for t in unique_entity_string:
    # duplicate translations
    if t["count"] > 1:
        print(t)

[mathjazz]

Comment Author: @mathjazz

Let's keep the bug open to investigate if we have further cases of duplicate translations, where do they originate from and how do we prevent sync from crashing.