1. Mike Bayer Avatar Mike Bayer
  2. Untitled project
  3. sqlalchemy_old
  4. Issues

move can't connect handling outside of "except Empty" / "except AttributeError"

Issue #4028 resolved
Mike Bayer repo owner created an issue

When a pool can't connect to the DB, Python 3 does this:

#!

Traceback (most recent call last):
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1122, in _do_get
    return self._pool.get(wait, self._timeout)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get
    raise Empty
sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect
    return fn()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 328, in unique_connection
    return _ConnectionFairy._checkout(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout
    rec = pool._do_get()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get
    self._dec_overflow()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get
    return self._create_connection()
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 461, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/pool.py", line 651, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 105, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.5/site-packages/sqlalchemy/engine/default.py", line 393, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib64/python3.5/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib64/python3.5/site-packages/MySQLdb/connections.py", line 191, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on 'dbhost_obviously' (4)")

this confuses everyone. move the handling outside of Empty.

Comments (5)

  2. Mike Bayer reporter

    we have the same issue illustrated in #4046 in terms of the engine. 

    from sqlalchemy import create_engine


e = create_engine("mysql://scott:tiger@localhost/test")

c = e.connect()

input("ok do the thing")

trans = c.begin()

try:
    c.execute("select 1")
except:
    c.execute("select 2")

    here's the current behavior due to the attribute catches (when it says "ok do the thing", you kill the MySQL connection using "kill", then press enter):

    #!

[classic@photon2 sqlalchemy]$ /opt/python3.6/bin/python3 test.py 
ok do the thing
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 292, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

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

Traceback (most recent call last):
  File "test.py", line 13, in <module>
    c.execute("select 1")
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 939, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1097, in _execute_text
    statement, parameters
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1189, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1182, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 292, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: 'select 1']

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1112, in _execute_context
    conn = self.__connection
AttributeError: 'Connection' object has no attribute '_Connection__connection'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1114, in _execute_context
    conn = self._revalidate_connection()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 424, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back

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

Traceback (most recent call last):
  File "test.py", line 15, in <module>
    c.execute("select 2")
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 939, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1097, in _execute_text
    statement, parameters
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1121, in _execute_context
    None, None)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception
    exc_info
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1114, in _execute_context
    conn = self._revalidate_connection()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 424, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'select 2']

    with upcoming change:

    #!

[classic@photon2 sqlalchemy]$ /opt/python3.6/bin/python3 test.py 
ok do the thing
Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 292, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')

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

Traceback (most recent call last):
  File "test.py", line 13, in <module>
    c.execute("select 1")
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/default.py", line 504, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/opt/python3.6/lib/python3.6/site-packages/MySQLdb/connections.py", line 292, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: 'select 1']

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1125, in _execute_context
    conn = self._revalidate_connection()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 427, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back

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

Traceback (most recent call last):
  File "test.py", line 15, in <module>
    c.execute("select 2")
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 942, in execute
    return self._execute_text(object, multiparams, params)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1104, in _execute_text
    statement, parameters
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1132, in _execute_context
    None, None)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/util/compat.py", line 186, in reraise
    raise value.with_traceback(tb)
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1125, in _execute_context
    conn = self._revalidate_connection()
  File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 427, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'select 2']
  5. Mike Bayer reporter

    Don't do recovery operations under Empty/AttributeError

    Made some adjustments to :class:.Pool and :class:.Connection such that recovery logic is not run underneath exception catches for pool.Empty, AttributeError, since when the recovery operation itself fails, Python 3 creates a misleading stack trace referring to the Empty / AttributeError as the cause, when in fact these exception catches are part of control flow.

    Change-Id: Id3ed9a8f96ce4ccb4009c94af30ddc2ddb9818b9 Fixes: #4028

    → <<cset f87069a3b0e9>>

  6. Log in to comment
Assignee
–
Type
bug
Priority
minor
Status
resolved
Component
pool
Milestone
1.2
Votes
0
Watchers
2
Jira: the preferred issue tracker for Bitbucket. Join the team!