Code

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#10454 closed (duplicate)

IntegrityError while deleting object with complex constraint

Reported by: liangent Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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`))')

Attachments (0)

Change History (9)

comment:1 Changed 5 years ago by k0001

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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()
[]

comment:2 follow-up: Changed 5 years ago by 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?

comment:3 in reply to: ↑ 2 Changed 5 years ago by 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.

comment:4 Changed 5 years ago by ikelly

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.

comment:5 Changed 5 years ago by liangent

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

the bug disappeared after i removed TransactionMiddleware from middleware list.

comment:6 Changed 5 years ago by 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...

comment:7 Changed 5 years ago by 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..

comment:8 Changed 5 years ago by 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.

comment:9 Changed 5 years ago by ikelly

  • Resolution set to duplicate
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.