Does this Flask-SQLAchemy thread-based session cause a MySQL Server Error?

Question

I have a web application that runs long jobs that are not dependent on user sessions. For this, I have an implementation for a Flask-SQLAlchemy session with a thread. The problem is several times a day, I get a MySQL server has gone away error when visiting my site. The site always loads after updating. I think the problem is with these streaming local sessions, but I'm not sure.

This is my implementation of the stream localization area:

 @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 as follows:

 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 to add

app.teardown_request(Exception=None)

Decorator, which is executed at the end of each request. I am currently experiencing a similar problem, and it seems that today I really resolved its use.

 @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 Raw SQLAlchemy , so you may have differences.

In documents

Break callbacks are special callbacks at which they are executed at another point. Strictly speaking, they are independent of the processing of actual requests, since they are related to the life cycle of the RequestContext object. When the request context is exposed, the teardown_request () functions are called.

In my case, I open a new scoped_session for each query, requiring me to delete it at the end of each query ( Flask-SQLAlchemy may not be needed). In addition, the teardown_request function is thrown by Exception if it occurred during the context. In this case, if an exception occurred (it is possible that the transaction was not deleted or a rollback was not required), we check whether there was an exception and a rollback.

If this does not work for my own testing, the next thing I was going to do is session.commit() at each break, just to make sure everything is cleared

---

UPDATE: a message also appears stating that MySQL disconnects connections after 8 hours, which will damage the session.

set pool_recycle=3600 in the configuration of your engine or in the setting <MySQL timeout. This is combined with the correct viewing of the session (closing sessions).