Introduction

I usually use SQLAlchemy when hitting MySQL from python. I'm not particularly inconvenienced when developing in Local, but after deploying with uwsgi in an environment close to the production, the phenomenon that the system crashed due to a mysterious error occurred frequently.

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

And

_mysql_exceptions.OperationalError: (2013, 'Lost connection to MySQL server during query')

And

sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

And this error ...

SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request

After investigating, many people have encountered this problem and have been offered various solutions, but since it is different depending on the blog, I tried it from the beginning.

I will leave a memorandum of what worked, but since it is a mystery as to whether it is true or false, I would appreciate it if you could tell me if it seems wrong.

Execution environment

Python3.5.2
SQLAlchemy 1.1.5
Flask 0.12

Countermeasures

As a premise, start from the place where engine and session are created as follows.

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import scoped_session, sessionmaker


metadata = MetaData()
engine = create_engine(uri, encoding='utf-8', pool_recycle=3600)
session = scoped_session(sessionmaker(autocommit=False,
                                      expire_on_commit = False,
                                      autoflush=True,
                                      bind=_engine))
metadata.create_all(bind=engine)

Review pool_recycle

[Reference] Python: Solve the problem of'MySQL server has gone away'in SQLAlchemy

According to the reference site, this error is

Occurs when SQLAlchemy issues a SQL statement while the connection to MySQL has timed out

So, first check wait_timeout on the MySQL side, and specify a smaller value in pool_recycle.

Check the wait_timeout setting on the SQL side and check

> show global variables like 'wait_timeout';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 600      |
+--------------------------+----------+
3 rows in set (0.00 sec)

It was made smaller than the specified time of pool_recycle (60 for trial this time). I felt that the number of errors had decreased a little, but the problem still persisted and I searched for another method.

Close session every time at the end of a request

[Reference] [Python] Battle with SQLAlchemy

According to the reference site, it is important to avoid omission of session close

@app.teardown_appcontext
def session_clear(exception):
    if exception and session.is_active:
        session.rollback()
    else:
        session.commit()

    session.close()

Flask's @ app.teardown_appcontext is called every time at the end of the request, so explicitly close the session here. Most of the errors have disappeared so far, but sometimes SQL goes Away.

SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request

Does not disappear no matter what. .. ..

Ping the mysqlserver to spawn a process

[Reference] MySQL server has gone away using Flask and SQLAlchemy

This is the last way to get there, and if the connection is closed, start the process first in advance. Is it the method? For the time being, all the errors have been resolved.

from sqlalchemy import exc
from sqlalchemy import event
from sqlalchemy.pool import Pool

@event.listens_for(Pool, "checkout")
def ping_connection(dbapi_connection, connection_record, connection_proxy):
    cursor = dbapi_connection.cursor()
    try:
        cursor.execute("SELECT 1")
    except:
        raise exc.DisconnectionError()
    cursor.close()

in conclusion

There are quite a few sites that mention this issue, but I've tried and quoted the ones that worked. The site I quoted this time also has various trial and error processes, so if you are stuck with similar problems, I think that you should also refer to that. I would appreciate it if you could tell me if there is a better way, or if the root cause is not there in the first place.

References

--Python: Solve the problem of'MySQL server has gone away'in SQLAlchemy (http://blog.amedama.jp/entry/2015/08/15/133322) -[Python] Battle with SQLAlchemy (http://d.hatena.ne.jp/heavenshell/20160220/1455987788) --MySQL server has gone away using Flask and SQLAlchemy (http://petitviolet.hatenablog.com/entry/20131113/1384329169)

[Python] SQLAlchemy error avoidance memorandum