当前位置：移动技术网 > IT编程>数据库>Mysql > MySQL中interactive_timeout和wait_timeout的区别

MySQL中interactive_timeout和wait_timeout的区别

2017年12月12日 | 移动技术网IT编程 | 我要评论

血咒暗礁在哪,戚薇动态图片,新华南路租房

在用mysql客户端对数据库进行操作时，打开终端窗口，如果一段时间没有操作，再次操作时，常常会报如下错误：

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 for activity 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参数

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.03 sec)

mysql> set session wait_timeout=10;
query ok, 0 rows affected (0.00 sec)
-------等待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

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

只修改interactive_timeout参数

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.06 sec)
mysql> set session interactive_timeout=10;
query ok, 0 rows affected (0.00 sec)
----------等待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)

q2：会话变量wait_timeout的继承问题

a2：如果是交互式连接，则继承全局变量interactive_timeout的值，如果是非交互式连接，则继承全局变量wait_timeout的值。

验证：

只修改全局变量interactive_timeout的值

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout'); 
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+---------------------+----------------+
2 rows in set (0.13 sec)

mysql> set global interactive_timeout=10;
query ok, 0 rows affected (0.00 sec)
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| interactive_timeout | 10 |
| wait_timeout | 28800 |
+---------------------+----------------+
2 rows in set (0.00 sec)

开启另外一个mysql客户端，查看会话变量的值

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 | 10 |
+---------------------+----------------+
2 rows in set (0.00 sec)

发现，wait_timeout的值已经变为10了。

但通过jdbc测试，wait_timeout的值依旧是28800

public class jdbc_test {
@suppresswarnings("static-access")
public static void main(string[] args) throws exception {
connection conn = null;
statement stmt = null;
resultset rs = null;
string url = "jdbc:mysql://192.168.244.10:3306/test";
string user = "root";
string password = "123456";
class.forname("com.mysql.jdbc.driver");
conn = drivermanager.getconnection(url, user, password);
stmt = conn.createstatement();
string sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
rs = stmt.executequery(sql);
while (rs.next()) {
system.out
.println(rs.getstring(1)+": "+rs.getstring(2));
}
}
}

结果输出如下：

interactive_timeout: 10
wait_timeout: 28800

只修改全局变量wait_timeout的值

mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800 |
| wait_timeout | 28800 |
+---------------------+----------------+
2 rows in set (0.17 sec)
mysql> set global wait_timeout=20;
query ok, 0 rows affected (0.07 sec)
mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
it_timeout');+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| interactive_timeout | 28800 |
| wait_timeout | 20 |
+---------------------+----------------+
2 rows in set (0.00 sec)

开启另外一个mysql客户端，查看会话变量的值

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.03 sec)

wait_timeout的值依旧是28800.

查看jdbc的结果

public class jdbc_test {
@suppresswarnings("static-access")
public static void main(string[] args) throws exception {
connection conn = null;
statement stmt = null;
resultset rs = null;
string url = "jdbc:mysql://192.168.244.10:3306/test";
string user = "root";
string password = "123456";
class.forname("com.mysql.jdbc.driver");
conn = drivermanager.getconnection(url, user, password);
stmt = conn.createstatement();
string sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
rs = stmt.executequery(sql);
while (rs.next()) {
system.out
.println(rs.getstring(1)+": "+rs.getstring(2));
}
thread.currentthread().sleep(21000);
sql = "select 1 from dual";
rs = stmt.executequery(sql);
while (rs.next()) {
system.out
.println(rs.getint(1));
}
}
}

查看jdbc的结果

interactive_timeout: 28800
wait_timeout: 20

同时，新增了一段程序，等待20s后，再次执行查询，报如下错误：

exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.communicationsexception: communications link failure

last packet sent to the server was 12 ms ago.
at sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method)
at sun.reflect.nativeconstructoraccessorimpl.newinstance(unknown source)
at sun.reflect.delegatingconstructoraccessorimpl.newinstance(unknown source)
at java.lang.reflect.constructor.newinstance(unknown source)
at com.mysql.jdbc.util.handlenewinstance(util.java:406)
at com.mysql.jdbc.sqlerror.createcommunicationsexception(sqlerror.java:1074)
at com.mysql.jdbc.mysqlio.reuseandreadpacket(mysqlio.java:3009)
at com.mysql.jdbc.mysqlio.reuseandreadpacket(mysqlio.java:2895)
at com.mysql.jdbc.mysqlio.checkerrorpacket(mysqlio.java:3438)
at com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:1951)
at com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2101)
at com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2548)
at com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2477)
at com.mysql.jdbc.statementimpl.executequery(statementimpl.java:1422)
at com.victor_01.jdbc_test.main(jdbc_test.java:29)
caused by: java.net.socketexception: software caused connection abort: recv failed
at java.net.socketinputstream.socketread0(native method)
at java.net.socketinputstream.socketread(unknown source)
at java.net.socketinputstream.read(unknown source)
at java.net.socketinputstream.read(unknown source)
at com.mysql.jdbc.util.readaheadinputstream.fill(readaheadinputstream.java:113)
at com.mysql.jdbc.util.readaheadinputstream.readfromunderlyingstreamifnecessary(readaheadinputstream.java:160)
at com.mysql.jdbc.util.readaheadinputstream.read(readaheadinputstream.java:188)
at com.mysql.jdbc.mysqlio.readfully(mysqlio.java:2452)
at com.mysql.jdbc.mysqlio.reuseandreadpacket(mysqlio.java:2906)
... 8 more

总结

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

2. 对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。

对于交互式连接，类似于mysql客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。

3. 判断一个连接的空闲时间，可通过show processlist输出中sleep状态的时间

mysql> show processlist;
+----+------+----------------------+------+---------+------+-------+------------------+
| id | user | host | db | command | time | state | info |
+----+------+----------------------+------+---------+------+-------+------------------+
| 2 | root | localhost | null | query | 0 | init | show processlist |
| 6 | repl | 192.168.244.20:44641 | null | sleep | 1154 | | null |
+----+------+----------------------+------+---------+------+-------+------------------+
2 rows in set (0.03 sec)

以上所述是小编给大家介绍的mysql中interactive_timeout和wait_timeout的区别，希望对大家有所帮助

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！点击进行留言回复

MySQL查看和修改事务隔离级别的实例讲解

查看事务隔离级别在 mysql 中，可以通过show variables like '%tx_isolation%'或select @@tx_isolation... [阅读全文]
小白安装登录mysql-8.0.19-winx64的教程图解(新手必看)

一、下载安装包（64位）mysql官网下载链接：点击下载下拉网页找到下图链接点击上述链接，找到下图链接选择如图版本点击下载。二、安装mysql数据库解压安装包，... [阅读全文]
MySQL数据库对敏感数据加密及解密的实现方式

大数据时代的到来，数据成为企业最重要的资产之一，数据加密的也是保护数据资产的重要手段。本文主要在结合学习通过mysql函数及python加密方法来演示数据加密的... [阅读全文]
Navicat连接MySQL时报10060、1045错误及my.ini位置问题

navicat连接数据库的10060及1045报错，mysql8.0.18的my.ini文件在哪出现这种现象的原因是3306端口被被防火墙禁掉，无法连接到该端口... [阅读全文]
windows10下 MySQL msi安装教程图文详解

一、下载1、点击mysql msi官网最新下载或复制链接。2、根据自己的需求进行下载，这里选择的是windows (x86, 32-bit), msi ins... [阅读全文]
mysql update语句的执行过程详解

以前有过一篇关于mysql查询语句的执行过程，这里总结一下update语句的执行过程。由于update涉及到数据的修改，所以，很容易推断，update语句比se... [阅读全文]
Mysql事务隔离级别原理实例解析

引言大家在面试中一定碰到过说说事务的隔离级别吧？老实说，事务隔离级别这个问题，无论是校招还是社招，面试官都爱问！然而目前网上很多文章，说句实在话啊，我看了后我都... [阅读全文]
简单了解MySQL union all与union的区别

union 是对数据进行并集操作，不包括重复行，同时进行默认排序union all 是对数据进行并集操作，包括重复行，不进行排序举例说明：创建数据库表：crea... [阅读全文]
MySQL case when使用方法实例解析

首先我们创建数据库表：create table `t_demo` ( `id` int(32) not null, `name` varchar(255) de... [阅读全文]
mysql事务管理操作详解

本文实例讲述了mysql事务管理操作。分享给大家供大家参考，具体如下：本文内容：什么是事务管理事务管理操作回滚点默认的事务管理首发日期：2018... [阅读全文]

网友评论


验证码：

MySQL中interactive_timeout和wait_timeout的区别

2017年12月12日 | 移动技术网IT编程 | 我要评论

您可能感兴趣的文章:

相关文章:

网友评论