MySQL中interactive_timeout和wait_timeout的区别(https://www.cnblogs.com/ivictor/p/5979731.html)
来源:互联网 发布:c语言代码大全表解释 编辑:程序博客网 时间:2024/05/01 15:38
在用mysql客户端对数据库进行操作时,打开终端窗口,如果一段时间没有操作,再次操作时,常常会报如下错误:
ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone awayNo 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: 10WAIT_TIMEOUT: 28800
只修改全局变量wait_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.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','wait_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: 28800WAIT_TIMEOUT: 20
同时,新增了一段程序,等待20s后,再次执行查询,报如下错误:
Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureLast 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)
参考
1. http://www.cnblogs.com/cenalulu/archive/2012/06/20/2554863.html
2. http://www.cnblogs.com/Alight/p/4118515.html
3. http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/
- MySQL中interactive_timeout和wait_timeout的区别(https://www.cnblogs.com/ivictor/p/5979731.html)
- MySQL中interactive_timeout和wait_timeout的区别
- mysql interactive_timeout wait_timeout区别
- JSON技术的调研报告(https://www.cnblogs.com/kunpengit/p/4001680.html)
- wait_timeout和interactive_timeout区别
- wait_timeout和interactive_timeout区别
- wait_timeout和interactive_timeout区别
- wait_timeout和interactive_timeout区别
- wait_timeout和interactive_timeout区别
- Verilog中wire与reg类型的区别(转载自http://www.cnblogs.com/farbeyond/p/5204586.html)
- https://www.cnblogs.com/sea-star/p/rtcp.html
- https://www.cnblogs.com/by-dream/p/5611555.html
- https://www.cnblogs.com/Y1Focus/p/6707121.html
- HTTPS://WWW.CNBLOGS.COM/ANDROID-JOKER/P/4414891.HTML https://www.cnblogs.com/android-joker/p/4414891
- IntelliJ IDEA2017 修改缓存文件的路径(转载:https://www.cnblogs.com/acm-bingzi/p/ideaCachePath.html)
- ServletConfig获取配置信息、ServletContext的应用https://www.cnblogs.com/smyhvae/p/4140877.html
- redis学习命令大全(转载https://www.cnblogs.com/woshimrf/p/5198361.html)
- mysql wait_timeout和interactive_timeout总结
- 模式识别 评价方法 ROC曲线, DET曲线, FPPW, FPPI etc.
- 在lnmp环境下引入xunsearch中的XS.php问题
- 判断手机浏览器的类型
- java从小白到工程师--基础篇(一)
- jQuery获取包括当前元素的HTML
- MySQL中interactive_timeout和wait_timeout的区别(https://www.cnblogs.com/ivictor/p/5979731.html)
- ubuntu16.04下twisted安装
- screen 关闭会话,不进的情况下
- 两年Java开发工作经验面试总结
- Javascript学习中的一些注意点(基本语法)
- 格式: echo -e "\033[字背景颜色;字体颜色m字符串\033[0m"
- 支付宝绑定社保;微信正式上线"零钱通"功能|ServiceHot一周热闻
- JAVA必背面试题和项目面试通关要点、、
- 5.常用命令