#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 )
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 , 7 years ago
| Description: | modified (diff) |
|---|
comment:3 by , 7 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 , 7 years ago
| Cc: | added |
|---|
comment:5 by , 7 years ago
| Summary: | Oracle 11.2 + large search related = boom in instance.delete() → Oracle crashes with id__in query with 100k ids |
|---|---|
| Triage Stage: | Unreviewed → Accepted |
Accepting for further investigation.
comment:6 by , 7 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 , 7 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 , 7 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 , 7 years ago
| Owner: | changed from to |
|---|---|
| Status: | new → assigned |
| Summary: | Oracle crashes with id__in query with 100k ids → Oracle 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 , 7 years ago
| Version: | 1.11 → master |
|---|
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 , 7 years ago
You could test connection.ops.bulk_batch_size() instead of testing by executing a query.
comment:12 by , 7 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.
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.