spring-boot MySQL 链接 8小时超时

• MySQL连接8小时，没有请求，则断开连接。报错如下：

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 51,000,697 milliseconds ago.  The last packet sent successfully to the server was 51,000,698 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.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3746)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2509)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
    at sun.reflect.GeneratedMethodAccessor22.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy57.executeQuery(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:692)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    ... 42 common frames omitted
Caused by: java.net.SocketException: 断开的管道 (Write failed)
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3728)
    ... 54 common frames omitted

• 解决办法：application.properties中加入以下配置

#验证连接的有效性
master.spring.datasource.test-while-idle=true
#获取连接时候验证，会影响性能
master.spring.datasource.test-on-borrow=true
master.spring.datasource.validation-query=SELECT 1 FROM DUAL
#空闲连接回收的时间间隔，与test-while-idle一起使用，设置5分钟
master.spring.datasource.time-between-eviction-runs-millis=300000
#连接池空闲连接的有效时间 ，设置30分钟
master.spring.datasource.min-evictable-idle-time-millis=1800000

• 参考帖子

spring boot mysql 8小时连接超时