[Python]SQLAlchemyのエラー回避備忘録

# はじめに

普段pythonからMySQLを叩く際，[SQLAlchemy](http://www.sqlalchemy.org/)を使っています．Localで開発する際には特に不自由していないのですが，本番に近い環境にuwsgiでdeployしてからというもの謎のエラーでシステムが落ちる現象が多発してしまいました．

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

とか

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

とか

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

あとこのエラー...

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

調べたところ，多くの方がこの問題にぶつかっており，解決方法を様々提示していただいているのですが，ブログによって書いてあることがまちまちなので，片っ端から試しました．

備忘録としてどれが効いたかを残しておきますが，真偽のほどは謎なのでもし間違っているようでしたら教えていただけると幸いです．

# 実行環境

- Python3.5.2
- SQLAlchemy 1.1.5
- Flask 0.12

# 対策

前提として以下のようにengine, sessionを作成しているところからスタートします．

```py3
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)
```

## pool_recycleを見直す

[参考][Python: SQLAlchemy で 'MySQL server has gone away' になる問題を解決する](http://blog.amedama.jp/entry/2015/08/15/133322)

参考サイト様によるとこのエラーは

> MySQL とのコネクションがタイムアウトを起こした状態で SQLAlchemy が SQL 文を発行したときに発生する

そうなのでまずMySQL側のwait_timeoutを確認し，それより小さい値を`pool_recycle`に指定します．

SQL側でwait_timeoutの設定を確認し，

```
> 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)
```

`pool_recycle`の指定時間よりも小さくしました（今回は試しに60）．若干エラー数は減った気がしたのですが，それでも問題は解消せず，別の方法を探しました．

## リクエストの最後に毎回sessionをcloseする

[参考][[Python] SQLAlchemy との戦い](http://d.hatena.ne.jp/heavenshell/20160220/1455987788)

参考サイト様によるとセッションのclose漏れを回避するのが重要そう

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

    session.close()
```

Flaskの`@app.teardown_appcontext`はリクエスト最後で毎回呼ばれるのでここで明示的にセッションをクローズします．
ここまでやって大体のエラーは消えたものの，たまーにSQLはGo Awayするし

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

がどうやっても消えない．．．

## mysqlserverにpingを送ってプロセスを起こす

[参考][FlaskとSQLAlchemyを使っててMySQL server has gone awayってなる](http://petitviolet.hatenablog.com/entry/20131113/1384329169)

最後にたどり着いたのがこの方法で，コネクションがクローズしてるなら前もって最初にプロセスを立ち上げよう．という方法なんでしょうか．とりあえずこれでエラーは全部解消されました

```py3
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()
```

# おわりに

この問題に言及されているサイトはかなりたくさんあるのですが，今回試してみてうまくいったものを引用させていただきました．今回引用させて頂いたサイトにも色々試行錯誤の過程が載っているので，同じような問題に詰まっている方はそちらも参考にされると良いかと思います．
もっといい方法がある，根本的な原因はそもそもそこじゃない，みたいなことがあるようでしたらお教え頂けると幸いです．

# 参考文献

- Python: SQLAlchemy で 'MySQL server has gone away' になる問題を解決する(<http://blog.amedama.jp/entry/2015/08/15/133322>)
- [Python] SQLAlchemy との戦い(<http://d.hatena.ne.jp/heavenshell/20160220/1455987788>)
- FlaskとSQLAlchemyを使っててMySQL server has gone awayってなる(<http://petitviolet.hatenablog.com/entry/20131113/1384329169>)