对wait_timeout引发的问题的分析及解决办法

美好的清晨遇到一到让人增长知识的错误！

1、错误现象

MySQL连接错误，具体报错信息如下：

The last packet sent successfully to the server was 47,795,922 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

所使用的MySQL的版本为：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+

2、发生场景

错误发生的场景是这样的， 使用MySQL8作为kettle的资源库，在经过一夜没有操作kettle后，第二天一大早使用kettle打开之前建好的作业时，报了上面的错误。
所报的错误信息是，上次成功发往MySQL服务的数据包发生在47795922毫秒之前，而该时间值超过了MySQL服务所配置的wait_timeout的时长。

3、解决思路

自然而然想到的解决办法就是增加wait_timeout的时间，wait_timeout参数有两种作用范围：session和global，鉴于在交互式方式下修改这两种范围下wait_timeout在重启MySQL server后都会失效，这里再介绍一种修改MySQL配置文件（my.conf）的方式，所以下面共提供了三种修改wait_timeout值的方式。

4、具体解决方式

以下操作在CentOS7.5下进行。

4.1、方式一：设置session范围的wait_timeout

登录进MySQL server，使用下面命令查看当前session的wait_timeout值：

mysql> show session variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

可以看到wait_timeout的值默认为28800，单位为s(秒)，换算成小时为小时，如果不在上面的命令中添加session，默认查询的也是当前session范围内的wait_timeout值。
使用下面的命令设置当前session的wait_timeout值，如果不加session，默认值设置的也是当前session范围内的wait_timeout值，并再次查看其值：

mysql> show session variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 100   |
+---------------+-------+
1 row in set (0.00 sec)

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

mysql> show session variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 100   |
+---------------+-------+
1 row in set (0.00 sec)

设置当前session范围内的wait_timeout值，当退出当前session时所设置的值会丢失，再次登录MySQL server会发现wait_timeout值已变成默认值——28800 ，要想退出session而不丢失更改，可以考虑设置global范围的wait_timeout值，即下面的方式二。

4.2、方式二：设置global范围的wait_timeout

登录进MySQL server，使用下面命令查看global范围的wait_timeout值

mysql> show global variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

使用下面的命令设置global的wait_timeout值，并再次查看其值：

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

mysql> show global variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 300   |
+---------------+-------+
1 row in set (0.00 sec)

4.2.1、思考

介绍到这里有一个问题值得思考，在设置了global范围的wait_timeout后，session范围的wait_timeout值是否会发生变化呢？
经过查看发现session范围的wait_timeout值并没有随着global范围的wait_timeout值的改变而改变。其实在MySQL官网的参考手册上对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指的是，在非交互连接方式下，MySQL server等到一个活动的时间，如果超出这个时间仍没有等到连接活动，MySQL server就会断开连接。在连接启动时，session范围的wait_timeout的值是根据客户端类型来确定的，要么初始化于global wait_timeout要么初始化于global interactive_timeout。那什么是interactive_timeout呢，在MySQL官网的参考手册上同样也有对它的解释：

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指的是，在交互式连接方式下，MySQL server等待一个活动的时间，如果超出这个时间仍没有等到连接活动，MySQL server就会断开连接。
了解了session范围的wait_timeout值的来源，就能根据需要设定它的值了。在交互式连接中，我们先设置global范围的interactive_timeout值，然后重新建立连接，再次查询session范围的wait_timeout值是否发生改变，验证过程如下：

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

mysql> exit
Bye
[xiaoliu@laptop-8a061sac ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 300   |
+---------------+-------+
1 row in set (0.01 sec)

可以看出，在交互模式下，session范围内的wait_timeout值已变为设置的global范围的interactive_timeout的值。
设置了global范围的wait_timeout之后，当服务器重启或者MySQL server重启后，上面的方式设置的值都会丢失，如何才能不丢失呢，需要把修改写进my.conf，即下面的方式三。

4.3、方式三：修改配置文件my.conf

鉴于interactive_timeout和wait_timeout影响不同交互方式下的等待空闲时间，所以我们把interactive_timeout和wait_timeout都写进my.conf，my.conf默认在/etc下，它们的值可以根据需要自行设定，内容如下：

[mysqld]
interactive_timeout=300
wait_timeout=300

之后重启MySQL server，systemctl restart mysqld，使用下面的方式再次查看session范围的wait_timeout、global范围的wait_timeout、session范围的interactive_timeout和global范围的interactive_timeout的值，均为在文件/etc/my.cnf中设置的值，如下：

mysql> select @@session.wait_timeout, @@global.wait_timeout, @@session.interactive_timeout, @@global.wait_timeout;
+------------------------+-----------------------+-------------------------------+-----------------------+
| @@session.wait_timeout | @@global.wait_timeout | @@session.interactive_timeout | @@global.wait_timeout |
+------------------------+-----------------------+-------------------------------+-----------------------+
|                    300 |                   300 |                           300 |                   300 |
+------------------------+-----------------------+-------------------------------+-----------------------+

5、总结

了解了wait_timeout的含义，以及为什么会出现本文开头提到的报错信息，列举了三种修改wait_timeout的方式。