Does this thread-local Flask-SQLAchemy session cause a "MySQL server has gone away" error?

Question

I have a web application that runs long jobs that are independent of user sessions. To achieve this, I have an implementation for a thread-local Flask-SQLAlchemy session. The problem is a few times a day, I get a MySQL server has gone away error when I visit my site. The site always loads upon refresh. I think the issue is related to these thread-local sessions, but I'm not sure.

This is my implementation of a thread-local session scope:

@contextmanager
def thread_local_session_scope():
    """Provides a transactional scope around a series of operations.
    Context is local to current thread.
    """
    # See this StackOverflow answer for details:
    # http://stackoverflow.com/a/18265238/1830334
    Session = scoped_session(session_factory)
    threaded_session = Session()
    try:
        yield threaded_session
        threaded_session.commit()
    except:
        threaded_session.rollback()
        raise
    finally:
        Session.remove()

And here is my standard Flask-SQLAlchemy session:

@contextmanager
def session_scope():
    """Provides a transactional scope around a series of operations.
    Context is HTTP request thread using Flask-SQLAlchemy.
    """
    try:
        yield db.session
        db.session.commit()
    except Exception as e:
        print 'Rolling back database'
        print e
        db.session.rollback()
    # Flask-SQLAlchemy handles closing the session after the HTTP request.

Then I use both session context managers like this:

def build_report(tag):
    report = _save_report(Report())
    thread = Thread(target=_build_report, args=(report.id,))
    thread.daemon = True
    thread.start()
    return report.id

# This executes in the main thread.
def _save_report(report):
    with session_scope() as session:
        session.add(report)
        session.commit()
        return report

# These executes in a separate thread.
def _build_report(report_id):
    with thread_local_session_scope() as session:
        report = do_some_stuff(report_id)
        session.merge(report)

EDIT: Engine configurations

app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://<username>:<password>@<server>:3306/<db>?charset=utf8'
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

Answer 1

Try adding an

app.teardown_request(Exception=None)

Decorator, which executes at the end of each request. I am currently experiencing a similar issue, and it seems as if today I have actually resolved it using.

@app.teardown_request
def teardown_request(exception=None):
    Session.remove()
    if exception and Session.is_active:
        print(exception)
        Session.rollback()

I do not use Flask-SQLAlchemy Only Raw SQLAlchemy, so it may have differences for you.

From the Docs

The teardown callbacks are special callbacks in that they are executed at at different point. Strictly speaking they are independent of the actual request handling as they are bound to the lifecycle of the RequestContext object. When the request context is popped, the teardown_request() functions are called.

In my case, I open a new scoped_session for each request, requiring me to remove it at the end of each request (Flask-SQLAlchemy may not need this). Also, the teardown_request function is passed an Exception if one occured during the context. In this scenario, if an exception occured (possibly causing the transaction to not be removed, or need a rollback), we check if there was an exception, and rollback.

If this doesnt work for my own testing, the next thing I was going to do was a session.commit() at each teardown, just to make sure everything is flushing

---

UPDATE : it also appears MySQL invalidates connections after 8 hours, causing the Session to be corrupted.

set pool_recycle=3600 on your engine configuration, or to a setting < MySQL timeout. This in conjunction with proper session scoping (closing sessions) should do it.