Opened 2 months ago

Closed 7 weeks ago

Last modified 7 weeks ago

#28596 closed Bug (fixed)

Oracle crashes with query with 2^16+1 bind parameters.

Reported by: Markus Stenberg Owned by: felixxm
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: felixxm Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

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.

Change History (14)

comment:1 Changed 2 months ago by Tim Graham

Description: modified (diff)

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.

comment:3 Changed 2 months ago 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+).

comment:4 Changed 2 months ago by felixxm

Cc: felixxm added

comment:5 Changed 8 weeks ago by Tim Graham

Summary: Oracle 11.2 + large search related = boom in instance.delete()Oracle crashes with id__in query with 100k ids
Triage Stage: UnreviewedAccepted

Accepting for further investigation.

comment:6 Changed 8 weeks ago 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.

comment:7 Changed 8 weeks ago 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..

comment:8 Changed 8 weeks ago 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.

comment:9 Changed 8 weeks ago by felixxm

Owner: changed from nobody to felixxm
Status: newassigned
Summary: Oracle crashes with id__in query with 100k idsOracle crashes with query with 2^16+1 bind parameters.

I remembered that I had encountered this problem in the past. It's related with Oracle PL/SQL limits i.e. Oracle allows to 216 = 65536 bind parameters per cursor (see docs). It will be hard to fix this issue in general, but it's feasible to fix UpdateQuery.update_batch() and DeleteQuery.delete_batch().

comment:10 Changed 7 weeks ago by felixxm

Version: 1.11master

When I was working on this PR I encountered similar issue with bulk_create(). I prepared 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".

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], [], [], [], [], [], [], [], [], [], []".

comment:11 Changed 7 weeks ago by Tim Graham

You could test connection.ops.bulk_batch_size() instead of testing by executing a query.

comment:12 Changed 7 weeks ago 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.

comment:13 Changed 7 weeks ago by felixxm

Has patch: set

comment:14 Changed 7 weeks ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

In 1b823b8:

Fixed #28596 -- Fixed QuerySet.bulk_create() and cascade deletion crash on Oracle when using more than 65535 parameters.

Thanks Tim Graham for the review.

comment:15 Changed 7 weeks ago by Mariusz Felisiak <felisiak.mariusz@…>

In 20d67854:

[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

Note: See TracTickets for help on using tickets.
Back to Top