AttributeError: 'Connection' object has no attribute '_Connection__connection'

[Daniel Birnstiel]

I am currently receiving the exception mentioned in the title on a production deployment of a flask application using SQLAlchemy.

This issue has happened before (closed in #1246 and #2317), but appears to have resurfaced again.

Some info about my environment:

% py --version
Python 3.4.3
% pip freeze | grep SQLAlchemy
SQLAlchemy==1.1.4

Database is an RDS instance on AWS using psycopg2==2.6.2 as a driver.

Full exception log: https://gist.github.com/Birne94/e6e7ba91d44a680ba09419f7aa9f5705 (also attached to this report)

The exception log shows two different exceptions happening:

• At first the server closes a connection, most likely due to a restart on the side of the database server. The query fails, page errors. I have a custom exception handler which will rollback the failed database session. Since this was a simple SELECT, I guess this is not needed (right?).

• Further access to the database fails with the exception mentioned in the title. As mentioned above, the failed transation was rolled back so there shouldn't be any issue with this one still existing. Again, the query was a simple select which should not start any transaction.

After restarting the server (uwsgi), everything went back to normal. Unfortunately, it was a priority to get the server up and running again, so I was unable to further investigate the issue by attaching a debugger.

I would guess that SQLAlchemy should be able to handle closed connections and be able to reconnect automatically.

If this is not the case, is there anything I can do to detect operations on an invalid connection and perform a manual reconnect?

Thanks for reading.

[Mike Bayer]

the AttributeError as given is only a cosmetic issue, and this will be resolved in #4028. Your application and/or the flask extensions in use are failing to emit a rollback on a transaction that it started, when a database error is encountered, before attempting to use the connection again.

the actual stack trace referring to your application which needs to be fixed is:

#!

Traceback (most recent call last):
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/app.py", line 1988, in wsgi_app
    response = self.full_dispatch_request()
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/app.py", line 1641, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/app.py", line 1544, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/app.py", line 1639, in full_dispatch_request
    rv = self.dispatch_request()
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/app.py", line 1625, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/views.py", line 84, in view
    return self.dispatch_request(*args, **kwargs)
  File "./inspora/webviews/utils.py", line 114, in dispatch_request
    return super().dispatch_request(*args, **kwargs)
  File "/www/inspora/venv/lib/python3.4/site-packages/flask/views.py", line 149, in dispatch_request
    return meth(*args, **kwargs)
  File "./inspora/webviews/styles.py", line 871, in get
    OutfitRecommendation.created_on > min_age).all()
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2645, in all
    return list(self)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2797, in __iter__
    return self._execute_and_instances(context)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/orm/query.py", line 2820, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 945, in execute
    return meth(self, multiparams, params)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1121, in _execute_context
    None, None)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
    exc_info
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 185, in reraise
    raise value.with_traceback(tb)
  File "/www/inspora/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1114, in _execute_context
    conn = self._revalidate_connection()
  File "/www/inspora/venv/lib/python3.4/site-packages/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

here is the most rudimentary form of this application error:

from sqlalchemy import create_engine

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

c = e.connect()

input(
    "kill the current database connection "
    "from the DB side, then press enter")

# start a transaction
trans = c.begin()

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

    # bug is here - need to trans.rollback() first
    c.execute("select 2")

[Mike Bayer]

Duplicate of #4028.

[Daniel Birnstiel]

I am pretty sure the transaction was rolled back as indicated in the log ("Rolling back database session because of..."). The code used for that is a flask teardown handler:

        def _session_fix(exception=None):
            if exception:
                warnings.warn("Rolling back database session because of "
                              "exception {}".format(exception))
                db_session.rollback()

            db_session.remove()

        app.teardown_appcontext(_session_fix)

Is there anything else to be done apart from calling session.rollback()?

[Mike Bayer]

that would be all that's needed assuming the connection is still being managed by that session. it looks like the DB itself killed the connection as part of the incident, and then you state the app didn't recover from this, which looks like you had connections in the pool that were themselves also shut down.

did you delete the attachment?

[Daniel Birnstiel]

Alright, thank you for the help so far. I will have a look at the connection pool.

The database connection is initialized through

engine2 = create_engine(config.DB_URI2, convert_unicode=True, echo=False)
Session2 = sessionmaker(autocommit=False, autoflush=False, bind=engine2)
db_session2 = scoped_session(Session2)

That session instance (db_session2) is then used through the whole application.

I had to remove the log since I accidentally uploaded a whole day's application logs while I intended to only upload a part of it. The exceptions can still be found in the gist I linked.

[Mike Bayer]

logs like these i very much need timestamps and the process ids as well as a complete series of events to try to see what was going on, the exceptions themselves are by design. what I'd want to do here is identify the programming pattern in flask that's causing this and then decide if work needs to be done improving that on my end, or theirs, or what. i can't reproduce this error as long as the rollback() is there.

[Mike Bayer]

you do need to confirm that your flask extensions are compatible with autocommit=False as that changes the transactional API of the Session.