Python: SQLAlchemy で 'MySQL server has gone away' になる問題を解決する

Python の有名な O/R マッパであるところの SQLAlchemy を MySQL と一緒に使っていたところ、ちょくちょく 'MySQL server has gone away' というエラーになることに気づいた。今回はこの問題の原因と対策について調べたので書き残しておく。

結論から先に書いておくと上記のエラーは MySQL とのコネクションがタイムアウトを起こした状態で SQLAlchemy が SQL 文を発行したときに発生する。対策については、MySQL とのコネクションがタイムアウトを起こさないように、一定時間毎にコネクションを張り直す設定を SQLAlchemy に行えば良い。

問題を再現させる環境を作る

まずは問題を再現させるための環境作りから始める。今回はプラットフォームに CentOS7 を使うことにする。

$ cat /etc/redhat-release 
CentOS Linux release 7.1.1503 (Core)
$ uname -r
3.10.0-229.el7.x86_64

MySQL (MariaDB) をインストールする

まずは MySQL のインストールから。ただし、CentOS7 では MySQL の代わりに MySQL フォークの MariaDB を使うことになる。

$ sudo yum -y install mariadb-server

問題を再現させやすくするための設定を MariaDB の設定ファイルに仕込む。具体的には 'wait_timeout' という値を 10 (sec) に設定しておく。

$ sudo sed -i.back -e "/^\[mariadb\]$/a\wait_timeout = 10" /etc/my.cnf.d/server.cnf

設定ができたら MariaDB を起動する。

$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

念のため上記の設定が反映されていることを確認しておこう。

$ mysql -u root -e "show global variables like 'wait_timeout';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 10 |
+---------------+-------+

動作確認に使うデータベースを用意しておく。

$ mysql -u root -e "create database sample;"
$ mysql -u root -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sample |
| test |
+--------------------+

SQLAlchemy w/ MySQL ドライバをインストールする

SQLAlchemy をインストールするために、まず Python のパッケージ管理システム 'pip' をインストールする。

$ wget -O - https://bootstrap.pypa.io/get-pip.py | sudo python
$ pip --version
pip 7.1.0 from /usr/lib/python2.7/site-packages (python 2.7)

SQLAlchemy をインストールする。

$ sudo pip install sqlalchemy
$ pip list | grep -i sqlalchemy
SQLAlchemy (1.0.8)

SQLAlchemy はドライバを入れ替えることで様々な RDB に対応できる。ここでは Python 3.x にも対応した MySQL ドライバの 'mysqlclient' をインストールする。

$ sudo yum -y install mariadb-devel python-devel
$ sudo pip install mysqlclient
$ pip list | grep -i mysqlclient
mysqlclient (1.3.6)

問題を再現させる

以下のサンプルコードで問題を再現させる。 MySQL (MariaDB) 側ではコネクションが 10 秒でタイムアウトを起こすため、それ以上の時間をコネクションが張りっぱなしになるようにすれば良いはず。そこで、time.sleep() 関数を使うことでコネクションが張られて 15 秒間待ってタイムアウトを起こしてから SQL 文が流し込まれるようにしている。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import time

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import BigInteger
from sqlalchemy.types import Text
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(BigInteger, primary_key=True)
    name = Column(Text, nullable=False)


def main():
    engine = create_engine('mysql+mysqldb://root@localhost/sample',)
    Base.metadata.create_all(engine)
    SessionMaker = sessionmaker(bind=engine)
    session = SessionMaker()

    new_user = User(name='foo')

    session.add(new_user)

    # SQLAlchemy が MariaDB にコネクションを張ってから実際に SQL が発行されるまでに 15 秒間待つ
    # MariaDB の wait_timeout 値が 10 (sec) なので、その間にコネクションがタイムアウトする
    time.sleep(15)

    # ここではタイムアウトしたコネクションに対して SQL 文を流し込むことになる
    session.commit()


if __name__ == '__main__':
    main()

上記を適当な名前で保存したら実行してみよう。以下のような例外になるはずだ。

$ python goneaway.py
...(省略)...
File "/usr/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
raise errorvalue
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: u'INSERT INTO users (name) VALUES (%s)'] [parameters: ('bar',)]

対策

次は上記の例外にならないようにするための対策について。これには 'sqlalchemy.engine.create_engine()' に 'pool_recycle' の設定を入れれば良い。この項目は SQLAlchemy が内部的に持っているコネクションを一定時間毎に張り直すか否かの設定だ。デフォルトでは -1 で張り直すことをしない状態になっている。ここに整数値を入れることで、その秒数毎に自動でコネクションを張り直すようになる。つまり、ここの値を MySQL (MariaDB) 側で設定した wait_timeout よりも小さい値にしておけばタイムアウトは起きなくなる。ここでは、先ほど設定した wait_timeout の 10 秒よりも小さい 5 秒に設定しておこう。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import time

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import Column
from sqlalchemy.types import BigInteger
from sqlalchemy.types import Text
from sqlalchemy.engine import create_engine
from sqlalchemy.orm.session import sessionmaker

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'

    id = Column(BigInteger, primary_key=True)
    name = Column(Text, nullable=False)


def main():
    engine = create_engine(
        'mysql+mysqldb://root@localhost/sample',
        # この設定を入れることでコネクションを一定時間毎に再接続する
        # ここでは 5 sec 毎にコネクションを貼り直すことになる
        # MariaDB 側の wait_timeout 値よりも短いことから、
        # タイムアウトするする前にコネクションが張り直されることになる
        pool_recycle=5,
    )
    Base.metadata.create_all(engine)
    SessionMaker = sessionmaker(bind=engine)
    session = SessionMaker()

    new_user = User(name='foo')

    session.add(new_user)

    # # 15 秒間待っている間にもバックグラウンドで SQLAlchemy はコネクションを一定時間毎に張り直す
    time.sleep(15)

    # ここではタイムアウトしていないコネクションを使って SQL 文が流し込まれる
    session.commit()


if __name__ == '__main__':
    main()

上記のサンプルコードも先ほどと同様に適当な名前で保存したら実行する。今度は例外にならないはずだ。

$ python recycle.py

念のためデータベースの内容を確認しても、ちゃんとレコードが追加されている。

$ mysql -u root -D sample -e "select * from users"
+----+------+
| id | name |
+----+------+
| 1 | foo |
+----+------+

まとめ

今回は SQLAlchemy を MySQL (MariaDB) と組み合わせて使う場合に発生する 'MySQL server has gone away' のエラーの原因と対策について扱った。

この問題は気づくのがテストの後ろの方になったり、下手をすると本番で稼働してからということにもなりかねないので注意が必要だ。何故なら MySQL (MariaDB) の wait_timeout のデフォルト値は 28800 (sec) = 8 (hour) になっているので、この問題が露見するのはアプリケーションが稼働してから 8 時間後ということになる。

この問題を起こさないために、必ず SQLAlchemy を使う際には 'pool_recycle' の値を設定しておく必要がある。本番の環境で使う wait_timeout の値を確認した上で、それよりも小さい値を入れるようにしよう。