mysql timeout分析_MySQL interactive_timeout wait_timeout详细讲解及区别分析

ERROR 2013 (HY000): Lost connection to MySQL server during query

ERROR 2006(HY000): MySQL server has gone away

No connection. Trying to reconnect...

这个报错信息就意味着当前的连接已经断开,需要重新建立连接。

那么,连接的时长是如何确认的?

其实,这个与interactive_timeout和wait_timeout的设置有关。

首先,看看官方文档对于这两个参数的定义

interactive_timeout

默认是28800,单位秒,即8个小时

The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout.

wait_timeout

默认同样是28800s

The number of seconds the server waits foractivity on a noninteractive connection before closing it.

On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

根据上述定义,两者的区别显而易见

1> interactive_timeout针对交互式连接,wait_timeout针对非交互式连接。所谓的交互式连接,即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。

说得直白一点,通过mysql客户端连接数据库是交互式连接,通过jdbc连接数据库是非交互式连接。

2> 在连接启动的时候,根据连接的类型,来确认会话变量wait_timeout的值是继承于全局变量wait_timeout,还是interactive_timeout。

下面来测试一下,确认如下问题

1. 控制连接最大空闲时长的是哪个参数。

2. 会话变量wait_timeout的继承问题

Q1:控制连接最大空闲时长的是哪个参数

A1:wait_timeout

验证

只修改wait_timeout参数

c66cbb628838e0aaf29bc97fc96540a0.gif

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');

+---------------------+----------------+

| variable_name | variable_value |

+---------------------+----------------+

| INTERACTIVE_TIMEOUT | 28800 |

| WAIT_TIMEOUT | 28800 |

+---------------------+----------------+

2 rows in set (0.03sec)

mysql> set session WAIT_TIMEOUT=10;

Query OK, 0 rows affected (0.00sec)

-------等待10s后再执行

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');

ERROR 2013 (HY000): Lost connection to MySQL server during query

c66cbb628838e0aaf29bc97fc96540a0.gif

可以看到,等待10s后再执行操作,连接已经断开。

只修改interactive_timeout参数

c66cbb628838e0aaf29bc97fc96540a0.gif

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');

+---------------------+----------------+

| variable_name | variable_value |

+---------------------+----------------+

| INTERACTIVE_TIMEOUT | 28800 |

| WAIT_TIMEOUT | 28800 |

+---------------------+----------------+

2 rows in set (0.06sec)

mysql> set session INTERACTIVE_TIMEOUT=10;

Query OK, 0 rows affected (0.00sec) ----------等待10s后执行

mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');

+---------------------+----------------+

| variable_name | variable_value |

+---------------------+----------------+

| INTERACTIVE_TIMEOUT | 10 |

| WAIT_TIMEOUT | 28800 |

+---------------------+----------------+

2 rows in set (0.06 sec)

c66cbb628838e0aaf29bc97fc96540a0.gif

Q2:会话变量wait_timeout的继承问题

A2:如果是交互式连接,则继承全局变量interactive_timeout的值,如果是非交互式连接,则继承全局变量wait_timeout的值。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值