Python 长期连接数据库的最不佳实践：Lost connection to MySQL server during query

问题来源

前几天用 Tornado 做 Web 服务器起了一个简单的以表格显示数据库中内容的网页，然后把它放到 lab 服务器上做 demo。卧病几天之后打开邮箱，发现 mentor 邮件说表格中不显示数据。赶紧登上服务器 screen 回去看下，发现抛了个这么异常：Lost connection to MySQL server during query

当然也说不定会是 MySQL server has gone away

Python：我这么爱你，你为什么要离开我？

MySQL：你丫八个小时都不跟俺说一句话，想起来了就冷不丁的冒一句，然后就消失，就这样还是俺真爱？哼！

Python：……（好吧，被你发现了，我就是玩玩而已你还认真了，真是的?）

以上内容纯属虚构，请勿对号入座（咋想也没办法对号入座啊）

问题原因

造成这个异常的原因其实非常简单，就是基本上没啥人访问我的 demo link，导致连接对象超过了 wait_timeout 的默认 28800 秒（即 8 小时），然后 MySQL 就关闭了这个连接。由于 TCP 的特性，Python 的驱动并不会知道被甩了，当有人访问 demo link 也就是进行一次查询的时候，就会触发异常，于是表格自然就是空白的啦。

为了更进一步的说明这个问题并提供若干最不佳实践，咱先简单的说说这段代码……GitHub 可以戳下面的触手猫（commit 为 959ce78f854894397a75bad0efef4ef0de06b7ca）
触手猫十周年啦

开头

首先在开头引入几个库，然后新建两个全局的对象：一个是用于数据库连接的 con，一个是查询游标 cur

con = mysql.connector.connect(host='127.0.0.1', user='root', password='root', database='san')
cur = con.cursor()

路由

在 make_app 函数中我们可以看到定义了俩路由，/ 对应 Index 类，/list / 对应 Retrive 类。

Retrive 类重载了 get 方法，返回的是一个函数 get_data，而 get_data 则是进行数据库相关操作的函数，代码如下：

def get_data():
    cur.execute('SHOW COLUMNS from switch_device')
    col_data = cur.fetchall()
    col_field = [i[0] for i in col_data]
    cur.execute('SELECT * FROM switch_device')
    data = cur.fetchall()
    
    bulk_dic = []
    for i in range(len(data)):
        es_dic = dict(zip(col_field, data[i]))
        bulk_dic.append(es_dic)
    return json.dumps(bulk_dic)

我不由得想起了我最爱的列表推导…… 反正就是查询了两次，根据列名和数据把表中的内容组成了一个 json。可惜啊我不知道 DictCursor

入口

入口非常简单，就是启动 tornado 而已。

总结

通篇可以看到有这么几个特点：

1. cur 和 con 是全局的，只要 Python 脚本在运行，那么这俩对象的 id（内存地址）就不会改变，无论多少次的页面刷新（数据库查询操作）都是由 cur 这个全局对象执行的
2. 并没有对 connection 进行 close。脚本在收到相应的信号量时会执行对应的行为，比如 SIGINT 也即 Ctrl+C，自然就退出了，连接自然也就被释放了。

再一次说明问题原因

再一次总结出错原因：demo 太冷门没人看，连接闲置时间超过 wait_timeout，MySQL 关闭连接，然后再次触发查询的时候，自然而然就会报错了。

针对这个问题，我想了几种个人觉得不咋样的解决方法，所以被称之为 “最不佳实践”。当然提前说一个比较好的办法，那就是用数据库框架啊，类似 SQLAlchemy 什么的，这类框架应该会处理这类问题的；还有就是，如果对应的驱动程序提供了类似 automatic reconnection 这种特性，那也好办多啦

注意：为了方便叙述，下面的 get_data() 就是指代数据库操作啦！

方法 1：增加 wait_timeout 时间

既然数据库小姐默认的 “生气时间” 是 28800 秒，那咱简单的给它调大点不就好了。

Python：亲爱的，你以后要多点耐心，小不忍则乱大谋啊~
MySQL：好吧好吧，那我就多点耐心。
一年之后……
Python：亲爱的，干嘛呢？
MySQL：老娘不认识你，丨。
Python 吸取教训，不能冷落小姐姐太久，于是再次通过自己的阴谋诡计取得小姐姐的好感与信任。
Python：亲爱的，你以后要多点耐心，小不忍则乱大谋啊~
MySQL：好吧好吧，那我就多点耐心。
十几天之后……
Python：亲爱的，干嘛呢？
MySQL：忙着呢，没空理你。
Python：…………（算了，干嘛一棵树上吊死）

简单的在得到游标之后来这么一句：

cur.execute('SET SESSION WAIT_TIMEOUT = 2147483')

注：

2147483（约 24 天）是 Windows 平台的 MySQL 的 WAIT_TIMEOUT 最大值，其他平台为 31536000（一年）

如果有必要的话咱还可以SET GLOBAL WAIT_TIMEOUT = 2147483，不过你真的确定要 global 么，说不好 DoS 了，查完日志就该回家种田了啊。

但实际上这是一种想当然一拍脑门一跺脚的完全不现实的不经过仔细思考的解决方案。为啥捏，待我慢慢道来。

治标不治本

不，这个三级标题是错误的。这种解决方案不仅不治标，更不治本！甚至有损于数据库的性能。暂且不考虑平台兼容性（设置为 31536000 对 Windows 平台就是无效值了啊），如果真的就是连接限制了超过 timeout 的时间，然后突然有了一次访问，那还不是照样挂？

影响数据库性能

MySQL 为什么要有这样一个参数？很简单啊，为了提高服务器性能啊。每维持一个连接都是需要耗费一定的内存和 CPU 资源的，关闭长期不使用的连接自然节约了资源，同时也避免了 max_connections 达到上限造成了 DoS 的结果。

即使我们把 max_connections 的值调到很高尽可能避免 DoS，这对于服务器的性能也是不利的，需要更多内存，更多文件描述符。这同样也是一个既不治标也不治本的解决 “解决方案” 的方案。堆硬件？有那钱还不如打赏辛苦写作本文的俺呐！

注：max_connections 默认值为 151，上限为 100000，同时还要受到文件描述符（用 ulimit 查看）的限制。

一句话总结

放弃吧，别用这种不太好的办法了。

方法 2：每次刷新页面都新建连接 - 查询 - 关闭连接

既然问题在于连接限制，那咱就每次数据库相关的操作都新建连接 - 查询 - 关闭连接。代码也很简单，把全局的数据库连接移动到函数内：

con = mysql.connector.connect(host='127.0.0.1', user='root', password='root', database='san')
cur = con.cursor()
# 执行操作
con.close()
return some_value

这确实是一个非常简单的处理方式，很强，没有 bug，绝对管用，不仅治标也治本。只是有一个问题，新建连接会慢点，在某些场景可能会稍差一点点

新建一次连接自然是比较费时、费资源的啦，说不定要 0.1 秒呢，那如果同时访问的人多了造成大并发的局面（tornado 是多线程非阻塞的服务器，所以短时间内可能会创建很多个 con 对象），那可能速度就要稍微慢一点了。

不过这种方法确实好用，因为抓住了问题的本质：闲置连接被关闭。绝大部分情况下用这个方案就可以啦

方法 3：数据库的操作之前 con.reconnect()

从方法 3 中我们发现了con.reconnect()这个神奇的小东西，那么我们在数据库操作之前执行这个，这样每次都会重新连接，那就可以啦。

con.reconnect()
get_data()

这个方法大概相当于方法 2 的改进版，每次操作没有重新新建 con 对象而是无论连接是否被 MySQL 关闭，都重新连接。没毛病，问题完美解决，只是也有一点点性能损失：

连接关闭了 reconnect 倒好，但是没关闭却 reconnect 了那不就浪费了嘛？

虽说如此，但是这个方法的性能会比方法 2 稍好一些。

方法 4：数据库操作时 try...except，异常重连

针对方法 4，很容易就可以进行一下改进，那咱先毫无顾虑的执行，出问题了捕获异常就好了。

try:
    get_data()
except mysql.connector.errors.OperationalError:
    con.reconnect()
    get_data()

两次get_data()有点不雅啊，唉灵机一动：

try:
    cur.execute('select version()')
except mysql.connector.errors.OperationalError:
    con.reconnect()
finally:
    get_data()

或者不用 finally 直接开始写新的语句块，也是可以的！

随便执行个查询操作来看看连接有没有被关闭，然后 finally 无论怎么都会被执行的，好办法好办法，拍案叫绝！

只是也稍微有那么一丁点不好，白白的让数据库执行了一个没有用的查询，多多少少还是浪费了一丢丢资源啦。

于是我又灵机一动，诞生了方法 5.

方法 5：数据库操作之前进行 con.ping()，异常重连

用一次 con.ping() 检测连接是否还在，不在的话就重新连接，finally 里执行查询，哇 (o ゜▽゜)o☆

try:
    con.ping()
except mysql.connector.errors.InterfaceError:
    con.reconnect()
finally:
    get_data()

同上，不用 finally 直接开始写新的语句块，也是可以的！con.ping()的性能开销应该比较理想的。

需要注意的是，不同的库可能有一点不同，比如 pymysql 的con.ping()包含一个 reconnect 的参数，默认为 True 的时候就会自动重连。所以如果你使用 pymysql，直接在数据库操作之前使用con.ping()就可以了。

另外一个需要注意的点，多线程情况下使用全局的 con 可能会触发线程安全相关的问题。以及如果涉及事务，要注意提交、回滚的操作，免得发生幻读之类的状况

方法 7：连接池

等有空再说吧~

后记

需要注意一个问题，如果数据库开启了事务，你的应用程序涉及到了多线程等并发问题，那么在全局使用一个 connection 时一定要注意线程安全和事务相关的问题。一旦代码写的不完善，就很容易出现类似幻读、脏读这种事务相关的问题。