[Django] #28596: Oracle 11.2 + large search related = boom in instance.delete()
Django
-------------------------------------+-------------------------------------
Reporter: Markus | Owner: nobody
Stenberg |
Type: Bug | Status: new
Component: Database | Version: 1.11
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Given model B, which has foreign key to model A (and delete=CASCADE).
If deleting A instance with 100k references from different B instances,
Oracle closes connection and the Oracle worker dies to ORA-0600.
Reason:
Django does search_related with id__in of 100k different ids. Oracle (at
least 11.2) cannot handle it and blows up. (This is known by Oracle IIRC
but not fixed at least not in 11.)
Workaround:
provide bulk_batch_size with e.g. 5000 maximum number of items in a
query.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
> Given model B, which has foreign key to model A (and delete=CASCADE).
>
> If deleting A instance with 100k references from different B instances,
> Oracle closes connection and the Oracle worker dies to ORA-0600.
>
> Reason:
> Django does search_related with id__in of 100k different ids. Oracle (at
> least 11.2) cannot handle it and blows up. (This is known by Oracle IIRC
> but not fixed at least not in 11.)
>
> Workaround:
> provide bulk_batch_size with e.g. 5000 maximum number of items in a
> query.
New description:
Given model B, which has foreign key to model A (and delete=CASCADE).
If deleting A instance with 100k references from different B instances,
Oracle closes connection and the Oracle worker dies to ORA-0600.
Reason: Django does search_related with `id__in` of 100k different ids.
Oracle (at least 11.2) cannot handle it and blows up. (This is known by
Oracle IIRC but not fixed at least not in 11.)
Workaround: provide `bulk_batch_size` with e.g. 5000 maximum number of
items in a query.
--
Comment (by Tim Graham):
Can you confirm if the issue affects the Django master branch with Oracle
12? Django 2.0 (the master branch) drops support for Oracle 11.2. Unless
this is a regression from previous Django releases, we won't fix the issue
in Django 1.11.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:1>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: => felixxm
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:2>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: felixxm | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Markus Stenberg):
Unfortunately I do not have test setup with Oracle 12 and that codebase
(it takes awhile to reproduce as well).
Django 2.0 code in affected parts is same, but maybe Oracle has fixed the
bug; however, 500+kb single SQL query sounds like a bug to me to start
with.
(it leads to SELECT .. WHERE id IN .. list of 100k ids .. in the related
gathering part of deletion.py.)
This isn't technically regression as the Oracle 11.2 (at least) in
question been broken always (we have encountered it now with both Django
1.8 and Django 1.11.).
I wrote ugly few-line monkeypatch that fixes the issue but I guess I have
to live with that until we get to Oracle 12 (and hopefully fixed bug and
Django 2.0+).
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:3>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: new
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* cc: felixxm (added)
* keywords: felixxm =>
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
Accepting for further investigation.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jani Tiainen):
I think that Oracle has limitation of 999 (or 1k) entries in a list of a
IN query. I recall seeing code that split queries like that to multiple
ones in older versions of Django (1.5 and older) but did it worked I can't
vouch for.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jani Tiainen):
According to https://docs.oracle.com/database/122/SQLRF/IN-
Condition.htm#SQLRF52169
limit is still 1000 values in IN condition expression..
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: nobody
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
https://github.com/django/django/blob/master/django/db/models/lookups.py#L343-L363
is the code, so probably an issue is somewhere in delete.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: 1.11
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => felixxm
Comment:
I remembered that I had encountered this problem in the past. It's related
with Oracle PL/SQL limits i.e. Oracle allows to 2^16^ = 65536 bind
parameters per cursor (see
[https://docs.oracle.com/database/121/LNPLS/limits.htm#GUID-00966B4C-
B9A5-47D4-94AA-54AEBCC07CE9__BABIHIJG docs]). It will be hard to fix this
issue in general, but it's feasible to fix `UpdateQuery.update_batch()`
and `DeleteQuery.delete_batch()`.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:9>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: assigned
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* version: 1.11 => master
Comment:
When I was working on this PR I encountered similar issue with
`bulk_create()`. I prepared [https://github.com/django/django/pull/9178
PR] with fix. Unfortunately test is not feasible, because it takes almost
30 minutes and exceeds db memory limit: "''ORA-04036: PGA memory used by
the instance exceeds PGA_AGGREGATE_LIMIT''".
{{{#!python
diff --git a/tests/bulk_create/tests.py b/tests/bulk_create/tests.py
index 2439050623..0dd9549902 100644
--- a/tests/bulk_create/tests.py
+++ b/tests/bulk_create/tests.py
@@ -255,3 +255,7 @@ class BulkCreateTests(TestCase):
# Objects save via bulk_create() and save() should have equal
state.
self.assertEqual(country_nl._state.adding,
country_be._state.adding)
self.assertEqual(country_nl._state.db, country_be._state.db)
+
+ def test_exceed_db_max_query_params(self):
+ # Oracle has limitation of number of parameters in a query.
+ Restaurant.objects.bulk_create((Restaurant(name='r'),) * 2**16)
}}}
Before this fix test failed with: "''ORA-00600: internal error code,
arguments: [qcscbndv1], [65535], [], [], [], [], [], [], [], [], [],
[]''".
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:10>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Tim Graham):
You could test `connection.ops.bulk_batch_size()` instead of testing by
executing a query.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
You're right, thanks. This PR fix also cascade deletion, because it
affects `Collector.get_del_batches()`, therefore it fix entire ticket
since `UpdateQuery.update_batch()` works fine even without it.
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/9178 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"1b823b8f182e8f31b8c9db281311ef718299eda7" 1b823b8]:
{{{
#!CommitTicketReference repository=""
revision="1b823b8f182e8f31b8c9db281311ef718299eda7"
Fixed #28596 -- Fixed QuerySet.bulk_create() and cascade deletion crash on
Oracle when using more than 65535 parameters.
Thanks Tim Graham for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Markus Stenberg | Owner: felixxm
Type: Bug | Status: closed
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"20d678542645deaac9c4b34eeb352b324a910fe2" 20d67854]:
{{{
#!CommitTicketReference repository=""
revision="20d678542645deaac9c4b34eeb352b324a910fe2"
[2.0.x] Fixed #28596 -- Fixed QuerySet.bulk_create() and cascade deletion
crash on Oracle when using more than 65535 parameters.
Thanks Tim Graham for the review.
Backport of 1b823b8f182e8f31b8c9db281311ef718299eda7 from master
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28596#comment:15>