MySQL中interactive_timeout和wait_timeout的区别

短命女 2022-05-25 11:42 217阅读 0赞

MySQL中interactive_timeout和wait_timeout的区别

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

  1. ERROR 2013 (HY000): Lost connection to MySQL server during query
  3. ERROR 2006 (HY000): MySQL server has gone away
  4. No connection. Trying to reconnect...

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

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

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

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

interactive_timeout

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

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

  1. The number of seconds the server waits for activity on a noninteractive connection before closing it.
  3. 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选项。

  1.  说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

2> 在连接启动的时候，根据连接的类型，来确认会话变量wait_timeout的值是继承于全局变量wait_timeout，还是interactive_timeout。

下面来测试一下，确认如下问题

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

  2. 会话变量wait_timeout的继承问题

Q1：控制连接最大空闲时长的是哪个参数

A1：wait_timeout

验证

只修改wait_timeout参数

  1. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  2. +---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 28800          |
  6. | WAIT_TIMEOUT        | 28800          |
  7. +---------------------+----------------+
  8. 2 rows in set (0.03 sec)
  10. mysql> set session WAIT_TIMEOUT=10;
  11. Query OK, 0 rows affected (0.00 sec)
  12. -------等待10s后再执行
  13. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  14. ERROR 2013 (HY000): Lost connection to MySQL server during query

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

只修改interactive_timeout参数

  1. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  2. +---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 28800          |
  6. | WAIT_TIMEOUT        | 28800          |
  7. +---------------------+----------------+
  8. 2 rows in set (0.06 sec)
  10. mysql> set session INTERACTIVE_TIMEOUT=10;
  11. Query OK, 0 rows affected (0.00 sec)
  12. ----------等待10s后执行
  13. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  14. +---------------------+----------------+
  15. | variable_name       | variable_value |
  16. +---------------------+----------------+
  17. | INTERACTIVE_TIMEOUT | 10             |
  18. | WAIT_TIMEOUT        | 28800          |
  19. +---------------------+----------------+
  20. 2 rows in set (0.06 sec)

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

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

验证：

只修改全局变量interactive_timeout的值

  1. mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout'); 
  2. +---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 28800          |
  6. | WAIT_TIMEOUT        | 28800          |
  7. +---------------------+----------------+
  8. 2 rows in set (0.13 sec)
  10. mysql> set global INTERACTIVE_TIMEOUT=10;
  11. Query OK, 0 rows affected (0.00 sec)
  13. mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wait_timeout');
  14. +---------------------+----------------+
  15. | variable_name       | variable_value |
  16. +---------------------+----------------+
  17. | INTERACTIVE_TIMEOUT | 10             |
  18. | WAIT_TIMEOUT        | 28800          |
  19. +---------------------+----------------+
  20. 2 rows in set (0.00 sec)

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

  1. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  2. +---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 10             |
  6. | WAIT_TIMEOUT        | 10             |
  7. +---------------------+----------------+
  8. 2 rows in set (0.00 sec)

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

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

  1. public class Jdbc_test {
  2.     @SuppressWarnings("static-access")
  3.     public static void main(String[] args) throws Exception {
  4.          Connection conn = null;
  5.          Statement stmt = null;
  6.          ResultSet rs = null;
  7.          String url = "jdbc:mysql://192.168.244.10:3306/test";
  8.          String user = "root";
  9.          String password = "123456";
  10.          Class.forName("com.mysql.jdbc.Driver");
  11.          conn = DriverManager.getConnection(url, user, password);
  12.          stmt = conn.createStatement();
  13.          String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
  14.          rs = stmt.executeQuery(sql);
  15.          while (rs.next()) {
  16.              System.out
  17.                      .println(rs.getString(1)+":  "+rs.getString(2));
  18.          }
  19.     }
  20. }

结果输出如下：

  1. INTERACTIVE_TIMEOUT:  10
  2. WAIT_TIMEOUT:  28800

只修改全局变量wait_timeout的值

  1. mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
  2. it_timeout');+---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 28800          |
  6. | WAIT_TIMEOUT        | 28800          |
  7. +---------------------+----------------+
  8. 2 rows in set (0.17 sec)
  10. mysql> set global WAIT_TIMEOUT=20;
  11. Query OK, 0 rows affected (0.07 sec)
  13. mysql> select variable_name,variable_value from information_schema.global_variables where variable_name in ('interactive_timeout','wa
  14. it_timeout');+---------------------+----------------+
  15. | variable_name       | variable_value |
  16. +---------------------+----------------+
  17. | INTERACTIVE_TIMEOUT | 28800          |
  18. | WAIT_TIMEOUT        | 20             |
  19. +---------------------+----------------+
  20. 2 rows in set (0.00 sec)

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

  1. mysql> select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout');
  2. +---------------------+----------------+
  3. | variable_name       | variable_value |
  4. +---------------------+----------------+
  5. | INTERACTIVE_TIMEOUT | 28800          |
  6. | WAIT_TIMEOUT        | 28800          |
  7. +---------------------+----------------+
  8. 2 rows in set (0.03 sec)

WAIT_TIMEOUT的值依旧是28800.

查看jdbc的结果

  1. public class Jdbc_test {
  2.     @SuppressWarnings("static-access")
  3.     public static void main(String[] args) throws Exception {
  4.          Connection conn = null;
  5.          Statement stmt = null;
  6.          ResultSet rs = null;
  7.          String url = "jdbc:mysql://192.168.244.10:3306/test";
  8.          String user = "root";
  9.          String password = "123456";
  10.          Class.forName("com.mysql.jdbc.Driver");
  11.          conn = DriverManager.getConnection(url, user, password);
  12.          stmt = conn.createStatement();
  13.          String sql = "select variable_name,variable_value from information_schema.session_variables where variable_name in ('interactive_timeout','wait_timeout')";
  14.          rs = stmt.executeQuery(sql);
  15.          while (rs.next()) {
  16.              System.out
  17.                      .println(rs.getString(1)+":  "+rs.getString(2));
  18.          }
  19.          Thread.currentThread().sleep(21000);
  20.          sql = "select 1 from dual";
  21.          rs = stmt.executeQuery(sql);
  22.          while (rs.next()) {
  23.              System.out
  24.                      .println(rs.getInt(1));
  25.          }
  27.     }
  28. }

查看jdbc的结果

  1. INTERACTIVE_TIMEOUT:  28800
  2. WAIT_TIMEOUT:  20

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

  1. Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  3. Last packet sent to the server was 12 ms ago.
  4.     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  5.     at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
  6.     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
  7.     at java.lang.reflect.Constructor.newInstance(Unknown Source)
  8.     at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
  9.     at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
  10.     at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
  11.     at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
  12.     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
  13.     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
  14.     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
  15.     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
  16.     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2477)
  17.     at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
  18.     at com.victor_01.Jdbc_test.main(Jdbc_test.java:29)
  19. Caused by: java.net.SocketException: Software caused connection abort: recv failed
  20.     at java.net.SocketInputStream.socketRead0(Native Method)
  21.     at java.net.SocketInputStream.socketRead(Unknown Source)
  22.     at java.net.SocketInputStream.read(Unknown Source)
  23.     at java.net.SocketInputStream.read(Unknown Source)
  24.     at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
  25.     at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
  26.     at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
  27.     at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)
  28.     at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
  29.     ... 8 more

总结

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

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

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

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

  1. mysql> show processlist;
  2. +----+------+----------------------+------+---------+------+-------+------------------+
  3. | Id | User | Host                 | db   | Command | Time | State | Info             |
  4. +----+------+----------------------+------+---------+------+-------+------------------+
  5. |  2 | root | localhost            | NULL | Query   |    0 | init  | show processlist |
  6. |  6 | repl | 192.168.244.20:44641 | NULL | Sleep   | 1154 |       | NULL             |
  7. +----+------+----------------------+------+---------+------+-------+------------------+
  8. 2 rows in set (0.03 sec)

发表评论

表情：
评论列表 （有 0 条评论，217人围观）

还没有评论，来说两句吧...

相关阅读