[Sqlalchemy-tickets] Issue #4182: pool_pre_ping does not properly detect the disconnect when the database user is dropped (zzzeek/sqlalchemy)

[Mike S. <iss...@bi...>]

New issue 4182: pool_pre_ping does not properly detect the disconnect when the database user is dropped
https://bitbucket.org/zzzeek/sqlalchemy/issues/4182/pool_pre_ping-does-not-properly-detect-the

Mike Shantz:

sqlalchemy version: 1.2.2
database: oracle (cx_Oracle 6.1, oracle client 12.1)

Dropping the user of an open connection results in an exception being raised, instead of a graceful attempt to reconnect. (Note that my actual use case also provides a creator callback to create_engine, which is able to recover from this condition on the new connection).

As demonstrated below, the attempt to close the cursor in the finally clause, when the connection has entered this error state, results in a second uncaught exception.

```
#!python

Python 3.5.2 (default, Nov 23 2017, 16:37:01) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> import sqlalchemy
>>> cx_Oracle.__version__
'6.1'
>>> cx_Oracle.clientversion
<built-in function clientversion>
>>> cx_Oracle.clientversion()
(12, 1, 0, 2, 0)
>>> sqlalchemy.__version__
'1.2.2'
>>> dsn = cx_Oracle.makedsn('xxx.xxx.xxx.xxx', 1521, service_name='some_oracle_service')
>>> user = 'valid_user'
>>> password = 'itsasecret'
>>> engine = sqlalchemy.create_engine('oracle://{user}:{passwd}@{dsn}'.format(user=user, passwd=password, dsn=dsn), pool_pre_ping=True)
>>> with engine.connect() as cx:
...     q = cx.execute('select * from dual')
...     print(q.fetchall())
... 
[('X',)]

```

At this point I drop the user from the database. Attempting to run the query a second time (with the same connection) I get:

```
#!python

>>> with engine.connect() as cx:
...     q = cx.execute('select * from dual')
...     print(q.fetchall())
... 
Traceback (most recent call last):
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 812, in _checkout
    result = pool._dialect.do_ping(fairy.connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 474, in do_ping
    cursor.execute(self._dialect_specific_select_one)
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 139 Serial number: 5637

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
    return self._connection_cls(self, **kwargs)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 90, in __init__
    if connection is not None else engine.raw_connection()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
    self.pool.unique_connection, _connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/pool.py", line 812, in _checkout
    result = pool._dialect.do_ping(fairy.connection)
  File "/home/mshantz/.virtualenvs/tmp-31dc1d1be1d35afd/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 474, in do_ping
    cursor.execute(self._dialect_specific_select_one)
sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 139 Serial number: 5637 (Background on this error at: http://sqlalche.me/e/e3q8)
>>> 

```