Why does SQLAlchemy/mysql keep timing out on me?

Question

I have 2 functions that need to be executed and the first takes about 4 hours to execute. Both use SQLAlchemy:

def first():
    session = DBSession
    rows = session.query(Mytable).order_by(Mytable.col1.desc())[:150] 
    for i,row in enumerate(rows):
        time.sleep(100)
    print i, row.accession

def second():
    print "going onto second function"
    session = DBSession
    new_row = session.query(Anothertable).order_by(Anothertable.col1.desc()).first() 
    print 'New Row: ', new_row.accession


first()
second()

And here is how I define DBSession:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy import create_engine

engine = create_engine('mysql://blah:blah@blah/blahblah',echo=False,pool_recycle=3600*12)
DBSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()    
Base.metadata.bind = engine   

first() finishes fine (takes about 4 hrs) and I see "going onto second function" printed then it immediately gives me an error:

sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server has gone away')

From reading the docs I thought assigning session=DBSession would get two different session instances and so that second() wouldn't timeout. I've also tried playing with pool_recycle and that doesn't seem to have any effect here. In the real world, I can't split first() and second() into 2 scripts: second() has to execute immediately after first()

Answer 1

Your engine (not session) keeps a pool of connections. When a mysql connection has not been used for several hours, mysql server closes the socket, this causes a "Mysql server has gone away" error when you try to use this connection. If you have a simple single-threaded script then calling create_engine with pool_size=1 will probably do the trick. If not, you can use events to ping the connection when it is checked out of the pool. This great answer has all the details:

SQLAlchemy error MySQL server has gone away

Answer 2

assigning session=DBSession would get two different session instances

That simply isn't true. session = DBSession is a local variable assignment, and you cannot override local variable assignment in Python (you can override instance member assignment, but that's unrelated).

Another thing to note is that scoped_session produces, by default, a thread-local scoped session (i.e. all codes in the same thread all have the same session). Since you call first() and second() in the same thread, they are one and the same session.

One thing you can do is to use regular (unscoped) session, just manage your session scope manually and create a new session in both function. Alternatively, you can check the doc about how to define custom session scope.

Answer 3

It doesn't look like you're getting separate Session instances. If the first query is successfully committing, then your Session could be expiring after that commit.

Try setting auto-expire to false for your session:

DBSession = scoped_session(sessionmaker(expire_on_commit=False, autocommit=False, autoflush=False,  bind=engine))

and then commit later.