Closed Bug 1703666 Opened 7 months ago Closed 1 month ago

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

Categories

(Webtools Graveyard :: Pontoon, defect, P2)

Tracking

(Not tracked)

RESOLVED MOVED

People

(Reporter: mathjazz, Assigned: mathjazz)

Details

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.

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.

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

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)
Assignee: nobody → m
Status: NEW → RESOLVED
Closed: 5 months ago
Resolution: --- → FIXED

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.

Status: RESOLVED → REOPENED
Resolution: FIXED → ---
*This bug has been moved to GitHub.*

*Please check it out on https://github.com/mozilla/pontoon/issues.*
Status: REOPENED → RESOLVED
Closed: 5 months ago1 month ago
Resolution: --- → MOVED
Product: Webtools → Webtools Graveyard
You need to log in before you can comment on or make changes to this bug.