您好,登錄后才能下訂單哦!
這篇文章主要為大家展示了mysql服務(wù)器在無操作超時主動斷開連接怎么辦,內(nèi)容簡而易懂,希望大家可以學(xué)習(xí)一下,學(xué)習(xí)完之后肯定會有收獲的,下面讓小編帶大家一起來看看吧。
我們在使用mysql服務(wù)的時候,正常情況下,mysql的設(shè)置的timeout是8個小時(28800秒),也就是說,如果一個連接8個小時都沒有操作,那么mysql會主動的斷開連接,當(dāng)這個連接再次嘗試查詢的時候就會報個”MySQL server has gone away”的誤,但是有時候,由于mysql服務(wù)器那邊做了一些設(shè)置,很多情況下會縮短這個連接timeout時長以保證更多的連接可用。有時候設(shè)置得比較變態(tài),很短,30秒,這樣就需要客戶端這邊做一些操作來保證不要讓mysql主動來斷開。
查看mysql的timeout
使用客戶端工具或者M(jìn)ysql命令行工具輸入show global variables like '%timeout%';就會顯示與timeout相關(guān)的屬性,這里我用docker模擬了一個測試環(huán)境。
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:服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù),就是你在你的項目中進(jìn)行程序調(diào)用
interactive_timeout: 服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù),就是你在你的本機上打開mysql的客戶端,cmd的那種
使用pymysql進(jìn)行查詢
我在數(shù)據(jù)庫里隨便創(chuàng)建了一個表,插入兩條數(shù)據(jù)
mysql> select * from person; +----+------+-----+ | id | name | age | +----+------+-----+ | 1 | yang | 18 | | 2 | fan | 16 | +----+------+-----+ 2 rows in set
我使用pymysql這個庫對其進(jìn)行查詢操作,很簡單
#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()
可以正確的得到結(jié)果
(1, 'yang', 18)
(2, 'fan', 16)
連接超時以后的查詢
上面可以正常得到結(jié)果是由于當(dāng)創(chuàng)建好一個鏈接以后,就立刻進(jìn)行了查詢,此時還沒有超過它的超時時間,如果我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()
這里進(jìn)行了兩次查詢,因為我把mysql的wait_timeout設(shè)置了30秒,所以我在第一次查詢之后停了31秒,目的讓mysql服務(wù)主動的和我剛才創(chuàng)建的連接斷開,得到的結(jié)果是
(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秒鐘以后,再次使用該連接進(jìn)行查詢將拋出2013, 'Lost connection to MySQL server during query'錯誤。
解決辦法
解決的方法有兩種,既然這里的超時是由于在規(guī)定時間內(nèi)沒有任何操作導(dǎo)致mysql主動的將鏈接關(guān)閉,pymysql的connection對象有一個ping()方法,可以檢查連接是否有效,在每次執(zhí)行查詢操作之前先執(zhí)行一下ping()方法,該方法默認(rèn)的有個reconnect參數(shù),默認(rèn)是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()
我曾嘗試使用另外一個線程不停來執(zhí)行ping()操作,但是當(dāng)我這樣做以后連接就會丟失,之后的操作就不能進(jìn)行了。這個問題我再研究研究。
#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()
還有一種方法是使用連接池,連接池中保持著指定數(shù)量的可用連接,每次重新獲取一個有效的連接進(jìn)行查詢操作,pymysql本身不具有連接池功能,需要借住DBUtils
#coding:utf-8 import pymysql import time from DBUtils.PooledDB import PooledDB, SharedDBConnection def mytest(): pool = PooledDB( creator=pymysql, # 初始化時,連接池至少創(chuàng)建的空閑連接,0表示不創(chuàng)建 maxconnections=3, # 連接池中空閑的最多連接數(shù),0和None表示沒有限制 mincached=2, # 連接池中最多共享的連接數(shù)量,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()
這種方式雖然可以正確的獲取結(jié)果,但是實際的項目中并不會這么使用,而是在執(zhí)行完查詢語句以后要將connection關(guān)閉,注意這里的關(guān)閉并不是真正的關(guān)閉,而只是將連接返回給連接池讓其它人使用.
#coding:utf-8 import pymysql import time from DBUtils.PooledDB import PooledDB, SharedDBConnection def mytest(): pool = PooledDB( creator=pymysql, maxconnections=3, # 初始化時,連接池至少創(chuàng)建的空閑連接,0表示不創(chuàng)建 mincached=2, # 連接池中空閑的最多連接數(shù),0和None表示沒有限制 maxcached=5, # 連接池中最多共享的連接數(shù)量,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() # 關(guān)閉連接,其實并沒有真正關(guān)閉,只是將連接返回給連接池 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()
以上就是關(guān)于mysql服務(wù)器在無操作超時主動斷開連接怎么辦的內(nèi)容,如果你們有學(xué)習(xí)到知識或者技能,可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。