[Django] #29777: ORA-00933: SQL command not properly ended with pagination in a ModelViewSet
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords:
Severity: Normal | orm,oracle,pagination
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
After an upgrade from Django 2.0.6 to Django 2.1.1 and DRF 3.8.2, I got an
error with a ModelViewSet with '''pagination''', it worked with previous
version.
When I get rid off pagination, there is no problems.
The stack error, notice the end :
{{{
api-dev | The above exception was the direct cause of the
following exception:
api-dev |
api-dev | Traceback (most recent call last):
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/core/handlers/exception.py", line 34, in inner
api-dev | response = get_response(request)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/core/handlers/base.py", line 126, in _get_response
api-dev | response = self.process_exception_by_middleware(e,
request)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/core/handlers/base.py", line 124, in _get_response
api-dev | response = wrapped_callback(request, *callback_args,
**callback_kwargs)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/views/decorators/csrf.py", line 54, in wrapped_view
api-dev | return view_func(*args, **kwargs)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/viewsets.py", line 103, in view
api-dev | return self.dispatch(request, *args, **kwargs)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/views.py", line 483, in dispatch
api-dev | response = self.handle_exception(exc)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/views.py", line 443, in handle_exception
api-dev | self.raise_uncaught_exception(exc)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/views.py", line 480, in dispatch
api-dev | response = handler(request, *args, **kwargs)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/mixins.py", line 42, in list
api-dev | page = self.paginate_queryset(queryset)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/generics.py", line 173, in paginate_queryset
api-dev | return self.paginator.paginate_queryset(queryset,
self.request, view=self)
api-dev | File "/usr/local/lib/python3.6/site-
packages/rest_framework/pagination.py", line 337, in paginate_queryset
api-dev | return list(queryset[self.offset:self.offset +
self.limit])
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/models/query.py", line 268, in __iter__
api-dev | self._fetch_all()
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/models/query.py", line 1186, in _fetch_all
api-dev | self._result_cache =
list(self._iterable_class(self))
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/models/query.py", line 54, in __iter__
api-dev | results =
compiler.execute_sql(chunked_fetch=self.chunked_fetch,
chunk_size=self.chunk_size)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/models/sql/compiler.py", line 1065, in execute_sql
api-dev | cursor.execute(sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/debug_toolbar/panels/sql/tracking.py", line 176, in execute
api-dev | return self._record(self.cursor.execute, sql,
params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/debug_toolbar/panels/sql/tracking.py", line 117, in _record
api-dev | return method(sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 100, in execute
api-dev | return super().execute(sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/raven/contrib/django/client.py", line 127, in execute
api-dev | return real_execute(self, sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 68, in execute
api-dev | return self._execute_with_wrappers(sql, params,
many=False, executor=self._execute)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
api-dev | return executor(sql, params, many, context)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 85, in _execute
api-dev | return self.cursor.execute(sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/utils.py", line 89, in __exit__
api-dev | raise dj_exc_value.with_traceback(traceback) from
exc_value
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/utils.py", line 85, in _execute
api-dev | return self.cursor.execute(sql, params)
api-dev | File "/usr/local/lib/python3.6/site-
packages/django/db/backends/oracle/base.py", line 513, in execute
api-dev | return self.cursor.execute(query,
self._param_generator(params))
api-dev | django.db.utils.DatabaseError: ORA-00933: SQL command
not properly ended
}}}
Example of my ModelViewSet and Model
{{{
#!div style="font-size: 80%"
Code highlighting:
{{{#!python
class MelLoginViewSet(viewsets.ModelViewSet):
queryset = MelLoginAnglais.objects.all()
serializer_class = MelLoginAnglaisSimpleSerializer
filter_backends = (django_filters.rest_framework.DjangoFilterBackend,
OrderingFilter, SearchFilter,)
pagination_class = LimitOffsetPagination
page_size = 10
class MelLoginAnglais(models.Model):
cod_ind = models.IntegerField(primary_key=True)
nom_login = models.CharField(max_length=100)
def __str__(self):
return "MelLogin : {cod} {login}".format(
cod=self.cod_ind,
login=self.nom_login
)
class Meta:
db_table = '"APOGEE"."MEL_LOGIN"'
managed = False
}}}
}}}
with python 3.6, Oracle 11.0.2.4
cx-Oracle 5.2.1 or 7.0
--
Ticket URL: <https://code.djangoproject.com/ticket/29777>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Carlton Gibson):
Hi...
> After an upgrade from Django 2.0.6 to Django 2.1.1 and DRF 3.8.2...
OK. DRF 3.8.2 supports both Django 2.x and Django 2.1.x. Can you please
downgrade Django (and keep DRF 3.8.2) and verify it works there?
(Then at least the change is presumably in Django, rather than DRF or your
code.)
If so, could you then git bisect to find the change? (See
[https://docs.djangoproject.com/en/dev/internals/contributing/triaging-
tickets/#bisecting-a-regression the guide on using bisect].)
Ideally we need to reduce this down to an issue that only involves Django
itself...
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
I'm afraid this has to do with your Oracle version.
Django 2.0 dropped support for Oracle < 12.1 so I assume it happened to
work for your use case but changes such as #28670 which are relied on by
pagination might have broken it.
https://docs.djangoproject.com/en/2.1/releases/2.0/#dropped-support-for-
oracle-11-2
https://code.djangoproject.com/wiki/SupportedDatabaseVersions
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by zorky):
Hi,
Thanks for your response.
I juste downgraded to '''Django 2.0.6''' (with DRF 3.8.2, django-filters
2.0) and '''it's working fine with no errors'''.
Thx
pip freeze :
{{{
# pip freeze
absl-py==0.5.0
appdirs==1.4.3
asn1crypto==0.24.0
astor==0.7.1
attrs==18.2.0
backcall==0.1.0
behave==1.2.6
behave-django==1.1.0
bleach==2.1.4
cached-property==1.5.1
cairocffi==0.9.0
CairoSVG==2.1.3
certifi==2018.8.24
cffi==1.11.5
chardet==3.0.4
click==6.7
coreapi==2.3.3
coreapi-cli==1.0.9
coreschema==0.0.4
cryptography==2.3.1
cssselect2==0.2.1
cx-Oracle==5.2.1
Cython==0.28.5
decorator==4.3.0
defusedxml==0.5.0
Django==2.0.6
django-auth-ldap==1.7.0
django-cleanup==2.1.0
django-cors-headers==2.4.0
django-debug-toolbar==1.10.1
django-extensions==2.1.2
django-filter==2.0.0
django-mail-templated==2.6.5
django-polymorphic==2.0.3
django-rest-swagger==2.2.0
django-treebeard==4.3
django-xworkflows==0.12.3
djangorestframework==3.8.2
djangorestframework-jwt==1.11.0
easy-thumbnails==2.5
entrypoints==0.2.3
et-xmlfile==1.0.1
factory-boy==2.11.1
Faker==0.9.1
gast==0.2.0
greenlet==0.4.15
grpcio==1.15.0
gunicorn==19.9.0
html5lib==1.0.1
idna==2.7
ipykernel==4.9.0
ipython==6.5.0
ipython-genutils==0.2.0
ipywidgets==7.4.2
isodate==0.6.0
itypes==1.1.0
jdcal==1.4
jedi==0.12.1
Jinja2==2.10
jsonschema==2.6.0
jupyter==1.0.0
jupyter-client==5.2.3
jupyter-console==5.2.0
jupyter-core==4.4.0
lxml==4.2.5
Markdown==2.6.11
MarkupSafe==1.0
meinheld==0.6.1
mistune==0.8.3
nbconvert==5.4.0
nbformat==4.4.0
notebook==5.7.0
numpy==1.15.1
openapi-codec==1.3.2
openpyxl==2.5.7
pandas==0.23.4
pandocfilters==1.4.2
parse==1.8.4
parse-type==0.4.2
parso==0.3.1
pdfrw==0.4
pexpect==4.6.0
pickleshare==0.7.4
Pillow==5.2.0
prometheus-client==0.3.1
prompt-toolkit==1.0.15
protobuf==3.6.1
psycopg2-binary==2.7.5
ptyprocess==0.6.0
pyasn1==0.4.4
pyasn1-modules==0.2.2
pycparser==2.19
Pygments==2.2.0
PyJWT==1.6.4
pyldap==3.0.0.post1
Pyphen==0.9.5
python-dateutil==2.7.3
python-ldap==3.1.0
pytz==2018.5
PyYAML==3.13
pyzmq==17.1.2
qtconsole==4.4.1
raven==6.9.0
requests==2.19.1
requests-toolbelt==0.8.0
Send2Trash==1.5.0
simplegeneric==0.8.1
simplejson==3.16.0
six==1.11.0
sqlparse==0.2.4
tensorboard==1.10.0
tensorflow==1.10.1
tensorflow-hub==0.1.1
termcolor==1.1.0
terminado==0.8.1
testpath==0.3.1
text-unidecode==1.2
tinycss2==0.6.1
tornado==5.1.1
traitlets==4.3.2
Unidecode==1.0.22
uritemplate==3.0.0
urllib3==1.23
wcwidth==0.1.7
WeasyPrint==0.42.3
webencodings==0.5.1
Werkzeug==0.14.1
widgetsnbextension==3.4.2
xworkflows==1.0.4
zeep==3.1.0
}}}
Replying to [comment:1 Carlton Gibson]:
> Hi...
>
> > After an upgrade from Django 2.0.6 to Django 2.1.1 and DRF 3.8.2...
>
> OK. DRF 3.8.2 supports both Django 2.x and Django 2.1.x. Can you please
downgrade Django (and keep DRF 3.8.2) and verify it works there?
> (Then at least the change is presumably in Django, rather than DRF or
your code.)
>
> If so, could you then git bisect to find the change? (See
[https://docs.djangoproject.com/en/dev/internals/contributing/triaging-
tickets/#bisecting-a-regression the guide on using bisect].)
>
> Ideally we need to reduce this down to an issue that only involves
Django itself...
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by zorky):
Thanks for your response,
But with Django 2.0.6 , pagination worked fine with the same Oracle server
v 11.2.0 (sorry, in the bug, I wrote v 11.0.2 but it's v 11.2.0.4, I asked
to the DBA), and on this page
https://docs.djangoproject.com/en/2.1/releases/2.0/#dropped-support-for-
oracle-11-2, they write "The end of upstream support for '''Oracle 11.2 is
Dec. 2020'''"
Replying to [comment:2 Simon Charette]:
> I'm afraid this has to do with your Oracle version.
>
> Django 2.0 dropped support for Oracle < 12.1 so I assume it happened to
work for your use case but changes such as #28670 which are relied on by
pagination might have broken it.
>
> https://docs.djangoproject.com/en/2.1/releases/2.0/#dropped-support-for-
oracle-11-2
> https://code.djangoproject.com/wiki/SupportedDatabaseVersions#Oracle
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Old description:
New description:
with python 3.6, Oracle 11.2.0.4
cx-Oracle 5.2.1 or 7.0
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: zorky | Owner: nobody
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette):
@zorky,
The release note I pointed to mentions that ''Django 2.0 officially
supports Oracle 12.1+'' which means that only Django 1.11 LTS, which is
supported until April 2020, officially support Oracle 11.2. Even if the
set of features your application was relying on was working fine on Django
2.0 doesn't mean it officially supported your version of Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:6>
Django
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 2.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
orm,oracle,pagination | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by DUVAL Olivier):
Ok thanks, I understand. I'm going downgrade Django to 2.0.6 and wait for
Oracle v 12 version upgrade in my organization in October / November.
Replying to [comment:6 Simon Charette]:
> @zorky,
>
> The release note I pointed to mentions that ''Django 2.0 officially
supports Oracle 12.1+'' which means that only Django 1.11 LTS, which is
supported until April 2020, officially support Oracle 11.2. Even if the
set of features your application was relying on was working fine on Django
2.0 doesn't mean it officially supported your version of Oracle.
--
Ticket URL: <https://code.djangoproject.com/ticket/29777#comment:7>