Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#30630 closed Bug (invalid)

MemoryError appears when using cx-Oracle 7.2 on Oracle 12c (12.1.0.2.0) with Python 3.7 (3.7.4).

Reported by: Alexandru Rudi Owned by: nobody
Component: Database layer (models, ORM) Version: 2.2
Severity: Normal Keywords: oracle cx-oracle python-3.7
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

These are the queries being executed before the crash:

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:

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)

Change History (11)

comment:1 Changed 3 years ago by Mariusz Felisiak

Resolution: invalid
Status: newclosed
Summary: MemoryError raised by inspectdb executed on Oracle 12c databaseMemoryError raised by inspectdb executed on Oracle 12c.

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 support channels.

comment:2 Changed 3 years ago by Alexandru Rudi

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.

comment:3 Changed 3 years ago by Mariusz Felisiak

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.

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

comment:5 Changed 3 years ago by Alexandru Rudi

Tried using the same version of instant client as the database version (12.1.0.2.0). Error still appears.

comment:6 Changed 3 years ago by Alexandru Rudi

Keywords: oracle added
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?

Version 0, edited 3 years ago by Alexandru Rudi (next)

comment:7 Changed 3 years ago by Alexandru Rudi

Keywords: cx-oracle python-3.7 added
Resolution: invalid
Status: closednew
Summary: MemoryError raised by inspectdb executed on Oracle 12c.MemoryError appears when using cx-Oracle 7.2 on Oracle 12c (12.1.0.2.0) with Python 3.7 (3.7.4).

comment:8 Changed 3 years ago by Jani Tiainen

Could you please check NLS settings from your db:

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.

comment:9 Changed 3 years ago by Mariusz Felisiak

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.

comment:10 in reply to:  8 Changed 3 years ago by Alexandru Rudi

Resolution: invalid
Status: newclosed

Replying to Jani Tiainen:

Could you please check NLS settings from your db:

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.

comment:11 Changed 3 years ago 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.

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