Skip to content

With cx_oracle 8.3 connection_invalidated flag is not set #7748

New issue
New issue
Closed
Closed
With cx_oracle 8.3 connection_invalidated flag is not set#7748
Labels
PRs (with tests!) welcomea fix or feature which is appropriate to be implemented by volunteersbugSomething isn't workingoracle
Milestone
 
1.4.x
@kotofos

Description

@kotofos
kotofos
opened on Feb 21, 2022 · edited by kotofos

Describe the bug

The connection_invalidated flag is not set when the oracle server is restarted or on other connection problems when using cx_oracle 8.3 and oracle 19

How To Reproduce

from sqlalchemy import create_engine
from sqlalchemy.exc import DBAPIError

engine = create_engine(
    'oracle://chinook:password@127.0.0.1:1521/?service_name=ORCLPDB1',
    max_identifier_length=128, # hide warning
)

engine.connect()
Session = sessionmaker(bind=engine)
session = Session()
session.execute('select 1 from DUAL')

_ = input('Please restart server now, wait for startup and press enter')

try:
    session.execute('select 1 from DUAL')
    print('Not failed, unexpected, please retry')
except DBAPIError as e:
    if e.connection_invalidated:
        print('Connection invalidated, retrying')
        session.rollback()
        session.execute('select 1 from DUAL')
        print('ok')
    else:
        print('connection_invalidated flag not set')

Error

restart server now, wait for it and press enter
connection_invalidated flag not set
Error closing cursor
Traceback (most recent call last):
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1276, in _execute_context
    self.dialect.do_execute(
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 608, in do_execute
    cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: DPI-1080: connection was closed by ORA-3113

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1368, in _safe_close_cursor
    cursor.close()
cx_Oracle.DatabaseError: DPI-1010: not connected
Exception during reset or similar
Traceback (most recent call last):
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 697, in _finalize_fairy
    fairy._reset(pool)
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 893, in _reset
    pool._dialect.do_rollback(self)
  File "/home/kotofos/Documents/slashdb/slashdb_venv39/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 558, in do_rollback
    dbapi_connection.rollback()
cx_Oracle.DatabaseError: DPI-1010: not connected

Versions

  • OS: Linux 5.3.18-59.37-preempt SMP PREEMPT Mon Nov 22 12:29:04 UTC 2021 (d10168e) x86_64 x86_64 x86_64 GNU/Linux
  • Python: 3.9.8
  • SQLAlchemy: 1.3.24; 1.4.31;
  • Database: Oracle 19 EE docker image container-registry.oracle.com/database/enterprise:19.3.0.0
  • DBAPI: cx_oracle 8.3; 8.2

Additional context

cx_oracle 7.3 and 8.1 works fine

Seems like cx_oracle introduced changes to disconnect error messages and codes. May be related: https://cx-oracle.readthedocs.io/en/latest/release_notes.html#version-8-2-may-2021​​ paragraph 10

My workaround was to try patch sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle.is_disconnect and it worked.

    def patched_is_disconnect(self, e, connection, cursor):
        err_msg = "connection was closed"  # patched
        (error,) = e.args
        if isinstance(
                e, (self.dbapi.InterfaceError, self.dbapi.DatabaseError)
        ) and ("not connected" in str(e) or err_msg in str(e)):
            return True
...

Activity

kotofos
added
requires triageNew issue that requires categorization
on Feb 21, 2022
agilevic

agilevic commented on Feb 21, 2022

@agilevic
agilevic
on Feb 21, 2022
Contributor

Thanks @kotofos

zzzeek
added
bugSomething isn't working
oracle
PRs (with tests!) welcomea fix or feature which is appropriate to be implemented by volunteers
on Feb 21, 2022
zzzeek
added this to the 1.4.x milestone on Feb 21, 2022
zzzeek

zzzeek commented on Feb 21, 2022

@zzzeek
zzzeek
on Feb 21, 2022
Member

please confirm this patch resolves

diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 3f8109a125..2e49c172ea 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -1240,8 +1240,10 @@ class OracleDialect_cx_oracle(OracleDialect):
             # ORA-03135: connection lost contact
             # ORA-01033: ORACLE initialization or shutdown in progress
             # ORA-02396: exceeded maximum idle time, please connect again
+            # DPI-1080: connection was closed by ORA-3113
+            # DPI-1010: not connected
             # TODO: Others ?
-            return error.code in (28, 3114, 3113, 3135, 1033, 2396)
+            return error.code in (28, 3114, 3113, 3135, 1033, 2396, 1080, 1010)
         else:
             return False
kotofos

kotofos commented on Feb 21, 2022

@kotofos
kotofos
on Feb 21, 2022
Author

While debugging I was having error.code == 0, so it will not work. That why I added "connection was closed" check.

error.code == 0
str(e) == "DPI-1080: connection was closed by ORA-3113"

Though, this may be some issue from cx_oracle side.

zzzeek

zzzeek commented on Feb 21, 2022

@zzzeek
zzzeek
on Feb 21, 2022
Member

that's IMO a bug in cx_Oracle. if they have a code in their string, that should be error.code. can you report with them and/or get clarification ? thanks.

zzzeek

zzzeek commented on Feb 21, 2022

@zzzeek
zzzeek
on Feb 21, 2022
Member

cc @anthony-tuininga

agilevic

agilevic commented on Feb 21, 2022

@agilevic
agilevic
on Feb 21, 2022
Contributor

@zzzeek 's patch makes sense to me. I'd be surprised if the exception object be created any differently, but it seems as though that might be the case seeing that there are other checks in the cx_oracle.is_disconnect.
The issue might be even with the ODPI library or even the interplay of cx_Oracle and the version of the Oracle client.
oracle/odpi@c0a406a

kotofos
mentioned this on Feb 22, 2022
anthony-tuininga

anthony-tuininga commented on Feb 23, 2022

@anthony-tuininga
anthony-tuininga
on Feb 23, 2022

@zzzeek, error.code is only populated when an Oracle error is generated. It is not populated for ODPI-C error messages. If we could go back in time we would populate error.code with the strings ORA-3113, DPI-1010 or DPI-1080. Changing that now, however, would be disruptive. In our own test suite we are simply examining the beginning of the error message which is always of the form XXX-NNNN: where XXX is one of DPI/ORA/etc. and NNNN is a number. That is likely the best approach to use for you, as well. If you have other suggestions, however, I'd be happy to hear them!

zzzeek

zzzeek commented on Feb 23, 2022

@zzzeek
zzzeek
on Feb 23, 2022
Member

hi Anthony -

well that's what we have to do for now, but I would suggest the possibility of adding other parameters, such as error.dpi_code. We like to keep away from parsing strings if at all possible as the format of these strings can change.

zzzeek

zzzeek commented on Feb 23, 2022

@zzzeek
zzzeek
on Feb 23, 2022
Member

oh or , of course, if there's a reliable flag like "connection.is_disconnected" or something like that, or more like what we do, on the exception object there's a flag "is_disconnect" which indicates if this error message means the connection has been disconnected or is otherwise no longer usable.

8 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    PRs (with tests!) welcomea fix or feature which is appropriate to be implemented by volunteersbugSomething isn't workingoracle

    Type

    No type

    Projects

    No projects

    Milestone

    Relationships

    None yet

      Development

      No branches or pull requests

        Participants

        @zzzeek@agilevic@kotofos@anthony-tuininga@sqla-tester

        Issue actions
          With cx_oracle 8.3 connection_invalidated flag is not set · Issue #7748 · sqlalchemy/sqlalchemy