Integrity error on django_admin_log after updating existing site to new Django 1.5 user model

Question

Apparently after adding my new user table to the site, django_admin_log still has a FK to auth_user table. Any way to address this? I didn't see this problem in staging or locally so something odd must have taken place.

Traceback (most recent call last) :

File "/app/.heroku/python/lib/python2.7/site-packages/django/core/handlers/base.py", line 115, in get_response response = callback(request, *callback_args, **callback_kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/newrelic-1.10.0.28/newrelic/api/object_wrapper.py", line 220, in call self._nr_instance, args, kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/newrelic-1.10.0.28/newrelic/hooks/framework_django.py", line 475, in wrapper return wrapped(*args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/contrib/admin/options.py", line 372, in wrapper return self.admin_site.admin_view(view)(*args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/utils/decorators.py", line 91, in _wrapped_view response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/views/decorators/cache.py", line 89, in _wrapped_view_func response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/contrib/admin/sites.py", line 202, in inner return view(request, *args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/utils/decorators.py", line 25, in _wrapper return bound_func(*args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/utils/decorators.py", line 91, in _wrapped_view response = view_func(request, *args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/utils/decorators.py", line 21, in bound_func return func(self, *args2, **kwargs2)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/transaction.py", line 223, in inner return func(*args, **kwargs)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/transaction.py", line 217, in exit self.exiting(exc_value, self.using)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/transaction.py", line 281, in exiting commit(using=using)

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/transaction.py", line 152, in commit connection.commit()

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/init.py", line 241, in commit self._commit()

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 242, in _commit six.reraise(utils.IntegrityError, utils.IntegrityError(*tuple(e.args)), sys.exc_info()[2])

File "/app/.heroku/python/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 240, in _commit return self.connection.commit()

File "/app/.heroku/python/lib/python2.7/site-packages/newrelic-1.10.0.28/newrelic/hooks/database_dbapi2.py", line 68, in commit return self._nr_connection.commit()

IntegrityError: insert or update on table "django_admin_log" violates foreign key constraint "django_admin_log_user_id_fkey" DETAIL: Key (user_id)=(2) is not present in table "auth_user".

Town

Answer 1

That because the django_admin_log table still contains a foreign key relation to the old auth_user table.

You need to drop this and recreate the table.

$ heroku pg:psql
psql => drop table django_admin_log;

For Django < 1.7

$ heroku run python manage.py syncdb

And for Django >= 1.7

$ ./manage.py sqlmigrate admin 0001 | heroku pg:psql

And that's it :)

EDITED with @dustinfarris Django 1.7+ answer precision

Placket

Answer 2

If you run into this and you're using >=1.7:

./manage.py dbshell

DROP TABLE django_admin_log;

and then:

./manage.py sqlmigrate admin 0001 | ./manage.py dbshell

Geralyngeraniaceous

Worked for me using 1.8.2. Would be best if you added what it actually does. – Sharpeared

For a new project with django 1.10.1, I did the drop as noted, but instead of the sqlimigrate command at the end, I used a simple migrate command. So it was like "migrate->error->drop->migrate". – Quadragesima

Thanks for the solution but it didn't work for me. It's throwing an error: "CommandError: Cannot find a migration matching '001' from app 'admin'. Is it in INSTALLED_APPS?" Can you tell me what I did wrong ? – Rennes

Answer 3

If you are on Django 1.7 or later, adding a proper migration for altering the django_admin_log table is a much better option in my opinion. That way you can keep any existing log entries, which may actually be something you have use for. Doing such an alter requires that the id field is the same, e.g. has the same name etc.

First you will have to find out the name of the constraint, which can be done by going into the database shell:

./manage.py dbshell

And then describing the django_admin_log table:

\d+ django_admin_log;

This will have the constraint in the output, something like:

"user_id_refs_id_c0d12874" FOREIGN KEY (user_id) REFERENCES my_custom_auth_model(id) DEFERRABLE INITIALLY DEFERRED

Where my_custom_auth_model is the name of the table where your custom auth model lives, and user_id_refs_id_c0d12874 is the name of the constraint, which you should copy for later.

Next, create a new migration:

./manage makemigrations --empty my_custom_auth_model

I renamed my new migration (i.e. 0000_alter_admin_log_constraint.py) to have something useful instead of a datestamp in the filename. Don't use four zeros though, use whatever was assigned when creating the migration :)

In the new migration, this is what I used for operations:

operations = [
    migrations.RunSQL(
        '''ALTER TABLE django_admin_log DROP CONSTRAINT user_id_refs_id_c0d12874''',
        reverse_sql='''ALTER TABLE django_admin_log ADD CONSTRAINT user_id_refs_id_c0d12874
            FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED'''),
    migrations.RunSQL(
        '''ALTER TABLE django_admin_log ADD CONSTRAINT user_id_refs_id_c0d12874
            FOREIGN KEY (user_id) REFERENCES my_custom_auth_model(id) DEFERRABLE INITIALLY DEFERRED''',
        reverse_sql='''ALTER TABLE django_admin_log DROP CONSTRAINT user_id_refs_id_c0d12874'''),
]

Substitute user_id_refs_id_c0d12874 with whatever constraint name you copied previously. As you can see, the two operations and their reverses are inverses of each other, meaning you can move this migrations backwards as well.

Now, all you have to do is to apply the new migration:

./manage.py migrate

The django_admin_log table should now be usable again, and anything in admin writing to it will work instead of failing with an IntegrityError.

Lunetta

Not the easiest solution, but at least it saves your data in the admin log! – Hydrastine

I don't think it's clean to hardcode the constraint names in the migration. This is not portable. – Milewski

Thanks. I too feel having a migration file is way to have it this helped me – Hooey

Answer 4

It appears as though there may have been a bad transaction as some point when you ran this, you could try fully resetting your db with:

heroku pg:reset

Or you could attempt to psql into the database and examine/correct the data thats creating the issue (which is likely that its trying to insert the same user twice):

heroku pg:psql

Narrow

Answer 5

I think that admin app only installs django_admin_log table.

python manage.py sqlclear admin

BEGIN;
DROP TABLE "django_admin_log";

COMMIT;

So you can also try.

python manage.py sqlclear admin | python manage.py dbshell
python manage.py syncdb

Tollhouse

Answer 6

Delete the database and create a superuser, finally run migrate:

python manage.py createsuperuser    
python manage.py migrate

Dowson

Deleting a database is not doable for some projects. – Richman