mysql服务器在无操作超时主动断开连接情况解决方法

本篇文章小编给大家分享一下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 
  mytest()
 File "F:/python/python3Test/mysqltest.py", line 22, in mytest
  cursor.execute("select * from person")
 ...
 ...
 File "C:Python35libsite-packagespymysqlconnections.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()