IntegrityError: duplicate key value violates unique constraint "entity_locale_active"

bugzilla-to-github commented 3 years ago

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.

bugzilla-to-github commented 3 years ago

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.

bugzilla-to-github commented 3 years ago

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

bugzilla-to-github commented 3 years ago

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)

bugzilla-to-github commented 3 years ago

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.

mathjazz commented 11 months ago

The issue has become more common recently with the introduction of Pretranslation: https://pontoon.mozilla.org/es-AR/mozillaorg/all-resources/?string=282842

flodolo commented 8 months ago

We have more errors

Nov 09 08:26:27 mozilla-pontoon app/web.1 django.db.utils.IntegrityError: duplicate key value violates unique constraint "entity_locale_active"
Nov 13 15:03:13 mozilla-pontoon app/web.3 django.db.utils.IntegrityError: duplicate key value violates unique constraint "entity_locale_active"
Nov 18 05:21:24 mozilla-pontoon app/worker.1 django.db.utils.IntegrityError: duplicate key value violates unique constraint "entity_locale_active"
(13 more at this point)
Nov 18 07:41:09 mozilla-pontoon app/worker.1 django.db.utils.IntegrityError: duplicate key value violates unique constraint "entity_locale_active"

mathjazz commented 8 months ago

The issue has become more common recently with the introduction of Pretranslation: https://pontoon.mozilla.org/es-AR/mozillaorg/all-resources/?string=282842

In the last batch of new strings added to Mozilla.org, this type of errors only appeared in es-AR and only in pretranslations that contain Fluent message references and variables. It turns out that placeables in these pretranslations were not stored in a canonical form, i.e. {example} instead of { example } (mind the whitespace).

In the exported files in the repository, the strings appeared as they should, which resulted in sync trying to import pretranslations, becuse they weren't the same as in Pontoon DB. That also triggered this issue. In the UI (editor, string list) the strings were also rendered properly, which only made the problem more difficult to spot.

As the first step, we should make sure that re-serializing pretranslations always stores them in the canonical form. For the record, this is the code in question, which (when used from the command line) actually solved the immediate sync issue: https://github.com/mozilla/pontoon/blob/73c05e4777bf2e78058743df86a02b0e30f54e57/pontoon/pretranslation/pretranslate.py#L59

Error could also be prevented by fixing #2120.

Finally, since the reason the problem appears only in es-AR could be that we have used TM entries with bad placeables formatting to train the MT engine, we should also look in that direction.

mathjazz commented 8 months ago

As the general fix for this very issue, we need to make sure translations actually get deactivated before new active translations are set: https://github.com/mozilla/pontoon/blob/73c05e4777bf2e78058743df86a02b0e30f54e57/pontoon/base/models.py#L2613-L2619

My suspicion is this:

we run the sync process within an atomic transaction
that makes sure that all DB changes are rolled back if the sync fails at any point
it also prevents committing the DB query to deactivate translations to the DB before new active translations are set
that means that we can produce duplicate active translations when marking them as active
the solution might be to use on_commit(), which is also a wild guess at this point

flodolo commented 8 months ago

appears only in es-AR could be that we have used TM entries with bad placeables formatting to train the MT engine

Given the amount of linters we have in place, I don't think these would exist in the "active" translations (strings currently in repositories). But, from my experience, translation memory has a lot more that doesn't show up in Pontoon, so it's still a possibility.

EDIT: just realized that, based on your comment on {$foo} vs { $foo }, that wouldn't be an error in any of the linters, since for Fluent they're both valid and equivalent.

mathjazz commented 2 months ago

This issue resurfaced in Hungarian Mozilla VPN Client after https://github.com/mozilla-l10n/mozilla-vpn-client-l10n/commit/2ab98c299c10eda1d32a041acfd28f76fc08b7dd landed.

We hit:

IntegrityError('duplicate key value violates unique constraint "entity_locale_active"
DETAIL:  Key (entity_id, locale_id, active)=(304357, 100, t) already exists.\n')

...

IntegrityError('duplicate key value violates unique constraint "entity_locale_active"
DETAIL:  Key (entity_id, locale_id, active)=(304326, 100, t) already exists.\n')

...

IntegrityError('duplicate key value violates unique constraint "entity_locale_active"
DETAIL:  Key (entity_id, locale_id, active)=(304322, 100, t) already exists.\n')

Approving failing Pretranslations didn't help. I had to delete all of them, sync again, and then run pretranslation.

mozilla / pontoon

IntegrityError: duplicate key value violates unique constraint "entity_locale_active" #2284

Bug 1703666