#29777 closed Bug (invalid)
ORA-00933: SQL command not properly ended with pagination in a ModelViewSet
Reported by: | DUVAL Olivier | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 2.1 |
Severity: | Normal | Keywords: | orm, oracle, pagination |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
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
Code highlighting:
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.2.0.4
cx-Oracle 5.2.1 or 7.0
Change History (7)
follow-up: 3 comment:1 by , 6 years ago
follow-up: 4 comment:2 by , 6 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
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
comment:3 by , 6 years ago
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 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 the guide on using bisect.)
Ideally we need to reduce this down to an issue that only involves Django itself...
comment:4 by , 6 years ago
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 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
comment:5 by , 6 years ago
Description: | modified (diff) |
---|
follow-up: 7 comment:6 by , 6 years ago
@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.
comment:7 by , 6 years ago
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 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.
Hi...
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 the guide on using bisect.)
Ideally we need to reduce this down to an issue that only involves Django itself...