使用SQLAlchemy时遇到”#2006: MySQL server has gone away”

使用Python+MySQL开发的童鞋,无论你用了什么web框架,只要你用了SQLAlchemy这个ORM框架(实际上这是最好的选择),早晚都会遇到一个问题,那就是”#2006: MySQL server has gone away”:

MySQL_server_has_gone_away

很不幸地,很自然地,本人也遇到了,下面听我慢慢道来。先说一下现象,再看一下原因,最后看下解决办法。不喜欢啰嗦的请坐电梯直接到解决方案。

Part 1: 现象

最直接的现象嘛,自然就是出现了 “#2006: MySQL server has gone away”的错误:

  ......
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 690, in _commit_impl
    self._handle_dbapi_exception(e, None, None, None, None)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 688, in _commit_impl
    self.engine.dialect.do_commit(self.connection)
  File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/dialects/mysql/base.py", line 2507, in do_commit
    dbapi_connection.commit()
OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away')

但是呢,当你检查MySQL Server,你会发现你的MySQL Server一直在运行,从来都没有‘gone away’。比如说,你的MySQL console一直开着,又比如说,系统日志里是肯定查不到MySQL Server重启的证据的。

还有就是,这个错误会经常的出现,并不是只出现一两次。如果仅仅出现一两次,相信大多数开发人员会“忘记”这茬的。

既然MySQL并没有gone away,那真正的含义是什么呢?其实这个异常是在告诉我们,数据库连接无效。就是说,客户端程序(也就是SQLAlchemy啦)在访问MySQL时,使用了一个已经不可用的数据库连接。这里的“不可用”的意思是说,服务器已经关闭了这个连接,而客户端依然保存着该链接。也就是说,该连接依然在SQLAlchemy的连接池里。那么,当应用程序试图使用该连接请求数据库时,就会发生”#2006: MySQL server has gone away”.

Part 2: 原因

那么,问题来了,为什么SQLAlchemy会试图使用一个已经关闭的连接呢?

首先,请看这里, wait_timeout是MySQL的一个系统参数,简而言之,MySQL会在wait_timeout时间之后关闭连接,无论这个连接正在干什么,空闲or正在使用!而这个wait_timeout的默认值是8小时,你可以在MySQL console查看该值:

mysql>>>SHOW VARIABLES;

举个极端的例子,你正在执行一个非常非常长的查询,时间超过了8小时,那么很不幸,在默认情况下,你得到的结果一定是”#2006: MySQL server has gone away”.

加大这个参数的值当然是一个缓解的办法,但不解决根本问题,因为这里还涉及SQLAlchemy。SQLAlchemy使用session来管理所有到MySQL的访问,而这个session是自动创建,但却需要手动关闭的。如果你没有关闭session,程序就会反复使用它,那么它早晚有一天会超时, 无论数据库的wait_timeout设多大,从而被MySQL主动关闭。所以根本原因其实就一句话,你忘了关闭数据库连接,是不是很无语?当然,不是类似于”connection.close()”这么简单,因为你真正能接触的还是session这个东东,而session的使用因人而异。

比如假如你用了Flask这个python web框架,那么服务器收到每个Http请求时,都会试图从连接池里获取一个session或者创建一个新的session,不论哪种情况,这个session都会在http请求处理完毕之后扔回连接池,之后的个http请求会继续使用这个session。最终,在一段时间(默认8小时)之后,当MySQL断开了这个连接,再次使用同一个session就会报错。

有些包针对这个问题做了优化,比如Flask-SQLAlchemy,这是一个Flask的插件,试图简化Flask+SQLAlchemy的使用, 我承认这是个相当不错的包,至少不会有”#2006: MySQL server has gone away”。但很遗憾,这个包目前的版本对多线程的支持不太好,会有各种并发问题,只好仍痛割爱了。

Part 3: 解决方案

如前文所述,使用SQLAlchemy时出现”#2006: MySQL server has gone away”的原因是数据库切断了连接而SQLAlchemy仍然认为它有效,而根本原因是使用SQLAlchemy的session不当,没有正确的关闭它。那么解决方案其实也就很明确了:正确的关闭session。

方法总结下来有3个:

  1. 设置SQLAlchemy的连接有效期,在MySQL关闭它之前,我先关闭它。
  2. 在Web框架的层面,每次请求处理完毕时,显式地关闭session。
  3. 在使用session之前,先检查其有效性,无效则创建新的session以供使用。

很明显,前两条能解决大部分问题,但第3条才是最终的解决办法,能处理前两条无法涵盖的问题。比如说方法1,它有多线程缺陷,假如连接在线程A创建,那么如果线程B试图使用该连接,即使连接未被关闭,那么一样会报错。又比如说2,如果在你的程序内部,有单独的线程执行类似计划任务一样的程序,那么显然不是web框架能覆盖的范围,因为它根本就没有Http Context。

那是不是只需要3就够了呢?理论上讲是这样,但1和2是3很好的补充,很大程度上加大方法3的效率。因为方法3有个问题,当发现某个连接出错时,SQLAlchemy不仅仅是抛弃当前连接,实际上会抛弃连接池里的所有连接(这么做的原因还未深究,很可能连接只是“虚拟的”,而物理连接只有一个,所以一个出错了就所有的都断了),可想而知,这个是很没有效率的手段。所以我的经验是,这些方法都用上,1和2能高效的解决大部分问题,3则是告别”#2006: MySQL server has gone away”最后的保障。

废话说说太多了,赶紧上代码吧:

1. 正确设置SQLAlchemy的连接有效期, , 跟多内容参考这里

engine = create_engine("mysql://root:root@localhost:3306/db",
  ......
  pool_recycle=3600,
  ......)

pool_recycle=3600的意思是SQLAlchemy会1小时后回收连接,这个时间需要小于MySQL的wait_timeout.

2. 在Web框架的层面,每次请求处理完毕时,显式地关闭session. 根据Web框架的不同,方法也不尽相同,以下是Flask的做法.

关于Http 请求,线程与SQLAlchemy的大致处理流程,可以参考这里

# database/__init__.py
engine = create_engine(......, pool_recycle=3600, ...)
db_session = scoped_session(sessionmaker(autocommit=False,
  autoflush=False,
  bind=engine))
# run.py
from flask import Flask
from database import db_session

# flask
app = Flask(__name__)

@app.teardown_appcontext
def shutdown_session(exception=None):
  db_session.remove()

teardown_appcontext是Flask支持的hook, 允许应用程序在每次Http请求处理完毕之后做一些额外的操作,而且这个hook一定会执行,即使在处理Http 请求期间发生异常.关于Flask的AppContext还可以参考这里

3. 终极保障,使用之前检查connection的有效性,更多内容参考这里.

engine = create_engine(..., pool_recycle=3600,...)

@event.listens_for(engine, "engine_connect")
def ping_connection(connection, branch):
  if branch:
    # "branch" refers to a sub-connection of a connection,
    # we don't want to bother pinging on these.
    return

  try:
    # run a SELECT 1. use a core select() so that
    # the SELECT of a scalar value without a table is
    # appropriately formatted for the backend
    connection.scalar(select([1]))
  except exc.DBAPIError as err:
    # catch SQLAlchemy's DBAPIError, which is a wrapper
    # for the DBAPI's exception. It includes a .connection_invalidated
    # attribute which specifies if this connection is a "disconnect"
    # condition, which is based on inspection of the original exception
    # by the dialect in use.
    if err.connection_invalidated:
      # run the same SELECT again - the connection will re-validate
      # itself and establish a new connection. The disconnect detection
      # here also causes the whole connection pool to be invalidated
      # so that all stale connections are discarded.
      connection.scalar(select([1]))
    else:
      raise

首先执行一个的基本的查询操作,这个操作即使没有任何表也会成功,只要连接正常。反之,如果数据库返回连接异常,就再执行一次查询,而第二次执行过程中会重新创建新连接。

根据自己的实际情况,选择合适的方法吧。

(全文完)

4 thoughts on “使用SQLAlchemy时遇到”#2006: MySQL server has gone away””

Leave a Reply

Your email address will not be published. Required fields are marked *