目录

解决mysql服务器在无操作超时主动断开连接的问题

我们在使用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()
  • 文章标题: 解决mysql服务器在无操作超时主动断开连接的问题
  • 本文作者: 杨彦星
  • 本文链接: https://www.yangyanxing.com/article/connect_short_problem.html
  • 版权声明: 本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。非商业转载请注明出处(作者,原文链接),商业转载请联系作者获得授权。