背景说明

本文使用MySQL 5.7进行分析，系统环境MacBook，讨论验证wait_timeout作用,MySQL默认配置文件/etc/my.cnf

一、MySQL默认配置下的线程连接情况

1、MySQL默认wait_timeout=8hour

show variables like '%wait_timeout%'

image.png

2、查看MySQL线程连接情况

2.1 背景：启动SpringBoot项目（访问DB即可）只配置默认DataSource参数，使用Tomcat8

• 设置参数
  • username
  • password
  • jdbc_url

2.2 查看线程连接情况

show full processlist

image.png

2.3 线程情况说明

• SpringBoot项目默认使用Connection Pool连接池
• 且每个Connection Pool线程数默认10个
• 图中的Time字段表示command字段标记的状态持续的时间,单位秒
  • 如，Sleep 410s
  • 默认情况下，只要time<8h该链接可以一直存在，即MySQL Server不会关闭/丢掉该链接

二、更改MySQL server wait_timeout默认时间

1、使用配置文件方式更新

• 编辑/etc/my.cnf文件在mysqld下面添加wait_timeout行,设为60s(一分钟）
• 重启MySQL

[mysqld]
wait_timeout=60
character-set-server=utf8mb4
default-time-zone = '+8:00'

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

• 验证更改有效(注意此处要加global）

show global  gvariables like '%wait_timeout%'

2、重新启动SpringBoot应用

2.1 不断执行：show full processlist命令

• SpringBoot应用起来后，先不去调用页面上的操作，如登录、刷新等
  • 目的：是让SpringBoot应用不做数据库请求的动作，让MySQL wait 60s之后，再进行页面操作，这样就会报wait_timeout Exception
    image.png

image.png image.png

2.2 结果分析

• 可以看到在Time达到60s后，SpringBoot线程池的线程连接在MySQL Server端已经被MySQL 关闭，即最后一幅图
• 连接分两端，MySQLserver单方面因为超过wait_timeout超时就把连接关了
• 但SpringBoot线程池中连接还保存着，这会导致，Spring应用执行SQL操作时报错
  • 前提：60s内不要进行页面操作
  • 若，60s内页面不断请求数据库，则time字段会不断刷新，刷新为页面请求DB后sleep的时长，这样也可以避免wait_timeout Exception

Caused by: com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 355,390 milliseconds ago.  The last packet sent successfully to the server was 355,390 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.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
        at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
        at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
        at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:562)
        at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:732)
        at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:671)
        at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket(NativeProtocol.java:986)
        at com.mysql.cj.NativeSession.execSQL(NativeSession.java:1157)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:947)
        ... 62 more
Caused by: java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:210)
        at java.net.SocketInputStream.read(SocketInputStream.java:141)
        at com.mysql.cj.protocol.ReadAheadInputStream.fill(ReadAheadInputStream.java:107)
        at com.mysql.cj.protocol.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:150)
        at com.mysql.cj.protocol.ReadAheadInputStream.read(ReadAheadInputStream.java:180)

• 此时还有一个现象，就是SpringBoot应用连接池连接长久处于close_wait状态
  • 我们知道close_wait状态是Tcp被动关闭连接方所处的状态，即A->B,A要和B断绝夫妻关系，A通知了B说我要关闭了，B想了想说同意也告诉A一声（B告诉之后处于close_wait状态），可是B还等着A回个声，但A再也没有回声，B就一直close_wait

admindeMacBook-Pro-7:~ 07$ lsof -iTCP |grep mysql
java      17752 qiankai07  166u  IPv6 0xf3d007512bb170e9      0t0  TCP localhost:52540->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  167u  IPv6 0xf3d007512bb13d29      0t0  TCP localhost:52541->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  168u  IPv6 0xf3d007512bb159e9      0t0  TCP localhost:52542->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  169u  IPv6 0xf3d0075121c60569      0t0  TCP localhost:52543->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  170u  IPv6 0xf3d0075121c610e9      0t0  TCP localhost:52544->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  171u  IPv6 0xf3d0075121c5ee69      0t0  TCP localhost:52545->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  172u  IPv6 0xf3d0075121c60b29      0t0  TCP localhost:52546->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  173u  IPv6 0xf3d007511c1cb8a9      0t0  TCP localhost:52547->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  174u  IPv6 0xf3d007511c1cbe69      0t0  TCP localhost:52548->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  175u  IPv6 0xf3d007511c1cb2e9      0t0  TCP localhost:52549->localhost:mysql (ESTABLISHED)
java      17752 qiankai07  176u  IPv6 0xf3d007511c1cdb29      0t0  TCP localhost:52550->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  177u  IPv6 0xf3d007512232ad29      0t0  TCP localhost:52551->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  178u  IPv6 0xf3d007512232e0e9      0t0  TCP localhost:52552->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  179u  IPv6 0xf3d00751201e5b29      0t0  TCP localhost:52553->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  180u  IPv6 0xf3d007511c391e69      0t0  TCP localhost:52554->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  181u  IPv6 0xf3d007511c3912e9      0t0  TCP localhost:52555->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  182u  IPv6 0xf3d007511c392fa9      0t0  TCP localhost:52556->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  183u  IPv6 0xf3d007511c3918a9      0t0  TCP localhost:52557->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  184u  IPv6 0xf3d00750ff44f9e9      0t0  TCP localhost:52558->localhost:mysql (CLOSE_WAIT)
java      17752 qiankai07  185u  IPv6 0xf3d00750ff450569      0t0  TCP localhost:52559->localhost:mysql (CLOSE_WAIT)

三、总结

就算以MySQL默认 wait_timeout值（8hour)有时候也会报wait_timeout异常** 解决wait_timeout异常

1. 可以将wait_timeout设为更高值

• Linux系统最高1年

2. 使用阿里德鲁伊Druid作为数据库连接池

3. 使用spring.datasource.testWhileIdle=true字段

• 默认会在3、4s间隔刷新sleep线程，是sleep 线程Time字段最长为3、4 秒就不会超过wait_timeout的60s了
  • 页面不进行操作也会刷新sleep时长

image.png

流程总结

• client（SpringBoot 连接池）连接处于空闲状态（（没有执行select等操作））的时长超过了MySQL设置的wait_timeout时间，MySQL单方面就把连接给关闭了，连接池再用这个关闭的连接做事情就报错
  • MySQL关闭连接后，Java连接池的连接都处于close_wait状态,而不是一开始的establish状态，本质是SpringBoot是用这些close_wait状态做事的所以就报错了
  • 从另一方面看，MySQL的wait_timeout若设为一个较大值，Java连接池的线程一直处于establish状态，随时执行操作而不报错**

testWhileIdel会保持连接新鲜程度

四、附-常用命令

#Mac MySQL重启
#启动MySQL服务
sudo /usr/local/[MySQL](http://lib.csdn.net/base/14 "undefined")/support-files/mysql.server start
#停止MySQL服务
sudo /usr/local/mysql/support-files/mysql.server stop
#重启MySQL服务
sudo /usr/local/mysql/support-files/mysql.server restart

#Mac取代netstat方式查看tcp,可以用grep做很多事
lsof -iTCP |grep mysql