IntegrityError while deleting object with complex constraint

[liangent]

i have models like this (the whole project is too complex and i dont want to put it here):

class P(Model):
    pass
class TC(Model):
    p = ForeignKey(P)

class S(Model):
    p = ForeignKey(P)
class CR(Model):
    s = ForeignKey(S)
    tc = ForeignKey(TC) # note: s.p always equals to tc.p

when i'm trying to delect a P object (with all its TC, S, CR sub-objects existing), an IntegrityError occurs, saying Cannot delete or update a parent row: a foreign key constraint fails (`proj/m_tc`, CONSTRAINT `p_id_refs_id_16cb2ff4a6a127c1` FOREIGN KEY (`p_id`) REFERENCES `main_p` (`id`))') .

Environment:

Request Method: POST
Request URL: http://example.com/admin/m/p/2/delete/
Django Version: 1.1 pre-alpha
Python Version: 2.5.2
Installed Applications:
['django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.sites',
 'django.contrib.admin',
 'django.contrib.admindocs',
 'django.contrib.redirects',
 'django.contrib.comments',
 'django.contrib.flatpages',
 'notification',
 'avatar',
 'tagging',
 'announcements',
 'registration',
 'proj.cm',
 'proj.m',
 'proj.u',
 'proj.mk']
Installed Middleware:
('django.middleware.locale.LocaleMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.contrib.redirects.middleware.RedirectFallbackMiddleware',
 'django.contrib.flatpages.middleware.FlatpageFallbackMiddleware',
 'django.contrib.csrf.middleware.CsrfMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.transaction.TransactionMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'proj.middleware.CAM',
 'django.middleware.doc.XViewMiddleware')


Traceback:
File "/usr/lib/python2.5/site-packages/django/core/handlers/base.py" in get_response
  86.                 response = callback(request, *callback_args, **callback_kwargs)
File "/usr/lib/python2.5/site-packages/django/contrib/admin/sites.py" in root
  450.                 return self.model_page(request, *url.split('/', 2))
File "/usr/lib/python2.5/site-packages/django/views/decorators/cache.py" in _wrapped_view_func
  44.         response = view_func(request, *args, **kwargs)
File "/usr/lib/python2.5/site-packages/django/contrib/admin/sites.py" in model_page
  469.         return admin_obj(request, rest_of_url)
File "/usr/lib/python2.5/site-packages/django/contrib/admin/options.py" in __call__
  799.             return self.delete_view(request, unquote(url[:-7]))
File "/usr/lib/python2.5/site-packages/django/contrib/admin/options.py" in delete_view
  722.             obj.delete()
File "/usr/lib/python2.5/site-packages/django/db/models/base.py" in delete
  464.         delete_objects(seen_objs)
File "/usr/lib/python2.5/site-packages/django/db/models/query.py" in delete_objects
  1014.         del_query.delete_batch(pk_list)
File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py" in delete_batch
  88.             self.do_query(self.model._meta.db_table, where)
File "/usr/lib/python2.5/site-packages/django/db/models/sql/subqueries.py" in do_query
  35.         self.execute_sql(None)
File "/usr/lib/python2.5/site-packages/django/db/models/sql/query.py" in execute_sql
  1996.             cursor.execute(sql, params)
File "/usr/lib/python2.5/site-packages/django/db/backends/util.py" in execute
  19.             return self.cursor.execute(sql, params)
File "/usr/lib/python2.5/site-packages/django/db/backends/mysql/base.py" in execute
  83.             return self.cursor.execute(query, args)
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py" in execute
  166.             self.errorhandler(self, exc, value)
File "/var/lib/python-support/python2.5/MySQLdb/connections.py" in defaulterrorhandler
  35.     raise errorclass, errorvalue

Exception Type: IntegrityError at /admin/m/p/2/delete/
Exception Value: (1451, 'Cannot delete or update a parent row: a foreign key constraint fails (`proj/m_tc`, CONSTRAINT `p_id_refs_id_16cb2ff4a6a127c1` FOREIGN KEY (`p_id`) REFERENCES `m_p` (`id`))')

[k0001]

Using your models, the following example runs OK with postgresql_psycopg2 and sqlite3.

>>> p1 = P()
>>> p1.save()
>>> 
>>> tc1 = TC(p=p1)
>>> tc1.save()
>>> 
>>> s1 = S(p=p1)
>>> s1.save()
>>> 
>>> cr1 = CR(s=s1, tc=tc1)
>>> cr1.save()
>>> 
>>> P.objects.all()
[<P: P object>]
>>> TC.objects.all()
[<TC: TC object>]
>>> S.objects.all()
[<S: S object>]
>>> CR.objects.all()
[<CR: CR object>]
>>> 
>>> p1.delete()
>>> 
>>> P.objects.all()
[]
>>> TC.objects.all()
[]
>>> S.objects.all()
[]
>>> CR.objects.all()
[]

[Erin Kelly]

I ran the same test with MySQL/InnoDB and trunk, and I also did not get the error. It seems likely there is some other complication that is not apparent in the ticket. For example, does this only happen when deleting from a particular view?

[liangent]

Replying to ikelly:

I ran the same test with MySQL/InnoDB and trunk, and I also did not get the error. It seems likely there is some other complication that is not apparent in the ticket. For example, does this only happen when deleting from a particular view?

i only tried to delete it from django's admin site.

[Erin Kelly]

I can't reproduce this through the admin site either. The only way this is going to progress is if you can produce a test case for this that exhibits the bug outside your large project.

[liangent]

did you add 'django.middleware.transaction.TransactionMiddleware',?

the bug disappeared after i removed TransactionMiddleware from middleware list.

[liangent]

i tried to read django source code, and found this:

    def add(self, model, pk, obj, parent_model, nullable=False):
        """
        Adds an item to the container.

        Arguments:
        * model - the class of the object being added.
        * pk - the primary key.
        * obj - the object itself.
        * parent_model - the model of the parent object that this object was
          reached through.
        * nullable - should be True if this relation is nullable.

        Returns True if the item already existed in the structure and
        False otherwise.
        """
        d = self.data.setdefault(model, SortedDict())
        retval = pk in d
        d[pk] = obj
        # Nullable relationships can be ignored -- they are nulled out before
        # deleting, and therefore do not affect the order in which objects
        # have to be deleted.
        if parent_model is not None and not nullable:
            self.children.setdefault(parent_model, []).append(model)
        return retval

yes, there's some nullable foreignkeys pointing to P model/object in another model (i forgot to mention it above), and code below

        for field, model in cls._meta.get_fields_with_model():
            if (field.rel and field.null and field.rel.to in seen_objs and
                    filter(lambda f: f.column == field.column,
                    field.rel.to._meta.fields)):
                assert False # i added an assert here
                if model:
                    sql.UpdateQuery(model, connection).clear_related(field,
                            pk_list)
                else:
                    update_query.clear_related(field, pk_list)

i didn't catch any assertion error...

[liangent]

in filter(lambda f: f.column == field.column, field.rel.to._meta.fields)

i cannot understand why, if model A:field F is pointing to model B, we can get the result that if F has the same column name as a field in model B then clear field F..

[liangent]

the bug also disappears after removing and filter(lambda f: f.column == field.column, field.rel.to._meta.fields), but i'm not sure if there's other side-effect.

[Erin Kelly]

Sounds like this is the same issue as #9308. That ticket has a patch that is supposed to resolve it.