現(xiàn)象
用戶連接到mysql，如果執(zhí)行一些sql后，閑置一段時(shí)間（就是不執(zhí)行任何sql），那么連接就會被mysql kill掉，當(dāng)用戶再次執(zhí)行sql的時(shí)候就會報(bào)失去連接的報(bào)錯(cuò)。如ERROR 2006 (HY000): MySQL server has gone away，或者Lost connection to MySQL server during query等
控制參數(shù)
這里涉及的參數(shù)有兩個(gè)，wait_timeout和interactive_timeout。而mysql的參數(shù)又分為global級別和session級別，所以嚴(yán)格的講，會涉及到四個(gè)參數(shù)。
mysql的連接分為兩種，一種是像mysql客戶端那種交互式的連接，一種是像pymysql，jdbc等等程序創(chuàng)建的連接。這兩種連接的閑置時(shí)間都是由session級別的wait_timeout控制的。
在連接初始化的時(shí)候，session級別的wait_timeout會根據(jù)連接的種類來初始化。如果是交互式連接，那么會和global級別的interactive_timeout值一樣，如果是jdbc等程序連接，那么會和global級別的wait_timeout一樣，interactive_timeout和wait_timeout默認(rèn)都是8個(gè)小時(shí)。這里session級別的interactive_timeout是沒有用處的。
驗(yàn)證
閑置時(shí)間只由session級別的wait_timeout控制。
1.交互式連接測試
root@localhost [(none)]>SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout;
+------------------------------+-----------------------+-------------------------------+------------------------+
| @@GLOBAL.interactive_timeout | @@GLOBAL.wait_timeout | @@session.interactive_timeout | @@session.wait_timeout |
+------------------------------+-----------------------+-------------------------------+------------------------+
| 28800 | 28800 | 28800 | 28800 |
+------------------------------+-----------------------+-------------------------------+------------------------+
1 row in set (0.01 sec)
root@localhost [(none)]>set session wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
##暫停11s再進(jìn)行下面操作
root@localhost [(none)]>SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 587156
Current database: *** NONE ***
+------------------------------+-----------------------+-------------------------------+------------------------+
| @@GLOBAL.interactive_timeout | @@GLOBAL.wait_timeout | @@session.interactive_timeout | @@session.wait_timeout |
+------------------------------+-----------------------+-------------------------------+------------------------+
| 28800 | 28800 | 28800 | 28800 |
+------------------------------+-----------------------+-------------------------------+------------------------+
1 row in set (0.00 sec)
root@localhost [(none)]>
然后用同樣的方式去測試剩下的三個(gè)參數(shù)，發(fā)現(xiàn)只有修改session級別的wait_timeout才會有ERROR 2006 (HY000): MySQL server has gone away錯(cuò)誤（請務(wù)必在同一個(gè)會話當(dāng)中測試）。
- pymysql連接測試(python3版本)
import pymysql
import time
con = pymysql.connect('localhost','wxp','123456')
cursor = con.cursor()
cursor.execute("SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout")
results = cursor.fetchall()
print(results)
cursor.execute('set session wait_timeout = 10 ')#把該參數(shù)換成其他三個(gè)進(jìn)行測試
cursor.fetchall()
time.sleep(11)
cursor.execute("SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout")
results = cursor.fetchall()
print(results)
con.close()
嘗試著用4個(gè)不同的參數(shù)來運(yùn)行上述代碼，只有修改session級別wait_timeout才會出現(xiàn)如下錯(cuò)誤
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')
在連接初始化的時(shí)候，session級別的wait_timeout會根據(jù)連接方式的不同，從global級別的interactive_timeout或者wait_timeout繼承。
先分別設(shè)置global級別的interactive_timeout和wait_timeout。
root@localhost [(none)]>set global interactive_timeout=20;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>set global wait_timeout=40;
Query OK, 0 rows affected (0.00 sec)
root@localhost [(none)]>SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout;
+------------------------------+-----------------------+
| @@GLOBAL.interactive_timeout | @@GLOBAL.wait_timeout |
+------------------------------+-----------------------+
| 20 | 40 |
+------------------------------+-----------------------+
1 row in set (0.00 sec)
1.重新用mysql客戶端開一個(gè)窗口，查看session參數(shù)
root@localhost [(none)]>SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout;
+------------------------------+-----------------------+-------------------------------+------------------------+
| @@GLOBAL.interactive_timeout | @@GLOBAL.wait_timeout | @@session.interactive_timeout | @@session.wait_timeout |
+------------------------------+-----------------------+-------------------------------+------------------------+
| 20 | 40 | 20 | 20 |
+------------------------------+-----------------------+-------------------------------+------------------------+
1 row in set (0.00 sec)
2.用pymysql查看參數(shù)值（python3版本）
import pymysql
con = pymysql.connect('192.168.116.132','wxp','123456',cursorclass=pymysql.cursors.DictCursor)
cursor = con.cursor()
cursor.execute("SELECT @@GLOBAL.interactive_timeout,@@GLOBAL.wait_timeout,@@session.interactive_timeout,@@session.wait_timeout")
results = cursor.fetchall()
print(results)
con.close()
結(jié)果為
{'@@GLOBAL.interactive_timeout': 20, '@@GLOBAL.wait_timeout': 40, '@@session.interactive_timeout': 20, '@@session.wait_timeout': 40}
