我们在使用mysql服务的时候,正常情况下,mysql的设置的timeout是8个小时(28800秒),也就是说,如果一个连接8个小时都没有操作,那么mysql会主动的断开连接,当这个连接再次尝试查询的时候就会报个”MySQL server has gone away”的误,但是有时候,由于mysql服务器那边做了一些设置,很多情况下会缩短这个连接timeout时长以保证更多的连接可用。有时候设置得比较变态,很短,30秒,这样就需要客户端这边做一些操作来保证不要让mysql主动来断开。

查看mysql的timeout

使用客户端工具或者Mysql命令行工具输入show global variables like '%timeout%';就会显示与timeout相关的属性,这里我用docker模拟了一个测试环境。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show variables like '%timeout%'; 
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 30 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 30 |
+-----------------------------+----------+
13 rows in set

wait_timeout:服务器关闭非交互连接之前等待活动的秒数,就是你在你的项目中进行程序调用
interactive_timeout: 服务器关闭交互式连接前等待活动的秒数,就是你在你的本机上打开mysql的客户端,cmd的那种

使用pymysql进行查询

我在数据库里随便创建了一个表,插入两条数据

1
2
3
4
5
6
7
8
mysql> select * from person;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yang | 18 |
| 2 | fan | 16 |
+----+------+-----+
2 rows in set

我使用pymysql这个库对其进行查询操作,很简单

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#coding:utf-8
import pymysql

def mytest():
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8')

cursor = connection.cursor()
cursor.execute("select * from person")
data = cursor.fetchall()
cursor.close()
for i in data:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()

可以正确的得到结果

1
2
(1, 'yang', 18)
(2, 'fan', 16)

连接超时以后的查询

上面可以正常得到结果是由于当创建好一个链接以后,就立刻进行了查询,此时还没有超过它的超时时间,如果我sleep一段时间,看看什么效果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#coding:utf-8

import pymysql
import time


def mytest():
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8')
cursor = connection.cursor()
cursor.execute("select * from person")
data = cursor.fetchall()
for i in data:
print(i)
cursor.close()

time.sleep(31)
cursor = connection.cursor()
cursor.execute("select * from person")
data2 = cursor.fetchall()
for i in data2:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()

这里进行了两次查询,因为我把mysql的wait_timeout设置了30秒,所以我在第一次查询之后停了31秒,目的让mysql服务主动的和我刚才创建的连接断开,得到的结果是

1
2
3
4
5
6
7
8
9
10
11
12
13
14
(1, 'yang', 18)
(2, 'fan', 16)
Traceback (most recent call last):
File "F:/python/python3Test/mysqltest.py", line 29, in <module>
mytest()
File "F:/python/python3Test/mysqltest.py", line 22, in mytest
cursor.execute("select * from person")
...
...
File "C:\Python35\lib\site-packages\pymysql\connections.py", line 702, in _read_bytes
CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

Process finished with exit code 1

可以看到在停了31秒钟以后,再次使用该连接进行查询将抛出2013, 'Lost connection to MySQL server during query'错误。

解决办法

解决的方法有两种,既然这里的超时是由于在规定时间内没有任何操作导致mysql主动的将链接关闭,pymysql的connection对象有一个ping()方法,可以检查连接是否有效,在每次执行查询操作之前先执行一下ping()方法,该方法默认的有个reconnect参数,默认是True,如果失去连接了会重连。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#coding:utf-8

import pymysql
import time


def mytest():
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8')
connection.ping()
cursor = connection.cursor()
cursor.execute("select * from person")
data = cursor.fetchall()
for i in data:
print(i)
cursor.close()

time.sleep(31)
connection.ping()
cursor = connection.cursor()
cursor.execute("select * from person")
data2 = cursor.fetchall()
for i in data2:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()

我曾尝试使用另外一个线程不停来执行ping()操作,但是当我这样做以后连接就会丢失,之后的操作就不能进行了。这个问题我再研究研究。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
#coding:utf-8

import pymysql
import time
import threading
import traceback

def ping(conn):
while True:
try:
conn.ping()
except:
print(traceback.format_exc())
finally:
time.sleep(1)

def mytest():
connection = pymysql.connect(
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8')
cursor = connection.cursor()
# 放在此处不行,必须等待cursor的一个execute之后再运行才可以
# th = threading.Thread(target=ping, args=(connection,))
# th.setDaemon(True)
# th.start()
cursor.execute("select * from person")
data = cursor.fetchall()
for i in data:
print(i)
cursor.close()

# 线程放在此处启动可以
th = threading.Thread(target=ping, args=(connection,))
th.setDaemon(True)
th.start()

time.sleep(31)
cursor = connection.cursor()
cursor.execute("select * from person")
data2 = cursor.fetchall()
for i in data2:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()

还有一种方法是使用连接池,连接池中保持着指定数量的可用连接,每次重新获取一个有效的连接进行查询操作,pymysql本身不具有连接池功能,需要借住DBUtils

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#coding:utf-8
import pymysql
import time
from DBUtils.PooledDB import PooledDB, SharedDBConnection


def mytest():
pool = PooledDB(
creator=pymysql,
# 初始化时,连接池至少创建的空闲连接,0表示不创建
maxconnections=3,
# 连接池中空闲的最多连接数,0和None表示没有限制
mincached=2,
# 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
maxcached=5,
maxshared=3,
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8'
)
connection = pool.connection()
cursor = connection.cursor()
cursor.execute("select * from person")
data = cursor.fetchall()
for i in data:
print(i)

time.sleep(40)
cursor.execute("select * from person")
data2 = cursor.fetchall()
for i in data2:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()

这种方式虽然可以正确的获取结果,但是实际的项目中并不会这么使用,而是在执行完查询语句以后要将connection关闭,注意这里的关闭并不是真正的关闭,而只是将连接返回给连接池让其它人使用.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
#coding:utf-8
import pymysql
import time
from DBUtils.PooledDB import PooledDB, SharedDBConnection


def mytest():
pool = PooledDB(
creator=pymysql,
maxconnections=3,
# 初始化时,连接池至少创建的空闲连接,0表示不创建
mincached=2,
# 连接池中空闲的最多连接数,0和None表示没有限制
maxcached=5,
# 连接池中最多共享的连接数量,0和None表示全部共享(其实没什么卵用)
maxshared=3,
host='localhost',
port=3306,
user='root',
password='123456',
db='mytest',
charset='utf8'
)
connection = pool.connection()
cursor = connection.cursor()
cursor.execute("select * from person")
data = cursor.fetchall()
for i in data:
print(i)
cursor.close()
# 关闭连接,其实并没有真正关闭,只是将连接返回给连接池
connection.close()

time.sleep(40)
connection = pool.connection()
cursor = connection.cursor()
cursor.execute("select * from person")
data2 = cursor.fetchall()
for i in data2:
print(i)
cursor.close()
connection.close()

if __name__ == '__main__':
mytest()