SQLAlchemy – MYSQL has gone away

Posted on January 2, 2013

1


So this bug bit me on the arse and it took me so long to track down the necessary bits of information needed to fix it that I think it’s worth sharing the hard won wisdom.

The Problem

When using SQLAlchemy with MYSQL for your python webapp, you encounter one or several of these error messages (precisely which will depend on which DB driver you’re using):

MySQL Connection not available
2006, 'MySQL server has gone away'
StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back)

This will tend to happen on apps with very low traffic (around 1 hit a day)

The Cause

These problems are quite probably caused by the connection to the MYSQL server timing out while an SQLAlchemy session is still open. Note: this isn’t the only cause. Another one, if you have very long-running queries, is that MYSQL simply times out during the query. In any case the basic issue is that MYSQL (unlike some other DBs) closes connections after the time defined by the wait_timeout setting which you can see by running the query SHOW VARIABLES.

SQLAlchemy – specifically the ORM parts of it – manages sending queries to the DB through the Session object. A session is created at the start of a request and remains open until it is explicity closed. If sessions have been left open they will eventually time out on the MYSQL end. When the next request comes SQLAlchemy assumes the session is still valid when it is not, and so fails.

The Solution

The first thing to make sure of is that you set the ‘pool_recycle option’ when using SQLAlchemy ‘create_engine’ function (more here). The value of pool_recycle should be less than your MYSQLs wait_timeout value (both are in seconds).

The next and altogether knottier problem is to make sure you correctly manage your session life cycle. It’s well worth carefully reading this documentation. Essentially, you have to make sure that your session is correctly opened when your data request starts and closed when it’s done.

Opening the session is usually done automatically when you start a query, assuming you have a sessionmaker object configured. You will normally have to do that manually by using scoped_session and sessionmaker to generate a correctly configure, thread safe Session. A good example can be found here (its for the Flask web framework but the principle is the same).

Closing the session is the most important, and it’s a bit trickier. Depending on your web framework you may be able to define a function that will run at the end of every request which will tear down all active sessions with session.remove(). Alternatively you may prefer to explicitly close the session as soon as your data has been retrieved – my choice, since my data layer had to be able to work without the web layer.

If you are having this problem you’ll have to analyze your architecture to see where you are opening and closing sessions to make sure you don’t hold on to a reference to an open session. However it does get  confusing, since you can configure a session maker which will implicitly create sessions and effectively behave like a reference to a session object. For example if you use SQLSoup, the SQLSoup instance has a session variable which you can use to query. In the background SQLSoup is simply taking care of creating a session object based on the current engine config when it’s needed, but you still need to explicitly close it.

The Conclusion

No one-stop fixes i’m afraid – you’ll have to figure out how your app is using sessions in order to make sure they’re all nicely closed at the end of each request. It took me some time and a fair amount of refactoring to squash this one, but hopefully this should at least point you in the right direction.

 

Posted in: Computers