Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#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 DUVAL Olivier)

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)

comment:1 Changed 4 years ago 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 the guide on using bisect.)

Ideally we need to reduce this down to an issue that only involves Django itself...

comment:2 Changed 4 years ago by Simon Charette

Resolution: invalid
Status: newclosed

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

Last edited 4 years ago by Simon Charette (previous) (diff)

comment:3 in reply to:  1 Changed 4 years ago by DUVAL Olivier

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 in reply to:  2 Changed 4 years ago by DUVAL Olivier

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 Changed 4 years ago by DUVAL Olivier

Description: modified (diff)

comment:6 Changed 4 years ago 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.

comment:7 in reply to:  6 Changed 4 years ago 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 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.

Note: See TracTickets for help on using tickets.
Back to Top