sqlalchemy连接池错误：MySQL server has gone away

63 阅读 0 评论 42 点赞

我是靠谱客的博主粗暴翅膀，最近开发中收集的这篇文章主要介绍sqlalchemy连接池错误：MySQL server has gone away，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

由于是初创公司，追求快速开发，后台选用python，采用Tornado+SQLAlchemy，数据库是mysql。python之前没接触过，都是现学的，前一周差不多都在边看边学边写的状态，好在python还是特别简单，有其他语言基础很容易上手，只是经常会犯排版对齐、行末忘记“:”的错误。
项目还没上线，只可能写代码的两人去访问数据库，而且频度很低，居然发生了2次数据库连接错误。经过日志分析，都是SQLAlchemy连接池的配置问题。
错误1：
TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30
错误2：
[ERROR] (OperationalError) (2006, 'MySQL server has gone away')

对于错误1，首先确认数据库的最大连接数是足够的，至少比log里达到的10个连接不应该出错。

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.01 sec)

再查show global status like '%connect%';也是正常的。可以肯定是SQLAlchemy连接池的配置问题。原来的代码：
engine = create_engine('mysql://'+etc.mysql_user+':'+etc.mysql_passwd+'@'+etc.mysql_host+':'+str(etc.mysql_port)+'/'+etc.host_name+'?charset=utf8',encoding="utf-8", echo=False)
没有设置pool_size的大小，默认为5。加上pool_size=100后，此问题不再出现。

另外在初版代码里图省事，写了个循环调用sql的查询语句，正是这个语句的调用直接导致该错误。把循环去掉，改为只调用SQL一次，增加一个方法获取多件，在之后的代码里循环处理业务逻辑；在调用sql的结束地方，增加finally代码块，明确调用session.close()，让连接资源尽快回收到连接池。之后打算加memcached或者redis，减少DB的IO。代码片段如下：

metadata = MetaData()
Session = sessionmaker() #使用sessionmaker()不需要显示调用session.close()
Session.configure(bind=engine)
'''
...省略
'''
def get_img_url_by_imgid_list( p_img_list ) :
    try :
        session = Session()
        items = session.query( ImgBaseItem ).filter( ImgBaseItem.imgid.in_(p_img_list)).all()
        if not items :
            return None
        res = {}
        for item in items :
            res[ item.imgid ] = item.to_url()
        return res
    except Exception as e :
        log.exp(e)
        return None
    finally :
        session.close()

对于错误2，刚开始没在意，因为发生的时机都是在早上来上班后的第一次访问，后面的sql请求都正常了。到第三天，意识到问题不是偶然的，查看mysql配置。

mysql> show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| interactive_timeout         | 28800    |
| wait_timeout                | 28800    |
+-----------------------------+----------+

是默认设置，mysql建立的连接，在8小时内都没有访问请求的话，mysql server将主动断开这条连接，后续在该连接上进行的查询操作都将失败，出现：error 2006 (MySQL server has gone away)，到这里问题就清楚了，SQLAlchemy连接池中的连接资源一直没有释放，第二天来上班时，超过了8小时，SQLAlchemy连接池中获取的失效连接去访问数据库服务器导致出错。
修复后代码：
engine = create_engine('mysql://'+etc.mysql_user+':'+etc.mysql_passwd+'@'+etc.mysql_host+':'+str(etc.mysql_port)+'/'+etc.host_name+'?charset=utf8',encoding="utf-8",pool_size=100, pool_recycle=3600, echo=False)
pool_recycle设置为3600（该值必须小于数据库服务器的interactive_timeout），连接池中的空闲连接超过1小时候，自动释放。
在解决问题的过程中，发现还是官方文档最好用，有空还是多上去看看：http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html
关于sqlalchemy数据库的连接配置参数摘要：