Using Flask-SQLAlchemy in Multiple uWSGI Processes

Question

I've been fighting a persistent error in my Flask application:

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

I'm using a mySQL server instance and the Flask-SQLAlchemy module. I double checked the expire time for connections on the mySQL instance and the reset time in the SQLAlchemy config. No issues there, connection pool is reset before the mySQL connections expire. I came to the conclusion that there must be some issue causing the connection to close and then the next user of that connection chokes.

I'm running the Flask application using uWSGI who spawns four processes. If I switch to a single process I can't reproduce the error. I'm guessing processes were stomping on each other through the shared connection pool. I added the following function to be run whenever uWSGI forks the process.

from uwsgidecorators import postfork

@postfork
def reset_db_connections():
    db.engine.dispose()

Works fine on startup and appeared to fix the issue when multiple requests were coming in at the same time. However, now when a process is reset, the next request on that process blows up with a similar, but not the same, SQL Server has gone away error. Here is the initial setup code for the database

def configure_db():
    from my_application.models import SomeModel
    db.create_all()

db = SQLAlchemy(app, session_options={'expire_on_commit': False})
configure_db()

Typical use of the database looks like this:

def save(self):
    try:
        db.session.add(self)
        db.session.commit()
    except Exception, ex:
        app.logger.error("Error saving campaign: %s" % ex)
        db.session.rollback()

Reads are one of the following two flavors:

user = db.session.query(User).filter(User.email == email).scalar()
user = User.query.filter(User.email == email).scalar()

My understanding is that Flask-SQLAlchemy uses scoped sessions so they should provide some protection in the multi process environment. Do I need to reset the connection pool on fork? Should I also be checking for live sessions when forking?

Update:

I've changed the fork to look like:

@postfork
def reset_db_connections():
    db.session.close_all()
    db.engine.dispose()
    db.create_scoped_session()

I still get the OperationalError, but it only happens during the fork and does not appear to interfere with requests. The stack trace does not include the fork though which does not allow me to catch it.

Traceback (most recent call last):

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 636, in _finalize_fairy fairy._reset(pool)

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 774, in _reset self._reset_agent.rollback()

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1563, in rollback self._do_rollback()

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1601, in _do_rollback self.connection._rollback_impl()

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 670, in _rollback_impl self._handle_dbapi_exception(e, None, None, None, None)

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception exc_info

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb)

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 668, in _rollback_impl self.engine.dialect.do_rollback(self.connection)

File "/home/vagrant/env/local/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py", line 2519, in do_rollback dbapi_connection.rollback()

Answer 1

You have to use lazy-apps=true option to uwsgi.

See this answer: uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac

I would not use the 'lazy' option as it is deprecated

When working with multiple processes with a master process, uwsgi initializes the application in the master process and then copies the application over to each worker process. The problem is if you open a database connection when initializing your application, you then have multiple processes sharing the same connection, which causes the error above.

In some cases, like using flask_admin or calling Base.metadata.create_all() in app/__init__.py, your app indeed makes a connection with the database already while being imported. With lazy-apps=false uwsgi forks after importing the module and thus the connection's file descriptor is copied to the children. With lazy-apps=true uwsgi forks itself and then does the imports. This way every sub-process has it's own connection.

uWSGI tries to (ab)use the Copy On Write semantics of the fork() call whenever possible. By default it will fork after having loaded your applications to share as much of their memory as possible. If this behavior is undesirable for some reason, use the lazy-apps option. This will instruct uWSGI to load the applications after each worker’s fork().