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.
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)
[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.
[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. .. ..
[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()
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.
--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)
Recommended Posts