解决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

interactive_timeout: 服务器关闭交互式连接前等待活动的秒数,就是你在你的本机上打开mysql的客户端,cmd的那种

使用pymysql进行查询

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

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

#coding:utf-8import pymysqldef 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-8import pymysqlimport timedef 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()

(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

解决办法

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

#coding:utf-8import pymysqlimport timedef 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()

#coding:utf-8import pymysqlimport timeimport threadingimport tracebackdef 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()

#coding:utf-8import pymysqlimport timefrom DBUtils.PooledDB import PooledDB, SharedDBConnectiondef 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()

#coding:utf-8import pymysqlimport timefrom DBUtils.PooledDB import PooledDB, SharedDBConnectiondef 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()

更多相关文章

随机推荐