【MySQL】参数wait_timeout和interactive_timeout的作用和区别是什么？
【MySQL】参数wait_timeout和interactive_timeout的作用和区别是什么？
真题1、参数wait_timeout和interactive_timeout的作用和区别是什么？
答案：interactive_timeout表示MySQL服务器关闭交互式连接前等待活动的秒数；wait_timeout表示MySQL服务器关闭非交互连接之前等待活动的秒数。这2个参数的默认值都是28800，单位秒，即8个小时。需要注意的是，这2个参数需要同时设置才会生效。
参数interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点就是，通过MySQL客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。其实，针对Client，真正生效的是会话级别的wait_timeout，空闲连接（交互和非交互）超过其会话级别的wait_timeout时间就会被回收掉。在客户端连接启动的时候，根据连接的类型，来确认会话变量wait_timeout的值是继承于全局变量wait_timeout，还是interactive_timeout。对于非交互式连接，类似于jdbc连接，wait_timeout的值继承自服务器端全局变量wait_timeout。对于交互式连接，类似于MySQL客户单连接，wait_timeout的值继承自服务器端全局变量interactive_timeout。
可通过查看show processlist输出中Sleep状态的时间来判断一个连接的空闲时间。若超过wait_timeout的值，则会报类似于如下的错误：
mysql> set session WAIT_TIMEOUT=3;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
mysql> show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 50
Current database: lhrdb
+----+------+-------------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-------------+-------+---------+------+----------+------------------+
| 7 | root | localhost | NULL | Sleep | 7131 | | NULL |
| 26 | root | localhost | NULL | Sleep | 7071 | | NULL |
| 27 | root | localhost | NULL | Sleep | 7064 | | NULL |
| 28 | root | LHRDB:47234 | NULL | Sleep | 6803 | | NULL |
| 50 | root | LHRDB:47238 | lhrdb | Query | 0 | starting | show processlist |
+----+------+-------------+-------+---------+------+----------+------------------+
5 rows in set (0.02 sec)
& 说明：
有关参数wait_timeout和interactive_timeout的更多内容可以参考我的博客：http://blog.itpub.net/26736162/viewspace-2149454/
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。
下面来测试一下，确认如下问题
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 |
+---------------------+----------------+
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
+---------------------+----------------+
| 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后再执行操作，连接已经断开。
只修改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)
+---------------------+----------------+
| 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)
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 |
+---------------------+----------------+
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)
+---------------------+----------------+
| 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)
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 10 |
| WAIT_TIMEOUT | 10 |
+---------------------+----------------+
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: 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 |
+---------------------+----------------+
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)
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)
+---------------------+----------------+
| variable_name | variable_value |
+---------------------+----------------+
| INTERACTIVE_TIMEOUT | 28800 |
| WAIT_TIMEOUT | 28800 |
+---------------------+----------------+
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: 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
总结
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 |
+----+------+----------------------+------+---------+------+-------+------------------+
rows in set (0.03 sec)
+----+------+----------------------+------+---------+------+-------+------------------+
| 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)
参考
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(可能导致过多sleep进程的两个参数)
1)interactive_timeout:
参数含义：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
参数默认值：28800秒（8小时）
(2)wait_timeout:
参数含义：服务器关闭非交互连接之前等待活动的秒数。
在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。
参数默认值：28800秒（8小时）
MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。
问题：
如果在配置文件my.cnf中只设置参数wait_timeout=100,则重启服务器后进入，执行:
Mysql> show variables like “%timeout%”;
会发现参数设置并未生效，仍然为28800（即默认的8个小时）。
查询资料后，要同时设置interactive_timeout和wait_timeout才会生效。
【mysqld】
wait_timeout=100
interactive_timeout=100
重启MySQL Server进入后，查看设置已经生效。
问题1：这里为什么要同时设置interactive_timeout,wait_timeout的设置才会生效？
问题2：interactive的值如果设置的和wait_timeout不同，为什么Interactive_timeout会覆盖wait_timeout?
问题3：在进行MySQL优化时，因为interactive_timeout决定的是交互连接的时间长短，而wait_timeout决定的是非交互连接的时间长短。如果在进行连接配置时mysql_real_connect()最后一个参数client_flag不设置为CLIENT_INTERACTIVE，是不是interactive_timeout的值不会覆盖wait_timeout?
问题4：为了减少长连接的数量，在设置优化时是不是可以将interactive_timeout的值设置的大些，而wait_timeout的值设置的小些？但是问题2的描述好像又不允许这样。。。
关于MySQL的interactive_timeout和wait_timeout
| [日期：2017-08-18] | 来源：Linux社区 作者：杨奇龙 | [字体：大 中 小] |
mysql的wait_timeout和interactive_timeout 2015-06-30 18:07:30
[MySQL分享]--interactive_timeout和wait_timeout小结（interactive_timeout会覆盖wait_timeout）
原创 2015年09月21日 16:50:23
- 标签：
- mysql /
- interactive_timeout /
- wait_timeout
- 1836
About Me
.............................................................................................................................................
● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群 小麦苗的微店
.............................................................................................................................................
###wait_timeout interactive_timeout####
wait_timeout :对于非交互连接，mysql在关闭它之前的等待时间
这个值的初始化根据连接客户端的连接方式不同：有两种方式：全局的wait_timeout或者是全局的interactive_timeout
interactive_timeout：对于交互连接，mysql在关闭它之前的等待时间
测试：
root:3406:(none)>show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 21 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 21 |
+-----------------------------+----------+
14 rows in set (0.00 sec)
root:3406:(none)>show global variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 21 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 14 |
+-----------------------------+----------+
14 rows in set (0.00 sec)
root:3406:(none)>show global variables like '%timeout%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 21 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 14 |
+-----------------------------+----------+
14 rows in set (0.00 sec)
上面是测试交互输入的方式，
wait_timeout =14
interactive_timeout=21
可以看到session级别的wait_timeout的初始化使用了interactive_timeout
当超过了21s后，再执行命令连接已经被干掉了
综上：
针对client其实真正生效的是session级别的wait_timeout，空闲连接（交互和非交互），
超过其session级别的wait_timeout时间就会被回收掉：
伪代码:
if (client_connection is interactive) then
session wait_timeout= global interactive_timeout
else if(client_connection is noninteractive ) then
session wait_timeout= global wait_timeout
end if
正文到此结束
- 本文标签： https sql CTO 参数 schema 关键词 ORM 服务器 struct cat 时间 二维码 wait_timeout 博客 queue root mail 进程 代码 list show processlist 删除 微信公众号 模型 java db 线程 Menu value Connection http 2015 id 云 windows 数据库 IDE key QQ群 ECS 文章 希望 mysql tab tar 数据 Select SQL Server DDL python 短连接 linux 测试 UI 解析 同步 src ACE 长连接 ssl CDN 配置 HTML 总结 client Word IO
- 版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
- 本文海报： 生成海报一 生成海报二
热门推荐
相关文章
近期评论
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
搞个gitee的项目
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
