MySQL中interactive_timeout和wait_timeout的区别
网络编程 2021-07-05 14:36www.168986.cn编程入门
这篇文章主要介绍了MySQL中interactive_timeout和wait_timeout的区别,非常不错具有参考借鉴价值，需要的朋友可以参考下
在用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。
狼蚁网站SEO优化来测试一下，确认如下问题
1. 控制连接最大空闲时长的是哪个参数。
2. 会话变量wait_timeout的继承问题
Q1控制连接最大空闲时长的是哪个参数
A1wait_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-aess")
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(".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-aess")
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(".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" .mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 12 ms ago. at sun.reflect.NativeConstructorAessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at .mysql.jdbc.Util.handleNewInstance(Util.java:406) at .mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) at .mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009) at .mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895) at .mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438) at .mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951) at .mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101) at .mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548) at .mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477) at .mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422) at .victor_01.Jdbc_test.main(Jdbc_test.java:29) Caused by: java..SocketException: Software caused connection abort: recv failed at java..SocketInputStream.socketRead0(Native Method) at java..SocketInputStream.socketRead(Unknown Source) at java..SocketInputStream.read(Unknown Source) at java..SocketInputStream.read(Unknown Source) at .mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) at .mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) at .mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) at .mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452) at .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的区别，希望对大家有所帮助，如果大家有任何疑问请给我留言，长沙网络推广会及时回复大家的。在此也非常感谢大家对狼蚁SEO网站的支持！
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程：电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示：编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程