Monday, July 15, 2013

SQLAlchemy - MySQL has gone away

In the previous blog post, I showed you how to use Django's Form with SQLAlchemy to manipulate MySQL db. There's something annoying me a lot after the day I post that article,  that's is the error:

"MySQL has gone away"

What's the...? I refreshed the page after an idle time, and the error appears from nowhere?! And if I refresh again, the app will load beautifully. What's going on here?

It seams like that is the common error when working with SQLAlchemy, and If you don't manage the session carefully and appropriately. The most common reason is the SQLAlchemy's session is still open while the connection to MySQL server timing out.

So here are what I have to do with my_db_utils.py:


1. Indicate the pool_recycle of the engine:

self.engine = create_engine(DB_ENGINE_URL, echo=False, pool_size = 100, pool_recycle=3600)

2. Using scoped_session:

self.Session = scoped_session(sessionmaker(bind=self.engine))

3. Remove session after commit:

def close(self):
self.Session.remove()

...

finally:
session.commit()
db.close()





References:

+ For more understanding about SQLAlchemy in comparison with Django's ORM: http://lucumr.pocoo.org/2011/7/19/sqlachemy-and-you/

+ http://mofanim.wordpress.com/2013/01/02/sqlalchemy-mysql-has-gone-away/

+ For the arguments of SQLAlchemy's create_engine: http://docs.sqlalchemy.org/en/rel_0_8/core/engines.html?highlight=create_engine#sqlalchemy.create_engine

+ For the usages of scoped_session: http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications