澳门在线威尼斯官方 > 电脑数据库 > 【威尼斯澳门在线】MySQL中interactive_timeout和wait

原标题:【威尼斯澳门在线】MySQL中interactive_timeout和wait

浏览次数:134 时间:2019-10-06

在用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          |
+---------------------+----------------+
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:  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          |
+---------------------+----------------+
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','wa
it_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:  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。

  1. 剖断三个接连的空闲时间,可经过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. 

2. 

3. 

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:【威尼斯澳门在线】MySQL中interactive_timeout和wait

关键词:

上一篇:威尼斯澳门在线:配置SQL Server on Linux(2)

下一篇:pt-mext