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时一定要注意线程安全和事务相关的问题。一旦代码写的不完善，就很容易出现类似幻读、脏读这种事务相关的问题。