解决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模拟了一个测试环境。

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进行查询

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

mysql> select * from person;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | yang | 18 |
| 2 | fan | 16 |
+----+------+-----+
2 rows in set

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

#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, 'yang', 18)

(2, 'fan', 16)

连接超时以后的查询

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

#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, '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，如果失去连接了会重连。

#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()操作，但是当我这样做以后连接就会丢失，之后的操作就不能进行了。这个问题我再研究研究。

#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

#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关闭，注意这里的关闭并不是真正的关闭，而只是将连接返回给连接池让其它人使用.

#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服务器在无操作超时主动断开连接的情况就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持猪先飞。