[Django] #16426: sqlite: Cannot delete more than 999 things if there is a relation pointing to them

Django

unread,

Jul 6, 2011, 3:23:17 PM7/6/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new
Milestone: | Component: Database
Version: 1.3 | layer (models, ORM)
Keywords: | Severity: Normal
Has patch: 0 | Triage Stage:
Needs tests: 0 | Unreviewed
Easy pickings: 0 | Needs documentation: 0
| Patch needs improvement: 0
| UI/UX: 0
-------------------------------------+-------------------------------------
Given these models:

{{{
#!python
class Thing(models.Model):
name = models.CharField(max_length=32)

def __unicode__(self):
return self.name

class RelatedThing(models.Model):
thing = models.ForeignKey(Thing)

def __unicode__(self):
return u'object related to %s.' % self.thing
}}}

using sqlite as the DB, attempting to delete more than 999 Things in one
go fails:

{{{
--> python manage.py shell
/home/kmtracey/django/hg-django/django/conf/__init__.py:75:
DeprecationWarning: The ADMIN_MEDIA_PREFIX setting has been removed; use
STATIC_URL instead.
"use STATIC_URL instead.", DeprecationWarning)
Python 2.6.5 (r265:79063, Apr 16 2010, 13:57:41)
[GCC 4.4.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from ttt.models import Thing
>>> Thing.objects.count()
0
>>> for i in range(1000):
... Thing.objects.create(name='Thing %d' % i)
...
<Thing: Thing 0>
<Thing: Thing 1>
<Thing: Thing 2>
[...output snipped...]
<Thing: Thing 996>
<Thing: Thing 997>
<Thing: Thing 998>
<Thing: Thing 999>
>>> Thing.objects.all().delete()
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/kmtracey/django/hg-django/django/db/models/query.py", line
444, in delete
collector.collect(del_query)
File "/home/kmtracey/django/hg-django/django/db/models/deletion.py",
line 167, in collect
if not sub_objs:
File "/home/kmtracey/django/hg-django/django/db/models/query.py", line
113, in __nonzero__
iter(self).next()
File "/home/kmtracey/django/hg-django/django/db/models/query.py", line
107, in _result_iter
self._fill_cache()
File "/home/kmtracey/django/hg-django/django/db/models/query.py", line
784, in _fill_cache
self._result_cache.append(self._iter.next())
File "/home/kmtracey/django/hg-django/django/db/models/query.py", line
273, in iterator
for row in compiler.results_iter():
File "/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py",
line 699, in results_iter
for rows in self.execute_sql(MULTI):
File "/home/kmtracey/django/hg-django/django/db/models/sql/compiler.py",
line 754, in execute_sql
cursor.execute(sql, params)
File "/home/kmtracey/django/hg-django/django/db/backends/util.py", line
34, in execute
return self.cursor.execute(sql, params)
File "/home/kmtracey/django/hg-
django/django/db/backends/sqlite3/base.py", line 226, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: too many SQL variables
>>> Thing.objects.count()
1000
>>> Thing.objects.filter(pk__gte=1000).delete()
>>> Thing.objects.count()
999
>>> Thing.objects.all().delete()
>>> Thing.objects.count()
0
}}}

The problem is occurring when trying to collect the !RelatedThings that
might need to be deleted along with the Things being deleted. The SQL it
is trying to execute is of the form:

{{{
#!sql
SELECT [list_of_columns] FROM [related_table] WHERE
[related_table]."thing_id" IN ([list of 1000 pks of Things being deleted])
}}}

I'm guessing this is due to item #9 in http://www.sqlite.org/limits.html

I tried the same thing on 1.2.X branch level and did not see the failure,
it is new with 1.3 (I did also try 1.3 from around 1.3 beta in addition to
the above which is with current trunk...it failed with the pre-release 1.3
as well).

--
Ticket URL: <https://code.djangoproject.com/ticket/16426>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,

Jul 6, 2011, 5:19:54 PM7/6/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

* stage: Unreviewed => Accepted

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:1>

Django

unread,

Sep 9, 2011, 11:04:22 PM9/9/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by version2beta):

Looks to me like the problem is with the reference to {{{sub_objs}}} in
{{{django/db/models/deletion.py}}} line 166. At that point, one cannot
reference the object without generating a {{{DatabaseError: too many SQL
variables}}} error.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:2>

Django

unread,

Oct 20, 2011, 9:13:22 AM10/20/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by mbaechtold):

Maybe this is SQLite limition.

See "9. Maximum Number Of Host Parameters In A Single SQL Statement" on
http://www.sqlite.org/limits.html:

"To prevent excessive memory allocations, the maximum value of a host
parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999."

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:3>

Django

unread,

Nov 10, 2011, 8:22:41 PM11/10/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by adam.hotz@…):

I have also found this problem using an SQLite database.
I am working around it using code similar to the following:

if not connection.features.supports_1000_query_parameters:
while len(Thing.objects.all()):
pks = Thing.objects.all()[0:999]
Thing.objects.filter(pk__in = pks).delete()

However this is far from optimal.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:4>

Django

unread,

Nov 11, 2011, 6:23:45 AM11/11/11

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by anonymous):

Never mind, it was easier just to recompile python and then copy the
sqlite3.dll and _sqlite.pyd files into my install. I changed the limit to
MAX_INT. A better solution would be for the sqlite3 python module to
expose the int sqlite3_limit(sqlite3*, int id, int newVal) runtime method.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:5>

Django

unread,

Jan 12, 2012, 8:32:32 PM1/12/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Changes (by will@…):

* cc: will@… (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:6>

Django

unread,

Feb 28, 2012, 7:56:51 AM2/28/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by ramiro):

Django

unread,

Mar 6, 2012, 11:57:18 AM3/6/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by anonymous):

couldn't some kind of checking be done in sqlite backend's delete() method
so it deletes the requested objects in groups < 999?

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:8>

Django

unread,

Mar 6, 2012, 12:02:13 PM3/6/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by aaugustin):

It isn't that easy -- in particular, it could break assumptions regarding
transactions and integrity (in case of a self-referencing foreign key).

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:9>

Django

unread,

Aug 23, 2012, 12:58:38 AM8/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by anonymous):

Perhaps a silly question but why does it need to list the PK of all the
things being deleted? This seems inefficient and vulnerable to limits like
the one in SQLite. Why not use SQL DELETE statements with WHERE clause?

{{{
Thing.objects.all().delete()
=> DELETE FROM Thing;

Thing.objects.filter(field='Whatsit').delete()
=> DELETE FROM Thing WHERE field='Whatsit';
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:10>

Django

unread,

Nov 23, 2012, 2:42:15 AM11/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0

Has patch: 0 | Patch needs improvement: 0

Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Comment (by aaugustin):

Unfortunately, this bug makes the test suite fail under SQLite since I
added a test that deletes all content types.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:11>

Django

unread,

Nov 23, 2012, 4:20:19 AM11/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by Aymeric Augustin <aymeric.augustin@…>):

In [changeset:"a892cd3191cd2e0d98756764ed7be3ad59b95850"]:
{{{
#!CommitTicketReference repository=""
revision="a892cd3191cd2e0d98756764ed7be3ad59b95850"
[1.5.x] Tweak a test to avoid hitting a limit with SQLite.

Django cannot delete more than 999 objects at a time with SQLite.

Refs #16426, #16039.

Backport of 2875b5d from master.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:13>

Django

unread,

Nov 23, 2012, 4:20:19 AM11/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by Aymeric Augustin <aymeric.augustin@…>):

In [changeset:"2875b5dcab23c027d019656b08da8b911bc60711"]:
{{{
#!CommitTicketReference repository=""
revision="2875b5dcab23c027d019656b08da8b911bc60711"

Tweak a test to avoid hitting a limit with SQLite.

Django cannot delete more than 999 objects at a time with SQLite.

Refs #16426, #16039.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:12>

Django

unread,

Nov 23, 2012, 4:28:27 AM11/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by akaariai):

Here is a quick attempt for fixing the limit issue:
https://github.com/akaariai/django/compare/ticket_16426

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:13>

Django

unread,

Nov 23, 2012, 6:40:18 PM11/23/12

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 1.3
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: | Needs documentation: 0

Has patch: 1 | Patch needs improvement: 0

Needs tests: 0 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------

Changes (by akaariai):

* has_patch: 0 => 1

Comment:

I have force-updated the
https://github.com/akaariai/django/compare/ticket_16426 branch, and now
the patch is looking pretty good to me. The query-amount counting in the
added test case is ugly... But it is just ugliness in a test case.

I would not be surprised if there are delete queries which still hit the
sqlite limit - generic foreign keys, multi-inheritance etc. But, getting
this moved forward is enough IMO.

Master-only in my opinion, we can consider backpatching to 1.5.x, but lets
first see how this behaves in master.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:14>

Django

unread,

Mar 26, 2013, 5:59:47 AM3/26/13

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Changes (by AkosLadanyi):

* cc: AkosLadanyi (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:15>

Django

unread,

Sep 24, 2013, 8:54:47 AM9/24/13

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by NiGhTTraX):

Any updates regarding this issue? The patch looks good to me. Should be at
least merged into master.

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:16>

Django

unread,

Oct 13, 2013, 1:34:02 PM10/13/13

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody
Type: Bug | Status: new

Comment (by aaugustin):

#21205 was a duplicate with an alternative patch:
https://github.com/django/django/pull/1699

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:17>

Django

unread,

Aug 11, 2014, 5:46:31 AM8/11/14

to django-...@googlegroups.com

#16426: sqlite: Cannot delete more than 999 things if there is a relation pointing
to them
-------------------------------------+-------------------------------------
Reporter: kmtracey | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 1.3

(models, ORM) | Resolution: fixed

Changes (by Anssi Kääriäinen <akaariai@…>):

* status: new => closed
* resolution: => fixed

Comment:

In [changeset:"dfadbdac6a63dce3304dff1977b5b0a15dc2d7b5"]:
{{{
#!CommitTicketReference repository=""
revision="dfadbdac6a63dce3304dff1977b5b0a15dc2d7b5"
Fixed #16426 -- deletion of 1000+ objects with relations on SQLite

SQLite doesn't work with more than 1000 parameters in a single query.
The deletion code could generate queries that try to get related
objects for more than 1000 objects thus breaking the limit. Django now
splits the related object fetching into batches with at most 1000
parameters.

The tests and patch include some work done by Trac alias NiGhTTraX in
ticket #21205.
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/16426#comment:18>

Reply all

Reply to author

Forward