mysql连接超时的属性设置

mysql连接超时的属性设置

2022-10-26 11:09:54.128 [http-nio-6788-exec-5] ERROR o.s.t.i.TransactionAspectSupport#completeTransactionAfterThrowing [line:525] - Application exception overridden by rollback exceptionorg.springframework.dao.RecoverableDataAccessException: ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 497,470,876 milliseconds ago. The last packet sent successfully to the server was 497,470,876 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.

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 497,470,876 milliseconds ago. The last packet sent successfully to the server was 497,470,876 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. Caused by: java.net.SocketException: Broken pipe (Write failed)

大概意思是当前的connection所进行过的最新请求是在52,587秒之前，这个时间是大于服务所配置的wait_timeout时间的。

原因分析： MySQL连接时，服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection。connections如果空闲超过8小时，Mysql将其断开，而DBCP连接池并不知道该connection已经失效，如果这时有Client请求connection，DBCP将该失效的Connection提供给Client，将会造成异常。

打开MySQL的控制台，运行:SHOW VARIABLES LIKE '%timeout%'; 查看和连接时间有关的MySQL系统变量。

##修改调整配置：True 修改为 true，min-idle 修改为 0
spring.datasource.max-idle = 30
spring.datasource.min = 0
spring.datasource.min-idle = 0
spring.datasource.remove-abandoned = true
spring.datasource.remove-abandoned-timeout = 180
spring.datasource.test-on-borrow = true
spring.datasource.test-while-idle = true

spring.datasource.tomcat.max-idle = 30
spring.datasource.tomcat.min = 0
spring.datasource.tomcat.min-idle = 0
spring.datasource.tomcat.remove-abandoned = true
spring.datasource.tomcat.remove-abandoned-timeout = 180
spring.datasource.tomcat.test-on-borrow = true
spring.datasource.tomcat.test-while-idle = true

配置属性后，从上一次启动服务到测试，间隔几天后，接口操作数据库可以正常访问。