MySQL中interactive_timeout和wait_timeout的区别

2024-03-11 00:18:02 187

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

ERROR2013(HY000):LostconnectiontoMySQLserverduringquery
ERROR2006(HY000):MySQLserverhasgoneaway
Noconnection.Tryingtoreconnect...

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

那么，连接的时长是如何确认的？

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

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

interactive_timeout

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

Thenumberofsecondstheserverwaitsforactivityonaninteractiveconnectionbeforeclosingit.AninteractiveclientisdefinedasaclientthatusestheCLIENT_INTERACTIVEoptiontomysql_real_connect().Seealsowait_timeout.

wait_timeout

默认同样是28800s

Thenumberofsecondstheserverwaitsforactivityonanoninteractiveconnectionbeforeclosingit.

Onthreadstartup,thesessionwait_timeoutvalueisinitializedfromtheglobalwait_timeoutvalueorfromtheglobalinteractive_timeoutvalue,dependingonthetypeofclient(asdefinedbytheCLIENT_INTERACTIVEconnectoptiontomysql_real_connect()).Seealsointeractive_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>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.03sec)

mysql>setsessionWAIT_TIMEOUT=10;
QueryOK,0rowsaffected(0.00sec)
-------等待10s后再执行
mysql>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
ERROR2013(HY000):LostconnectiontoMySQLserverduringquery

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

只修改interactive_timeout参数

mysql>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.06sec)
mysql>setsessionINTERACTIVE_TIMEOUT=10;
QueryOK,0rowsaffected(0.00sec)
----------等待10s后执行
mysql>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|10|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.06sec)

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

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

验证：

只修改全局变量interactive_timeout的值

mysql>selectvariable_name,variable_valuefrominformation_schema.global_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.13sec)

mysql>setglobalINTERACTIVE_TIMEOUT=10;
QueryOK,0rowsaffected(0.00sec)
mysql>selectvariable_name,variable_valuefrominformation_schema.global_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|10|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.00sec)

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

mysql>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|10|
|WAIT_TIMEOUT|10|
+---------------------+----------------+
2rowsinset(0.00sec)

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

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

publicclassJdbc_test{
@SuppressWarnings("static-access")
publicstaticvoidmain(String[]args)throwsException{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
Stringurl="jdbc:mysql://192.168.244.10:3306/test";
Stringuser="root";
Stringpassword="123456";
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
Stringsql="selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('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>selectvariable_name,variable_valuefrominformation_schema.global_variableswherevariable_namein('interactive_timeout','wa
it_timeout');+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.17sec)
mysql>setglobalWAIT_TIMEOUT=20;
QueryOK,0rowsaffected(0.07sec)
mysql>selectvariable_name,variable_valuefrominformation_schema.global_variableswherevariable_namein('interactive_timeout','wa
it_timeout');+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|20|
+---------------------+----------------+
2rowsinset(0.00sec)

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

mysql>selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('interactive_timeout','wait_timeout');
+---------------------+----------------+
|variable_name|variable_value|
+---------------------+----------------+
|INTERACTIVE_TIMEOUT|28800|
|WAIT_TIMEOUT|28800|
+---------------------+----------------+
2rowsinset(0.03sec)

WAIT_TIMEOUT的值依旧是28800.

查看jdbc的结果

publicclassJdbc_test{
@SuppressWarnings("static-access")
publicstaticvoidmain(String[]args)throwsException{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
Stringurl="jdbc:mysql://192.168.244.10:3306/test";
Stringuser="root";
Stringpassword="123456";
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection(url,user,password);
stmt=conn.createStatement();
Stringsql="selectvariable_name,variable_valuefrominformation_schema.session_variableswherevariable_namein('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="select1fromdual";
rs=stmt.executeQuery(sql);
while(rs.next()){
System.out
.println(rs.getInt(1));
}
}
}

查看jdbc的结果

INTERACTIVE_TIMEOUT:28800
WAIT_TIMEOUT:20

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

Exceptioninthread"main"com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:Communicationslinkfailure

Lastpacketsenttotheserverwas12msago.
atsun.reflect.NativeConstructorAccessorImpl.newInstance0(NativeMethod)
atsun.reflect.NativeConstructorAccessorImpl.newInstance(UnknownSource)
atsun.reflect.DelegatingConstructorAccessorImpl.newInstance(UnknownSource)
atjava.lang.reflect.Constructor.newInstance(UnknownSource)
atcom.mysql.jdbc.Util.handleNewInstance(Util.java:406)
atcom.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
atcom.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
atcom.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
atcom.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
atcom.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
atcom.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
atcom.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
atcom.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
atcom.victor_01.Jdbc_test.main(Jdbc_test.java:29)
Causedby:java.net.SocketException:Softwarecausedconnectionabort:recvfailed
atjava.net.SocketInputStream.socketRead0(NativeMethod)
atjava.net.SocketInputStream.socketRead(UnknownSource)
atjava.net.SocketInputStream.read(UnknownSource)
atjava.net.SocketInputStream.read(UnknownSource)
atcom.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
atcom.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
atcom.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
atcom.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)
atcom.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
...8more

总结

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

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

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

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

mysql>showprocesslist;
+----+------+----------------------+------+---------+------+-------+------------------+
|Id|User|Host|db|Command|Time|State|Info|
+----+------+----------------------+------+---------+------+-------+------------------+
|2|root|localhost|NULL|Query|0|init|showprocesslist|
|6|repl|192.168.244.20:44641|NULL|Sleep|1154||NULL|
+----+------+----------------------+------+---------+------+-------+------------------+
2rowsinset(0.03sec)

以上所述是小编给大家介绍的MySQL中interactive_timeout和wait_timeout的区别，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

MySQL中interactive_timeout和wait_timeout的区别

热门推荐

随机推荐