instance.delete() gives IntegrityError

[carn1x]

I have the following model:

class Profile(BaseModel):
    audit_log = AuditLog()
    user = OneToOneField(AUTH_USER_MODEL, related_name='profile')
    phone = CharField(max_length=20)
    name = CharField(max_length=255)

and calling profile_instance.delete() give the following error:

IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test_db`.`profile_profileauditlogentry`, CONSTRAINT `user_id_refs_id_6c82164d` FOREIGN KEY (`user_id`) REFERENCES `custom_user_emailuser` (`id`))')

Is this a bug or am I misusing auditlog? For instance I note that the auditlog expects to store data about when a host instance is deleted, however I don't understand how the instance could ever hope to survive when it is enforcing relationships with the host instance?

[carn1x]

Test via django shell:

>>> from django.contrib.auth import get_user_model  
>>> from profile.models import Profile
>>> user = get_user_model().objects.create_user('[email protected]', 'testpass')
>>> user
<EmailUser: [email protected]>
>>> user.pk
4L
>>> profile = Profile(user=user, phone='1', name='abc')
>>> profile.save()
>>> profile
<Profile: Profile object>
>>> profile.pk
4L
>>> user.delete()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/models/base.py", line 739, in delete
    collector.delete()
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/models/deletion.py", line 279, in delete
    query.delete_batch(pk_list, self.using)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/models/sql/subqueries.py", line 48, in delete_batch
    self.do_query(self.get_meta().db_table, self.where, using=using)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/models/sql/subqueries.py", line 33, in do_query
    self.get_compiler(using).execute_sql(NO_RESULTS)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 786, in execute_sql
    cursor.execute(sql, params)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 81, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/data/.venv/test/local/lib/python2.7/site-packages/django/db/backends/mysql/base.py", line 129, in execute
    return self.cursor.execute(query, args)
  File "/data/.venv/test/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/data/.venv/test/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
IntegrityError: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`test`.`profile_profileauditlogentry`, CONSTRAINT `profile_pro_user_id_36650277eb237a29_fk_custom_user_emailuser_id` FOREIGN KEY (`user_id`) REFERENCES `custom_user_emailuser` (`id`))')

[carn1x]

Also, just tested with Profile.audit_log removed just for fun and there is no error on user.delete() in that scenario.

[carn1x]

Changing from OneToOneField to ForeignKey has no effect, the problem remains.

[carn1x]

Just read the snippet on M2M relations in the doc: http://django-audit-log.readthedocs.org/en/stable/model_history.html#m2m-relations which I guess makes this unsupported?

[jscn]

I'm also hitting this error after upgrading from Django 1.6 to 1.8 (i.e., I'm only getting this error when using migrations generated by the new migrations framework rather than those generated by south.)

I'm not sure I understand why this would be unsupported since a) it worked with Django 1.6 and b) it's failing on a OneToOneField, not a M2M.

[bobort]

My workaround is to delete the specified ForeignKey constraints on the database backend.

[sraddhanjali]

http://django-audit-log.readthedocs.org/en/latest/model_history.html#disabling-enabling-tracking-on-a-model-instance This link does say, to disable the tracking while trying to delete. But it is not working for me.

[joerg86]

Hello,

I have been struggling around with this issue for some hours as well. And I thought it might help somebody to describe what I found out:

As far as I can see the problem occurrs when you delete an object that has a relationship with another object that is audited as well.

Example: You have a user and the user has a profile and audit logs for both models.

• You want to delete the user "robert"
• Django first deletes the related objects - the Profile of Robert in this case (plus others)
• if the Profile is deleted, django-audit-log is triggered and creates a ProfileAuditLogEntry ("james deleted the profile of user robert").
• Django deletes the user which now fails, because the ProfileAuditLogEntry has an FK to it! The new ProfileAuditLogEntry is a new related object that Django of course could not find when it deleted the related objects to "release" all FKs - because it was not yet there.

My workaround / solution was to use audit_log.disable_tracking() in a pre_delete signal handler of Profile. Like this, the deletion of the related object is simply not logged and the deletion of the user works fine. Of course, it is not the most elegant solution - especially if you want to track deletiion of profiles in other circumstances - but it works for us....

I hope this is helpful for somebody :)

Jörg

[vaibhav-jain]

I am too having similar IntegrityError error . . . . everything was working fine but after migrating from SQLite to postgreSQL error started . . can someone tell me how to fix that. I do not want to disable the tracking . .

Traceback (most recent call last):
  File "C:\Python34\lib\site-packages\django\db\backends\__init__.py", line 145, in _commit
    return self.connection.commit()
psycopg2.IntegrityError: insert or update on table "girvi_itemauditlogentry" violates foreign key constraint "girvi_itemauditlogentry_packet_id_11cfe9f7_fk_girvi_packet_id"
DETAIL:  Key (packet_id)=(2047) is not present in table "girvi_packet".

django.db.utils.IntegrityError: insert or update on table "girvi_itemauditlogentry" violates foreign key constraint "girvi_itemauditlogentry_packet_id_11cfe9f7_fk_girvi_packet_id"
DETAIL:  Key (packet_id)=(2047) is not present in table "girvi_packet".

[joerg86]

A log entry for "item" referring to an already deleted "packet" will always fail. And in the end your log entries are deleted anyway.It does not make a difference if you had disabled tracking before deletion.

[vaibhav-jain]

@joerg86 I am using this app for last one year never had this kind of issue. Suddenly after migration the error started. Why not before ??

[joerg86]

Because you migrated to PostgresSQL and it enforces FK integrity. Which SQLite does not do.

[vaibhav-jain]

@joerg86 Please can you suggest me any work around ???

[vaibhav-jain]

@joerg86 I followed your approach of disabling the tracking it works but what if delete is only called on child instance in that case I want to log the child details. If you can suggest me something. :) but disable the tracking if deletion is called on Parent instance . . . Here