解決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-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()

這里進行了兩次查詢，因為我把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: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-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()

我曾嘗試使用另外一個線程不停來執行ping()操作，但是當我這樣做以后連接就會丟失，之后的操作就不能進行了。這個問題我再研究研究。

#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()

還有一種方法是使用連接池，連接池中保持著指定數量的可用連接，每次重新獲取一個有效的連接進行查詢操作,pymysql本身不具有連接池功能，需要借住DBUtils

#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()

這種方式雖然可以正確的獲取結果，但是實際的項目中并不會這么使用，而是在執行完查詢語句以后要將connection關閉，注意這里的關閉并不是真正的關閉，而只是將連接返回給連接池讓其它人使用.

#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()

以上這篇解決mysql服務器在無操作超時主動斷開連接的情況就是小編分享給大家的全部內容了，希望能給大家一個參考，也希望大家多多支持好吧啦網。