mysql java lient_interactive_MySQL之wait_timeout和interactive_timeout参数

最新推荐文章于 2023-03-18 22:55:41 发布

ONES Piece

最新推荐文章于 2023-03-18 22:55:41 发布

阅读量170

点赞数

文章标签： mysql java lient_interactive

本文链接：https://blog.csdn.net/weixin_28813401/article/details/113291711

版权

引言

在用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的设置有关。

1. interactive_timeout参数定义

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.

interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

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

2. wait_timeout参数定义

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.

服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。

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

控制连接最大空闲时长的参数及验证

控制连接最大空闲时长的参数是: wait_timeout

验证: 1. 只修改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 |

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

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

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 |

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

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后再执行操作，连接已经断开。

验证: 2. 只修改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 |

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

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 |

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

rows in set (0.06 sec)

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 |

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

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 |

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

rows in set (0.06 sec)

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

会话变量wait_timeout的继承问题

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

验证1: 只修改全局变量interactive_timeout的值

1. 交互式连接修改INTERACTIVE_TIMEOUT值

打开一个Mysql客户端修改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 |

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

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 |

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

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 |

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

rows in set (0.00 sec)

WAIT_TIMEOUT的值已经变为10，继承INTERACTIVE_TIMEOUT的值。

2. 非交互式连接修改INTERACTIVE_TIMEOUT值

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的值依旧是28800，没有继承INTERACTIVE_TIMEOUT的值

验证2: 只修改全局变量wait_timeout的值

1. 交互式连接修改wait_timeout值

打开一个Mysql客户端修改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 |

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

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 |

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

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 |

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

rows in set (0.03 sec)

wait_timeout的值依旧是28800，没有继承刚才设置的WAIT_TIMEOUT值

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));

}

2. 非交互式连接修改wait_timeout值

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));

}

输出结果

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 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

wait_timeout的变为20，继承刚才设置的WAIT_TIMEOUT值

总结

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

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

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

mysql> show processlist;

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

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

| 6 | repl | 192.168.244.20:44641 | NULL | Sleep | 1154 | | NULL |

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

rows in set (0.03 sec)

参考

https://www.cnblogs.com/ivictor/p/5979731.html

http://www.cnblogs.com/Alight/p/4118515.html

http://www.cnblogs.com/jiunadianshi/articles/2475475.html

ONES Piece

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql java lient_interactive_MySQL之wait_timeout和interactive_timeout参数

引言在用mysql客户端对数据库进行操作时，打开终端窗口，如果一段时间没有操作，再次操作时，常常会报如下错误：ERROR 2013 (HY000): Lost connection to MySQL server during queryERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect....
复制链接

扫一扫

mysql java lient_interactive_MySQL之wait_timeout和interactive_timeout参数

“相关推荐”对你有帮助么？