[Python] SQLAlchemy error avoidance memorandum

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

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)

Recommended Posts

[Python] SQLAlchemy error avoidance memorandum
#python Python Japanese syntax error avoidance
Python memorandum
Python Memorandum 2
Python memorandum
python memorandum
python memorandum
Python memorandum
python memorandum
Python memorandum
Python basics memorandum
Python pathlib memorandum
Python memorandum (algorithm)
Python Error Handling
Python memorandum [links]
Python memorandum numbering variables
python memorandum (sequential update)
Python Not Implemented Error
Python memorandum (personal bookmark)
Python error list (Japanese)
Python basic memorandum part 2
[Python] Iterative processing_Personal memorandum
Memorandum @ Python OR Seminar
python memorandum super basic
[Python] for statement error
Effective Python Learning Memorandum Day 15 [15/100]
Cisco Memorandum _ Python config input
Effective Python Learning Memorandum Day 6 [6/100]
Effective Python Learning Memorandum Day 12 [12/100]
Effective Python Learning Memorandum Day 9 [9/100]
Effective Python Learning Memorandum Day 8 [8/100]
ABC memorandum [ABC163 C --managementr] (Python)
About python beginner's memorandum function
Memorandum @ Python OR Seminar: matplotlib
A memorandum about correlation [Python]
Effective Python Learning Memorandum Day 14 [14/100]
[youtube-dl] python3 SSL error (CERTIFICATE_VERIFY_FAILED)
Effective Python Learning Memorandum Day 1 [1/100]
Memorandum @ Python OR Seminar: Pulp
python metaclass and sqlalchemy declareative
Error when playing with python
Slice error in python (´ ; ω ; `)
Effective Python Learning Memorandum Day 13 [13/100]
A memorandum about Python mock
Effective Python Learning Memorandum Day 3 [3/100]
Effective Python Learning Memorandum Day 5 [5/100]
Memorandum @ Python OR Seminar: Pandas
PyCUDA build error handling memorandum
[python] Random number generation memorandum
Effective Python Learning Memorandum Day 4 [4/100]
Memorandum @ Python OR Seminar: scikit-learn
Effective Python Learning Memorandum Day 7 [7/100]
Effective Python Learning Memorandum Day 2 [2/100]
Error resolution python version check
python parallel / asynchronous execution memorandum
[python] Error when installing library ramkan
ABC memorandum [ABC159 C --Maximum Volume] (Python)
Python pywin32 (win32com) Excel operation memorandum
Error when building mac python environment
Python error: ModuleNotFoundError: No module named'flask'
[Python] A memorandum of beautiful soup4