MySQL 的连接时长控制--interactive_timeout和wait_timeout

喜欢ヅ旅行 2023-06-07 05:59 28阅读 0赞

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

ERROR 2013 (HY000): Lost connection to MySQL server during query

ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...

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

那么，连接建立后，连接的时长是如何确定的呢？

在MySQL中，这个与两个参数`interactive_timeout`和`wait_timeout`的设置有关。

注:以下说明基于MySQL 5.7.

# 1.interactive\_timeout和wait\_timeout的定义 #

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

**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参数，定义了对于交互式连接，服务端等待数据的最大时间。如果超过这个时间，服务端仍然没有收到数据，则会关闭连接。

所谓交互式client，是指调用mysql\_real\_connect()函数建立连接时，设置了CLIENT\_INTERACTIVE选项。比较常用的就是命令行终端。

查看interactive\_timeout的值：

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

**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参数，定义对于非交互式连接，服务端等待数据的最长时间。如果超过这个时间，服务端仍然没有收到数据，则会关闭连接。

在连接线程启动的时候，根据连接的类型，决定会话级的wait\_timeout的值是初始化为全局的wait\_timeout，还是全局的interactive\_timeout。即如果是交互式连接，会话变量wait\_timeout初始化为全局的interactive\_timeout，否则，初始化为全局的wait\_timeout。

查看wait\_timeout的值：

默认同样是28800s,即8小时。

根据上述定义，两者的区别显而易见：interactive\_timeout针对交互式连接，wait\_timeout针对非交互式连接。所谓的交互式连接，即在mysql\_real\_connect()函数中使用了CLIENT\_INTERACTIVE选项。

说得直白一点，通过mysql命令行终端连接数据库是交互式连接，通过jdbc等连接数据库是非交互式连接。

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

*  控制连接最大空闲时长的是哪个参数。
 *  会话变量wait\_timeout的继承问题

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

先给出答案：wait\_timeout

接下来进行验证。

## 2.1 只修改会话的wait\_timeout参数 ##

查看当前会话的wait\_timeout和interactive\_timeout。

设置当前会话的wait\_timeout为10s

mysql> set session wait_timeout=10;
    Query OK, 0 rows affected (0.00 sec)

再次查看

等待10s，再次查看

mysql> show session  variables where Variable_name in ('interactive_timeout', 'wait_timeout');
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    8
    Current database: *** NONE ***
    
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | interactive_timeout | 28800 |
    | wait_timeout        | 28800 |
    +---------------------+-------+
    2 rows in set (0.01 sec)

可以看到，等待10s后再执行操作，原来的连接已经断开，并重新建立连接。

## 2.2 只修改会话的interactive\_timeout参数 ##

首先查看当前会话的interactive\_timeout和wait\_timeout.

接着，设置当前会话的interactive\_timeout为10s

mysql> set session interactive_timeout=10;
    Query OK, 0 rows affected (0.00 sec)

再次查看

等待10s，再次查看

可以看到，即使等待10后，连接是正常的。所以，设置interactive\_timeout，对连接的时长没有影响。

# 3.会话变量wait\_timeout的继承问题 #

上面已经提到，如果是交互式连接，则继承自全局变量interactive\_timeout的值，如果是非交互式连接，则继承自全局变量wait\_timeout的值。

下面进行验证。

## 3.1 只修改全局变量interactive\_timeout的值 ##

首先查看全局的interactive\_timeout和wait\_timeout。

接着，设置全局的interactive\_timeout为10s。

mysql> set global INTERACTIVE_TIMEOUT=10;
    Query OK, 0 rows affected (0.00 sec)

再次查看

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

发现，WAIT\_TIMEOUT的值已经变为10了。

等待10s后，再次查看，会发现原来的连接已经断开，连接的时长设置已经生效。

mysql> show session  variables where Variable_name in ('interactive_timeout', 'wait_timeout');
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    70
    Current database: *** NONE ***
    
    +---------------------+-------+
    | Variable_name       | Value |
    +---------------------+-------+
    | interactive_timeout | 10    |
    | wait_timeout        | 10    |
    +---------------------+-------+
    2 rows in set (0.01 sec)

但通过非终端测试，wait\_timeout的值依旧是28800：

package main
    
    import (
            "database/sql"
            "log"
    
            _ "github.com/go-sql-driver/mysql"
    )
    
    var DB *sql.DB
    var dataBase = "root:Aa123456@tcp(127.0.0.1:3306)/?loc=Local&parseTime=true"
    
    func mysqlInit() {
            var err error
            DB, err = sql.Open("mysql", dataBase)
            if err != nil {
                    log.Fatalln("open db fail:", err)
            }
    
            DB.SetMaxOpenConns(1)
    
            err = DB.Ping()
            if err != nil {
                    log.Fatalln("ping db fail:", err)
            }
    }
    
    func main() {
            mysqlInit()
            execSql()
    }
    
    func execSql() {
            var variableName string
            var value int
            sql := "show session  variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
            rows, err := DB.Query(sql)
            if err != nil {
                    log.Println("query failed:", err)
                    return
            }
    
            defer rows.Close()
    
            for rows.Next() {
                    err = rows.Scan(&variableName, &value)
                    if err != nil {
                            log.Println("rows.Scan failed:", err)
                            return
                    }
    
                    log.Println("variable_name:", variableName, ", value:", value)
            }
    }

output:

2019/10/13 17:11:22 variable_name: interactive_timeout , value: 10
    2019/10/13 17:11:22 variable_name: wait_timeout , value: 28800

结果输出如下

INTERACTIVE_TIMEOUT:  10
    WAIT_TIMEOUT:  28800

## 3.2 只修改全局变量wait\_timeout的值 ##

首先查看全局的interactive\_timeout和wait\_timeout。

接着，将全局的WAIT\_TIMEOUT设置为20s。

mysql> set global WAIT_TIMEOUT=20;
    Query OK, 0 rows affected (0.07 sec)

再次查看

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

WAIT\_TIMEOUT的值依旧是28800.

查看非终端的代码执行的结果：

func execSql() {
            var variableName string
            var value int
            sql := "show session  variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
            rows, err := DB.Query(sql)
            if err != nil {
                    log.Println("query failed:", err)
                    return
            }
    
            defer rows.Close()
    
            for rows.Next() {
                    err = rows.Scan(&variableName, &value)
                    if err != nil {
                            log.Println("rows.Scan failed:", err)
                            return
                    }
    
                    log.Println("variable_name:", variableName, ", value:", value)
            }
    }

output:

2019/10/13 17:23:10 variable_name: interactive_timeout , value: 28800
    2019/10/13 17:23:10 variable_name: wait_timeout , value: 20

修改程序，执行sql语句后，等待25s后，再次执行sql语句，查看执行情况。

func main() {
            mysqlInit()
            for {
                    execSql()
                    time.Sleep(25*time.Second)
            }
    }
    
    func execSql() {
            var variableName string
            var value int
            sql := "show session  variables where Variable_name in ('interactive_timeout', 'wait_timeout')"
            rows, err := DB.Query(sql)
            if err != nil {
                    log.Println("query failed:", err)
                    return
            }
    
            defer rows.Close()
    
            for rows.Next() {
                    err = rows.Scan(&variableName, &value)
                    if err != nil {
                            log.Println("rows.Scan failed:", err)
                            return
                    }
    
                    log.Println("variable_name:", variableName, ", value:", value)
            }
    }

output:

2019/10/13 17:26:46 variable_name: interactive_timeout , value: 28800
    2019/10/13 17:26:46 variable_name: wait_timeout , value: 20
    [mysql] 2019/10/13 17:27:11 packets.go:36: unexpected EOF
    [mysql] 2019/10/13 17:27:11 packets.go:141: write tcp 127.0.0.1:53878->127.0.0.1:3306: write: broken pipe
    2019/10/13 17:27:11 variable_name: interactive_timeout , value: 28800
    2019/10/13 17:27:11 variable_name: wait_timeout , value: 20

可以看到，等待25s后，再次执行sql，此时连接已经断开。  
底层又重新建立连接。

# 4.总结 #

*  控制连接最大空闲时长的wait\_timeout参数。
 *  关于wait\_timeout的继承
    
     *  对于非交互式连接，类似于jdbc连接，wait\_timeout的值继承自全局变量wait\_timeout。
     *  对于交互式连接，类似于mysql命令行终端，wait\_timeout的值继承全局变量interactive\_timeout。
 *  判断一个连接的空闲时间，可通过show processlist输出中Sleep状态的时间

# 5.参考 #

https://www.cnblogs.com/ivictor/p/5979731.html

http://www.cnblogs.com/cenalulu/archive/2012/06/20/2554863.html

http://www.cnblogs.com/Alight/p/4118515.html

http://ronaldbradford.com/blog/sqlstatehy000-general-error-2006-mysql-server-has-gone-away-2013-01-02/