[SOLVED] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

When things don't work as they should.
Post Reply
itsthejb
Posts: 20
Joined: Thu Sep 26, 2019 11:21 am

[SOLVED] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by itsthejb »

Hi everyone,

I was switching around my Redis instances (changing ports), and now suddenly getting this error, which stops Mayan from starting:

Code: Select all

Running migrations:
  No migrations to apply.
Error during signal_pre_upgrade signal: duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"
DETAIL:  Key (defined_storage_name)=(converter__assets_cache) already exists.
, <class 'django.db.utils.IntegrityError'>
Traceback (most recent call last):
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 581, in get_or_create
    return self.get(**kwargs), False
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 435, in get
    raise self.model.DoesNotExist(
mayan.apps.file_caching.models.DoesNotExist: Cache matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"
DETAIL:  Key (defined_storage_name)=(converter__assets_cache) already exists.


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

Traceback (most recent call last):
  File "/opt/mayan-edms/bin/mayan-edms.py", line 22, in <module>
    main()
  File "/opt/mayan-edms/bin/mayan-edms.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/opt/mayan-edms/lib/python3.9/site-packages/mayan/apps/common/management/commands/performupgrade.py", line 28, in handle
    signal_pre_upgrade.send(sender=self)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 180, in send
    return [
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 181, in <listcomp>
    (receiver, receiver(signal=self, sender=sender, **named))
  File "/opt/mayan-edms/lib/python3.9/site-packages/mayan/apps/common/handlers.py", line 9, in handler_pre_upgrade
    management.call_command(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/__init__.py", line 181, in call_command
    return command.execute(*args, **defaults)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 268, in handle
    emit_post_migrate_signal(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/core/management/sql.py", line 42, in emit_post_migrate_signal
    models.signals.post_migrate.send(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 180, in send
    return [
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/dispatch/dispatcher.py", line 181, in <listcomp>
    (receiver, receiver(signal=self, sender=sender, **named))
  File "/opt/mayan-edms/lib/python3.9/site-packages/mayan/apps/converter/handlers.py", line 9, in handler_create_asset_cache
    Cache.objects.update_or_create(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 608, in update_or_create
    obj, created = self.select_for_update().get_or_create(defaults, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 588, in get_or_create
    return self.create(**params), True
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 453, in create
    obj.save(force_insert=True, using=self.db)
  File "/opt/mayan-edms/lib/python3.9/site-packages/mayan/apps/events/decorators.py", line 22, in wrapper
    result = func(self, *args, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/mayan/apps/file_caching/models.py", line 191, in save
    result = super().save(*args, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/base.py", line 881, in _save_table
    results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/base.py", line 919, in _do_insert
    return manager._insert(
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/query.py", line 1270, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
    cursor.execute(sql, params)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/mayan-edms/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"
DETAIL:  Key (defined_storage_name)=(converter__assets_cache) already exists.

The postgresql instance is giving these errors:

Code: Select all

2022-07-17 16:26:21.898 UTC [53] ERROR:  duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"
2022-07-17 16:26:21.898 UTC [53] DETAIL:  Key (defined_storage_name)=(converter__assets_cache) already exists.
2022-07-17 16:26:21.898 UTC [53] STATEMENT:  INSERT INTO "file_caching_cache" ("defined_storage_name", "maximum_size") VALUES ('converter__assets_cache', 10485760) RETURNING "file_caching_cache"."id"

It's obviously constraint-oriented, but I'm not sure why suddenly it decided to make changes. Because I change the Redis port?

I had a previous unique-constraint error related to `file_caching_cache`, so I tried to fix that by removing the entry. Now I get this error.

I can restore the db from a backup, so it's not the end of the world, but if there are any manual database changes I can make to fix this manually, that would also be good.

Needless to say I can provide any database query results to help resolve it.

Hope someone can help!
Last edited by itsthejb on Sat Sep 24, 2022 5:57 pm, edited 2 times in total.
itsthejb
Posts: 20
Joined: Thu Sep 26, 2019 11:21 am

Re: [Solved] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by itsthejb »

Solved by restoring from a backup
User avatar
michael
Developer
Developer
Posts: 275
Joined: Sun Apr 19, 2020 6:21 am

Re: [Solved] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by michael »

Hi,

That field is marked as unique and enforced at the database layer. It is also updated using Django's `update_or_create` manager method which is guaranteed to be thread-safe and does not suffer from race conditions.

This appear to be a problem with a corrupted database. There is a contradiction between the error messages "Cache matching query does not exist." and "duplicate key value violates unique constraint". The row does not exists but when attempting to create it, the table unique constraint kicks in and says it already exists. It looks like the database table and table index managing the constraint (`file_caching_cache_defined_storage_name_key`) got de-synchronized.
itsthejb
Posts: 20
Joined: Thu Sep 26, 2019 11:21 am

Re: [Solved] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by itsthejb »

Hi!

Thanks for your reply, sorry I missed this previously. The issue has actually reoccurred, so looks like you are right and there is some issue that restoring from backup may not solve.

Would you or anyone be able to suggest how I can properly fix this? I'm SQL able, but more on the MySQL side, and not knowledgeable at all about the Mayan schema. Are there `mayan-edms.py` commands I could use for this?

Thanks
michael wrote: Sun Jul 24, 2022 6:16 am Hi,

That field is marked as unique and enforced at the database layer. It is also updated using Django's `update_or_create` manager method which is guaranteed to be thread-safe and does not suffer from race conditions.

This appear to be a problem with a corrupted database. There is a contradiction between the error messages "Cache matching query does not exist." and "duplicate key value violates unique constraint". The row does not exists but when attempting to create it, the table unique constraint kicks in and says it already exists. It looks like the database table and table index managing the constraint (`file_caching_cache_defined_storage_name_key`) got de-synchronized.
itsthejb
Posts: 20
Joined: Thu Sep 26, 2019 11:21 am

Re: duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by itsthejb »

Here's an output of `inspectdb`, perhaps this is useful? https://pastebin.com/PM1VjeB4
itsthejb
Posts: 20
Joined: Thu Sep 26, 2019 11:21 am

Re: [SOLVED] duplicate key value violates unique constraint "file_caching_cache_defined_storage_name_key"

Post by itsthejb »

Back to solved: I pgdumped everything and recreated my database. Seems to have fixed everything
Post Reply