14

Got stuck I have an database in which when I try to make python manage.py migrate it's giving this error as follows:

django.db.utils.IntegrityError: duplicate key value violates unique constraint "auth_permission_pkey"
DETAIL:  Key (id)=(241) already exists.

following is whole error :

Operations to perform:
  Apply all migrations: admin, auth, companyapp, contenttypes, djcelery, kombu_transport_django, loginapp, projectmanagement, recruitmentproject, sessions, smallproject
Running migrations:
  No migrations to apply.
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/__init__.py", line 363, in execute_from_command_line
    utility.execute()
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/__init__.py", line 355, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/base.py", line 283, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/base.py", line 330, in execute
    output = self.handle(*args, **options)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/commands/migrate.py", line 227, in handle
    self.verbosity, self.interactive, connection.alias, apps=post_migrate_apps, plan=plan,
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/core/management/sql.py", line 53, in emit_post_migrate_signal
    **kwargs
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/dispatch/dispatcher.py", line 193, in send
    for receiver in self._live_receivers(sender)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/contrib/auth/management/__init__.py", line 83, in create_permissions
    Permission.objects.using(using).bulk_create(perms)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/models/query.py", line 443, in bulk_create
    ids = self._batched_insert(objs_without_pk, fields, batch_size)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/models/query.py", line 1080, in _batched_insert
    inserted_id = self._insert(item, fields=fields, using=self.db, return_id=True)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/models/query.py", line 1063, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 1099, in execute_sql
    cursor.execute(sql, params)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/backends/utils.py", line 80, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/ubuntu/.local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "auth_permission_pkey"
DETAIL:  Key (id)=(241) already exists.
Piyush S. Wanare
  • 4,703
  • 6
  • 37
  • 54
  • Please post the contents of the migration that's causing the issue. – Adam Barnes Oct 17 '17 at 10:45
  • There are two possible causes. Maybe that migration added only a `unique=True` parameter (rob's answer is enough) or the unique field is required. (not allowed `null=True`) I can write an answer In the latter (special) case. – hynekcer Oct 17 '17 at 14:55
  • Similar problem has been addressed https://stackoverflow.com/a/11093322/5378183 – Mariah Jan 17 '18 at 23:24

4 Answers4

28

I have tried the above answers, but those did not help me.

Django has built in command to solve this problem.

python manage.py sqlsequencereset auth | python manage.py dbshell

The proper explanation for why this occurs and what the above command does can all be found in this blog post

jibin mathew
  • 682
  • 11
  • 14
  • 1
    Thanks. Didn't know that command existed. Runs it, gets fixed instantly. Thanks. – KhoPhi Feb 26 '20 at 11:12
  • 1
    Yes! There are lots of fixes on SO for the individual bits, but this fixed things for me. – Tom Apr 01 '20 at 16:04
4

You have to reset the auth_permission_id_seq as it is most likely lower than the maximum id

SELECT MAX(id)+1 FROM auth_permission
ALTER SEQUENCE auth_permission_id_seq RESTART WITH <result of previous cmd>;

Where 100 is the MAX(id)+1. There is probably a way to do this in one command but my SQL knowledge is limited.

The following will show you the current value of the sequence number and not the one you have to set it to (as it can be way off the MAX(id) in auth_permission)

SELECT last_value FROM auth_permission_id_seq;

I personally had the same issue when I restored the dump (data only) of the production database to the development one.

nbeuchat
  • 6,575
  • 5
  • 36
  • 50
3

I solved this by first running

SELECT last_value FROM auth_permission_id_seq;

to see what the latest sequence number was (for me it was 80), then I set it to a larger value:

ALTER SEQUENCE auth_permission_id_seq RESTART WITH 100;

elimisteve
  • 1,771
  • 1
  • 18
  • 22
0

Without much other context, it looks like you've added a unique constraint to your model, but you have rows in your database that violate this constraint, so the migration fails. So, in your database, you have two rows where auth_permission_pkey == 241.

You need to remove or change this row so it is unique, and re-run your migration.

rob
  • 2,119
  • 1
  • 22
  • 41