Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#28596 closed Bug (fixed)

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

Reported by: Markus Stenberg Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Mariusz Felisiak 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 by Tim Graham, 6 years ago

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 by Markus Stenberg, 6 years ago

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 by Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added

comment:5 by Tim Graham, 6 years ago

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 by Jani Tiainen, 6 years ago

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 by Jani Tiainen, 6 years ago

According to https://docs.oracle.com/database/122/SQLRF/IN-Condition.htm#SQLRF52169

limit is still 1000 values in IN condition expression..

comment:8 by Mariusz Felisiak, 6 years ago

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 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
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 by Mariusz Felisiak, 6 years ago

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 by Tim Graham, 6 years ago

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

comment:12 by Mariusz Felisiak, 6 years ago

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 by Mariusz Felisiak, 6 years ago

Has patch: set

comment:14 by GitHub <noreply@…>, 6 years ago

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 by Mariusz Felisiak <felisiak.mariusz@…>, 6 years ago

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