背景描述
在用MySQL Client链接MySQL Server进行操作的是偶,如果一段时间没有操作,可能会遇到以下错误:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
或者
ERROR 2013 (HY000): Lost connection to MySQL server during query
这个报错意味着当前的链接已经断开,需要重新建立链接。 那么这个超时的时长是跟哪些有关呢?
其实,这个超时时间跟interactive_timeout、wait_timeout 的设置有关。
定义
先来看看这两个参数的含义
说明:我这里查的是MySQL 5.7版本的官方文档。https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_wait_timeout
- wait_timeout
- Default Value: 28800
- The number of seconds the server waits for activity on a noninteractive connection before closing it.
从字面上理解的话,就是服务器在非交互链接之前等待活动的秒数。
- interactive_timeout
- Default Value: 28800
- The number of seconds the server waits for activity on an interactive connection before closing it.
从字面上理解,就是服务器在交互链接之前等待活动的秒数。
看起来就是
(1)wait_timeout 针对非交互连接
(2)interactive_timeout 针对交互连接
测试
(1)只修改interactive_timeout
值,观察交互式以及非交互的表现
mysql> set global interactive_timeout=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 28800 | 10 | 28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)
- 交互式
Client开启一个新的链接.
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 10 | 10 | 10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.03 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 10
Current database: *** NONE ***
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 3600 | 10 | 10 | 10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.13 sec)
MySQL [(none)]>
可见,当前会话的interactive_timeout
=10,并且超过10s后,服务器断开了会话连接。
- 非交互式
def main():
num = 0
while True:
max_num = session.query(func.max(JenkinsResponeTime.id)).first()
logger.info(f'max_num:{max_num}')
print(f'num:{num}')
session.commit()
num += 1
time.sleep(15)
这里用一个python demo 去测试。
# python3 01.py
num:0
num:1
num:2
num:3
num:4
num:5
num:6
....
可见, interactive_timeout
并不影响非交互式的应用。
(2)只修改wait_timeout
值,观察交互式以及非交互的表现
MySQL Server端用root@localhost 登录修改
mysql> set global interactive_timeout=30;
Query OK, 0 rows affected (0.00 sec)
mysql> set global wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 10 | 28800 | 30 | 28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)
mysql>
- 交互式
Client端用mysql
命令登录db
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
| 10 | 30 | 30 | 30 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.03 sec)
MySQL [(none)]>
可以看到,@@session.wait_timeout
不是等于global.wait_timeout
, 而是等于@@global.interactive_timeout
。 注意:但是这并不代表非交互的@@session.wait_timeout
等于global.wait_timeout
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:48:34 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.04 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:48:58 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.03 sec)
MySQL [(none)]> SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 34
Current database: *** NONE ***
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout | now() |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
| 10 | 30 | 30 | 30 | 2024-07-25 15:50:39 |
+-----------------------+------------------------+------------------------------+-------------------------------+---------------------+
1 row in set (0.13 sec)
MySQL [(none)]>
并且,经过了interactive_timeout
空闲后,超时断开。
- 非交互
python demo
while True:
try:
sql = "SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();"
result = connect.execute(sql).fetchall()
print(result)
except Exception as e:
print(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') + f' catch exception:{e} ')
time.sleep(15)
输出如下:
# python3 03.py
[(10, 10, 30, 30, datetime.datetime(2024, 7, 25, 16, 14, 13))]
2024-07-25 16:14:28catch exception:(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')
[SQL: SELECT @@global.wait_timeout, @@session.wait_timeout, @@global.interactive_timeout, @@session.interactive_timeout, now();]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
可以看到,非交互中session.wait_timeout
并不等于interactive_timeout
。
非交互式经过session.wait_timeout
后超时。
总结
(1)交互式: 关注interactive_timeout参数。
(2)非交互式: 关注 wait_timeout参数。
延伸
我们知道Linux上有关于TCP KEEPALIVE 的一些参数, 比如tcp_keepalive_time
。
tcp_keepalive_time
默认值720,(2小时),就是空闲多久,开始发keepalive探测包。 那么这个是否可以会影响mysql上的超时时间呢?
这里用非交互式的做讨论。
比如wait_timeout=20 ,Client A应用 每隔30s才查询一次, Client的tcp_keepalive_time
设置为15, 那么20s后,A应用是否会超时异常呢?
答案是肯定的,操作系统的tcp_keepalive_time
只是一个四层的ACK空包,mysql并不鸟它。
测试如下:
# echo 15 > /proc/sys/net/ipv4/tcp_keepalive_time
16:26:22 启动应用, 可以看到在16:26:37操作系统发keepalive包, 16:26:42 超时断开。抓包如下:
16:26:22.181299 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [P.], seq 438:451, ack 696, win 5, length 13
16:26:22.214075 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [P.], seq 696:707, ack 451, win 237, length 11
16:26:22.253305 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 707, win 5, length 0
16:26:37.214296 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 707, win 5, length 0
16:26:37.246975 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [.], ack 451, win 237, length 0
16:26:42.234322 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [F.], seq 707, ack 451, win 237, length 0
16:26:42.274277 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [.], ack 708, win 5, length 0
16:26:52.240972 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [P.], seq 451:537, ack 708, win 5, length 86
16:26:52.241067 IP 192.168.0.200.39574 > 192.168.0.100.3306: Flags [F.], seq 537, ack 708, win 5, length 0
16:26:52.273747 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [R], seq 2919111959, win 0, length 0
16:26:52.273787 IP 192.168.0.100.3306 > 192.168.0.200.39574: Flags [R], seq 2919111959, win 0, length 0
标签:global,192.168,session,timeout,MySQL,wait,interactive
From: https://www.cnblogs.com/doctormo/p/18323820