Opened 14 months ago

Closed 14 months ago

Last modified 14 months 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 14 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 14 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 14 months ago by felixxm

Cc: felixxm added

comment:5 Changed 14 months 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 14 months 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 14 months 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 14 months 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 14 months 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 14 months 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 14 months ago by Tim Graham

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

comment:12 Changed 14 months 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 14 months ago by felixxm

Has patch: set

comment:14 Changed 14 months 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 14 months 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