[Django] #30630: MemoryError raised by inspectdb executed on Oracle 12c database
Django
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
AlexanderRavenheart |
Type: Bug | Status: new
Component: Database | Version: 2.2
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
These are the queries being executed before the crash:
{{{#!python
Query: ALTER SESSION SET NLS_TERRITORY = 'AMERICA'
Params: []
Query: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' TIME_ZONE = 'UTC'
Params: []
Query: SELECT 1 FROM DUAL WHERE DUMMY LIKE TRANSLATE(:arg0 USING
NCHAR_CS) ESCAPE TRANSLATE('\' USING NCHAR_CS)
Params: {':arg0': 'X'}
}}}
This is the traceback output in the command line:
{{{#!python
Traceback (most recent call last):
File ".\manage.py", line 17, in <module>
execute_from_command_line(sys.argv)
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\__init__.py", line 381, in
execute_from_command_line
utility.execute()
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\__init__.py", line 375, in execute
self.fetch_command(subcommand).run_from_argv(self.argv)
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\base.py", line 323, in run_from_argv
self.execute(*args, **cmd_options)
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\base.py", line 364, in execute
output = self.handle(*args, **options)
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\commands\inspectdb.py", line 34, in handle
for line in self.handle_inspection(options):
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\core\management\commands\inspectdb.py", line 47, in
handle_inspection
with connection.cursor() as cursor:
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\base\base.py", line 256, in cursor
return self._cursor()
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\base\base.py", line 233, in _cursor
self.ensure_connection()
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\base\base.py", line 217, in ensure_connection
self.connect()
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\base\base.py", line 197, in connect
self.init_connection_state()
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\oracle\base.py", line 259, in
init_connection_state
['X'])
File "D:\Rudi\Workspace\Visual Studio\PTT Lite\env\lib\site-
packages\django\db\backends\oracle\base.py", line 510, in execute
return self.cursor.execute(query, self._param_generator(params))
MemoryError
}}}
The SELECT query returns 1 as a result if executed in Oracle SQL Developer
so I assume the error is not in the execution of that command.
I don't know if it's a bug in Django, cx-Oracle or at some other level or
if it's a configuration issue and have no clue how to proceed.
Database information:
- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
Production
- PL/SQL Release 12.1.0.2.0 - Production
- "CORE 12.1.0.2.0 Production"
- TNS for Linux: Version 12.1.0.2.0 - Production
- NLSRTL Version 12.1.0.2.0 - Production
Environment:
- Python 3.7.4 64-bit
- cx-Oracle 7.2.0
- Oracle Instant Client Base Windows x64 version 19.3 (zip format)
--
Ticket URL: <https://code.djangoproject.com/ticket/30630>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: AlexanderRavenheart | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
I'm not able to reproduce this issue, but I don't think that's an issue in
Django. At the beginning I would try to install instance client for the
same version i.e. "Oracle Instant Client Base Windows x64 version
12.1.0.2.0" (I know that this should "theoretically" works with 19.3), you
can also check Visual Studio compatibility.
To confirm that it is not related with Django you can try to reproduce
this issue with using `cx_Oracle.cursor`:
{{{
import cx_Oracle
connection = cx_Oracle.connect("user", "password", "TNS")
cursor = connection.cursor()
cursor.execute(".....")
}}}
Please use one of
[https://code.djangoproject.com/wiki/TicketClosingReasons/UseSupportChannels
support channels].
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: AlexanderRavenheart | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by AlexanderRavenheart):
{{{#!python
import cx_Oracle
connection = cx_Oracle.connect("user", "password", "TNS")
cursor = connection.cursor()
cursor.execute(".....")
}}}
I executed the query commands as run by inspectdb using directly cx-Oracle
by following the steps specified above without changing anything else in
the setup/environment and everything executed without any issues so it is
not a Visual Studio, Oracle Instant Client or cx-Oracle issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: AlexanderRavenheart | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Probably it will work also with cursor wrapped by Django:
{{{
from django.db import connection:
with connection.cursor() as cursor:
cursor.execute(....)
}}}
I think that memory issue is related with more complicated query.
`init_connection_state()` is used when Django establishes an connection,
so it should crash not only in `inspectdb`.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: AlexanderRavenheart | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jani Tiainen):
As instructed use same version of instant client as your database version.
Sometimes oracle is really picky.
Also note that sql developer uses JDBC not OCI. You could try to use
sqlplus which uses OCI and shoul give some clue if it is OCI thats acting
funny here.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: AlexanderRavenheart | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by AlexanderRavenheart):
Tried using the same version of instant client as the database version
(12.1.0.2.0). Error still appears.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: => oracle
Comment:
{{{#!python
def __init__(self, connection):
self.cursor = connection.cursor()
#self.cursor.outputtypehandler = self._output_type_handler
}}}
Disabling that line in base.py in class FormatStylePlaceholderCursor
removes the MemoryError. No other changes required to default Django 2.2
implementation.
Without the output type handler, the inspectdb command completed
successfully and generated models for all the tables found in the
database.
I do not know how to proceed any further and don't even know the negative
effects of disabling that handler. Is is still required?
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:6>
Django
with Python 3.7 (3.7.4).
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
python-3.7 | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* keywords: oracle => oracle cx-oracle python-3.7
* status: closed => new
* resolution: invalid =>
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:7>
Django
with Python 3.7 (3.7.4).
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle cx-oracle | Triage Stage:
python-3.7 | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Jani Tiainen):
Could you please check NLS settings from your db:
{{{#!sql
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM NLS_INSTANCE_PARAMETERS;
}}}
Also note that following settings takes place in environment before
connection is opened:
{{{
NLS_LANG=.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=TRUE
}}}
{{{ _output_type_handler }}} is used to properly read numerical values
from db. It shouldn't affect model creation but it has effect when reading
values from db.
Also if you could please try older versions of cx_Oracle just to rule out
that it's some bug in cx_Oracle itself.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:8>
Django
with Python 3.7 (3.7.4).
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: oracle cx-oracle | Triage Stage:
python-3.7 | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Thanks for investigation, but I still don't believe that it is an issue in
Django, it's probably some `cx_Oracle` issue with `outputtypehandler`
related with a specific database, environment, OS, etc. configuration. I
agree with Jani you could try to check with previous versions of
`cx_Oracle` i.e. 7.1, 7.0, 6.X. If it works with other versions of
`cx_Oracle` then you should report this issue in https://github.com/oracle
/python-cx_Oracle/issues.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:9>
Django
with Python 3.7 (3.7.4).
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Component: Database layer | Version: 2.2
(models, ORM) |
Keywords: oracle cx-oracle | Triage Stage:
python-3.7 | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => invalid
Comment:
Replying to [comment:8 Jani Tiainen]:
> Could you please check NLS settings from your db:
> {{{#!sql
> SELECT * FROM NLS_DATABASE_PARAMETERS;
> SELECT * FROM NLS_INSTANCE_PARAMETERS;
> }}}
From NLS_DATABASE_PARAMETERS:
||= PARAMETER =||= VALUE=||
||NLS_RDBMS_VERSION|| 12.1.0.2.0||
||NLS_NCHAR_CONV_EXCP|| FALSE||
||NLS_LENGTH_SEMANTICS|| BYTE||
||NLS_COMP|| BINARY||
||NLS_DUAL_CURRENCY|| $||
||NLS_TIMESTAMP_TZ_FORMAT|| DD-MON-RR HH.MI.SSXFF AM TZR||
||NLS_TIME_TZ_FORMAT|| HH.MI.SSXFF AM TZR||
||NLS_TIMESTAMP_FORMAT|| DD-MON-RR HH.MI.SSXFF AM||
||NLS_TIME_FORMAT|| HH.MI.SSXFF AM||
||NLS_SORT|| BINARY||
||NLS_DATE_LANGUAGE|| AMERICAN||
||NLS_DATE_FORMAT|| DD-MON-RR||
||NLS_CALENDAR|| GREGORIAN||
||NLS_NUMERIC_CHARACTERS|| .,||
||NLS_NCHAR_CHARACTERSET|| AL16UTF16||
||NLS_CHARACTERSET|| WE8ISO8859P1||
||NLS_ISO_CURRENCY|| AMERICA||
||NLS_CURRENCY|| $||
||NLS_TERRITORY|| AMERICA||
||NLS_LANGUAGE|| AMERICAN||
From NLS_INSTANCE_PARAMETERS:
||= PARAMETER =||= VALUE=||
||NLS_LANGUAGE|| AMERICAN||
||NLS_TERRITORY|| AMERICA||
||NLS_COMP|| BINARY||
||NLS_LENGTH_SEMANTICS|| BYTE||
||NLS_NCHAR_CONV_EXCP|| FALSE||
The rest of the values are (null).
> Also note that following settings takes place in environment before
connection is opened:
> {{{
> NLS_LANG=.AL32UTF8
> ORA_NCHAR_LITERAL_REPLACE=TRUE
> }}}
Can these be configured from settings.py?
> {{{ _output_type_handler }}} is used to properly read numerical values
from db. It shouldn't affect model creation but it has effect when reading
values from db.
>
> Also if you could please try older versions of cx_Oracle just to rule
out that it's some bug in cx_Oracle itself.
Tested with 7.1 and 7.0, both worked without issue, so it seems to be a
regression in cx_Oracle, even though I scanned their changelogs twice and
no mention of a change that would impact this. Thank you for being
understanding and guiding me in the right direction. I will raise a ticket
on their end.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:10>
Django
with Python 3.7 (3.7.4).
-------------------------------------+-------------------------------------
Reporter: Alexandru Rudi | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 2.2
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: oracle cx-oracle | Triage Stage:
python-3.7 | Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alexandru Rudi):
For anyone else encountering this issue the fix is included in cx_Oracle
7.2.1 as mentioned here: https://github.com/oracle/python-
cx_Oracle/issues/330.
--
Ticket URL: <https://code.djangoproject.com/ticket/30630#comment:11>