Resetting session in Flask

[robinandeer]

Hi,

I've been using sqlservice with Flask now for a while - generally works great with the implementation in #3, however, the server seems too loose connection to the database after some time of inactivity.

I also see a lot of errors like this which are only resolved after a server restart:

Traceback (most recent call last):
  File "/home/hiseq.clinical/miniconda/envs/cgadmin/lib/python3.6/site-packages/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/hiseq.clinical/miniconda/envs/cgadmin/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1114, in _execute_context
    conn = self._revalidate_connection()
  File "/home/hiseq.clinical/miniconda/envs/cgadmin/lib/python3.6/site-packages/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

Can you perhaps help out and point me to the relevant area of where to perhaps catch such exceptions or help me figure out why the session doesn't seem to be resetting on new requests. I've been looking over the Flask-SQLAlchemy code but I'm not getting very far 😕

[dgilland]

What kind of database connection settings are you using for things like pool size, pool recycle, and pool timeout? And how do these compare to your database's connection timeout settings?

When you see these errors, are they continuously occurring throughout multiple requests? i.e. once this error happens, are subsequent requests also triggering the same error? Generally speaking, once SQLAlchemy detects an invalid connection, it should try to re-establish a fresh one for all connections in its pool.

Do you have any other teardown functions registered? Are any of these functions throwing uncaught exceptions that might prevent the database session from being cleaned up in FlaskSQLService?

You can also try using pessimistic connection handling with SQLAlchemy. With pessimistic connection handling, the connection is validated before it's checked out from the pool. If it's "bad", then that connection is discarded a new connection is created. With the upcoming SQLAlchemy v1.2 release, they're now including it:

• https://docs.sqlalchemy.org/en/latest/changelog/migration_12.html#pessimistic-disconnection-detection-added-to-the-connection-pool
• https://docs.sqlalchemy.org/en/latest/core/pooling.html#pool-disconnects-pessimistic

Otherwise, you can implement it yourself: https://docs.sqlalchemy.org/en/latest/core/pooling.html#custom-legacy-pessimistic-ping

A similar question was also asked here: https://github.com/dgilland/alchy/issues/26

[robinandeer]

Hey! Thanks again @dgilland!

What kind of database connection settings are you using for things like pool size, pool recycle, and pool timeout? And how do these compare to your database's connection timeout settings?

I set SQL_POOL_RECYCLE = 3600 but for the rest I use the defaults.

When you see these errors, are they continuously occurring throughout multiple requests? i.e. once this error happens, are subsequent requests also triggering the same error?

Yes, the same error occurs for each subsequent request.

Do you have any other teardown functions registered?

I don't believe so.. None that I have setup and I don't think any of my plugins set it up either.

I will continue testing around. Hopefully SQLAlchemy will release the new version soon 👍

[dgilland]

What database are you using? And what is its connection wait timeout? Curious if the SQL_POOL_RECYCLE is higher than the connection wait timeout or not.

You don't have to wait for SQLAlchemy 1.2 to implement the pessimistic connection handling. There are recipes for adding it manually.

Only other thing I can think of is to check whether there are any database transaction errors that aren't being rolled back correctly (this should be handled by the teardown method which removes/rolls back the transaction after the end of the request but if this method is being skipped for whatever reason then that would be a problem).

BTW, what version of SQLAlchemy/Flask are you using?

[robinandeer]

I use:

Flask==0.12 SQLAlchemy==1.1.5

By "teardown method" you mean the one decorated by @app.teardown_appcontext, yes? That function is just removing the session in your original implementation. I can try to test if it get's executed although testing it for errors that only occur "spontaneously" after one day are trickier of course..

I'm using MySQL 5.5.47 btw. I'm not sure how to check the wait timeout. Our IT department is handling the installation etc.

However, I do see some initially positive results since setting SQL_POOL_RECYCLE = 7200 (increased from 3600). Sorry, I don't really understand the implications of setting these options but I will try to read up on it!

Thanks for the help so far!!

[dgilland]

For MySQL, the variable is wait_timeout which you can check with:

$ mysql
MySQL> show variables like 'wait_timeout';

You want your SQL_POOL_RECYCLE to be less than the wait_timeout since MySQL will drop the connection after wait_timeout of no activity while SQLAlchemy won't explicitly get a new connection until after SQL_POOL_RECYCLE of no activity.

You may also want to upgrade to the latest SQLAlchemy (1.1.9) in case of any bug fixes that might be relevant.

Last area of investigation: What kind of transactions are you doing? Normal single phase commits? Two phase? Save points? Anything beyond the normal "BEGIN/COMMIT"?

[dgilland]

Closing this issue due to inactivity. Feel free to resurrect it or create a new issue if needed.