SQLAlchemy + oursql Connection Timeout Issue

Bug #906183 reported by Stephen Hu on 2011-12-19
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
oursql
Undecided
Unassigned

Bug Description

i'm running into the MySQL timeout issue (MySQL server has gone away) using oursql in conjunction with SQLAlchemy. from the SQLAlchemy documentation:

Connection Timeout

MySQL features an automatic connection close behavior, for connections that have been idle for eight hours or more. To circumvent having this issue, use the pool_recycle option which controls the maximum age of any connection.

My application is a web application where i hold onto connections persistently and i've added the pool_recycle option as suggested by sqlalchemy, but wondering if somehow this is not being recognized by oursql. i have yet to look into any of the SQLAlchemy code to see if what i imagine them to be doing is testing their connections periodically to essentially keep it alive.

env:

Python = 2.7.1
SQLAlchemy = 0.7.3
oursql = 0.9.3
MySQL = 5.1.58
Linux = 2.6.x (64 bit)

here's a snippet of my python code, sorry I canot provide a runnable reproducer at the moment:

conn_str = driver + 'mysql+oursql://' + user + ':' + password + '@' + host + ':' + \
    str(port) + '/' + db

self.engine = sqlalchemy.create_engine( conn_str, echo=False, pool_recycle=3600 ) # number of seconds for a connection
self.metadata = sqlalchemy.MetaData(bind=self.engine)
self.connection = self.engine.connect()
self.Session = sqlalchemy.orm.sessionmaker(bind=self.engine)
self.session = self.Session()

Stephen Hu (goodruck) wrote :

i tried using another mysql driver (mysqldb) to rule out any potential issues with SQLAlchemy and found that the latest version of that exhibits the same behavior. upon realizing this, i started to look into my code and found that SQLAlchemy runs with autocommit=False, which means that you must commit (and close) your connections so i was holding onto connections continuously. setting this to true, each query would result in committing and returning the connection to the pool.

please close this bug.

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers