引言
在用 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
可以看到,等待 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)
可以看到,等待 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 值
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 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;
+----+------+----------------------+------+---------+------+-------+------------------+
| 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 |
+----+------+----------------------+------+---------+------+-------+------------------+
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
更多内容请关注公众号
![](https://raw.githubusercontent.com/tsinghuabang/picture/master/markdown/qrcode/yunxiaomi-15cm.jpg)
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于