mysql 会话闲置时间控制

现象

用户连接到mysql，如果执行一些sql后，闲置一段时间（就是不执行任何sql），那么连接就会被mysql kill掉，当用户再次执行sql的时候就会报失去连接的报错。如ERROR 2006 (HY000): MySQL server has gone away，或者Lost connection to MySQL server during query等

控制参数

这里涉及的参数有两个，wait_timeout和interactive_timeout。而mysql的参数又分为global级别和session级别，所以严格的讲，会涉及到四个参数。

mysql的连接分为两种，一种是像mysql客户端那种交互式的连接，一种是像pymysql，jdbc等等程序创建的连接。这两种连接的闲置时间都是由session级别的wait_timeout控制的。

在连接初始化的时候，session级别的wait_timeout会根据连接的种类来初始化。如果是交互式连接，那么会和global级别的interactive_timeout值一样，如果是jdbc等程序连接，那么会和global级别的wait_timeout一样，interactive_timeout和wait_timeout默认都是8个小时。这里session级别的interactive_timeout是没有用处的。

验证

闲置时间只由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再进行下面操作
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)]>

然后用同样的方式去测试剩下的三个参数，发现只有修改session级别的wait_timeout才会有ERROR 2006 (HY000): MySQL server has gone away错误（请务必在同一个会话当中测试）。

2. 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 ')#把该参数换成其他三个进行测试
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个不同的参数来运行上述代码，只有修改session级别wait_timeout才会出现如下错误

pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

在连接初始化的时候，session级别的wait_timeout会根据连接方式的不同，从global级别的interactive_timeout或者wait_timeout继承。

先分别设置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客户端开一个窗口，查看session参数

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查看参数值（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()

结果为
{'@@GLOBAL.interactive_timeout': 20, '@@GLOBAL.wait_timeout': 40, '@@session.interactive_timeout': 20, '@@session.wait_timeout': 40}