#23713: SQlite3: too many SQL variables, Foreign Keys & Delete
-------------------------------------+-------------------------------------
Reporter: Tanbouz | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 1.7
(models, ORM) | Keywords: sqlite cascade delete
Severity: Normal | foreign
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Using:
{{{
Python 3.4.2 (default, Oct 23 2014, 12:36:46)
[GCC 4.9.1] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import django
>>> django.VERSION
(1, 7, 1, 'final', 0)
>>> import sqlite3
>>> sqlite3.version
'2.6.0'
>>> sqlite3.sqlite_version
'3.8.7'
}}}
Database Structure
{{{
# Attempt to delete a row from the Categories table
Table-1 Categories
Table-2 Groups (FK: Groups -> Categories)
Table-3 Items (FK: Items -> Groups)
Table-R Features <-> Items (Many to Many) Error happens on a query to
delete rows from this table!
Table-4 Features
}}}
Test code:
{{{
from django.db import connection as dbconnection
# Pass a model to have its data deleted
def clean(db_model):
while db_model.objects.count():
row = db_model.objects.all()[:1]
# Can't use delete on a slice
row = db_model.objects.get(pk = row[0].pk)
try:
row.delete()
except django.db.utils.OperationalError:
print(dbconnection.queries)
}}}
dbconnection.queries prints several small SELECT queries but the
'''last''' query is a DELETE query with more than 1000 parameters
{{{
{'time': '0.001', 'sql': 'QUERY =
\'DELETE FROM "myapp_foreignmodel" WHERE
"myapp_foreignmodel"."foreignkey_id"
IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
%s, %s....
And so on (1180 total in my case)
}}}
Errors:
{{{
django.db.utils.OperationalError: too many SQL variables
sqlite3.OperationalError: too many SQL variables
}}}
[https://code.djangoproject.com/ticket/16426 Related ticket #16426]
The above ticket solves the problem by splitting delete queries into
batches. It doesn't seem to include cascaded delete queries aimed at a
foreign table.
''
[https://docs.djangoproject.com/en/1.7/topics/db/queries/#deleting-objects
When Django deletes an object, by default it emulates the behavior of the
SQL constraint ON DELETE CASCADE – in other words, any objects which had
foreign keys pointing at the object to be deleted will be deleted along
with it.]''
--
Ticket URL: <https://code.djangoproject.com/ticket/23713>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/050.f47804df1ff6de12780ffa0aa98ec99e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
