mysql hikari连接池异常处理（com.zaxxer.hikari.pool.ProxyConnection）

异常信息如下：com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 603,312 milliseconds ago. 

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 603,312 milliseconds ago. The last packet sent successfully to the server was 603,312 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.### The error may exist in com/dbappsecurity/base/mapper/SysConfigMapper.java (best guess)### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: DELETE FROM tb_sys_config     WHERE (code = ?)### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 603,312 milliseconds ago. The last packet sent successfully to the server was 603,312 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.; The last packet successfully received from the server was 603,312 milliseconds ago. The last packet sent successfully to the server was 603,312 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.; nested exception is com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 603,312 milliseconds ago. The last packet sent successfully to the server was 603,312 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.2023-03-29 11:00:57 | [89-exec-10] ERROR c.d.base.config.security.BaseExceptionHandler : Catch Exception,message=JDBC commit failed; nested exception is java.sql.SQLException: Connection is closedorg.springframework.transaction.TransactionSystemException: JDBC commit failed; nested exception is java.sql.SQLException: Connection is closed

问题提示的原因：

1. sql的问题
2. 让我们设置数据路连接超时时间
3. 设置数据库的超时时间参数

实际定位下来，都不是，因为这sql之前执行过；

解决过程如下（数据库命令可以在Navicat执行，也可以直接在服务器上执行）：

show variables like '%max_connection%'; -- 查看数据库设置的链接数，默认最大是1000,

show global status like 'Thread%'; -- 查看当前数据库已经连的数量，并且各连状态

综合1和2，得出实际连接是不超标，理论上能够获取到数据库连接的；

继续定位

show PROCESSLIST; -- 查询数据库中哪些线程正在执行，执行状态是什么，如果存在lock时，执行的内容是什么

-- 此时显示，在调用数据库操作时，存在等待锁的情况。truncate tb_sys_schudual_record 是 lock状态

-- 这导致该事物里面其他的sql操作被阻塞。

为什么是lock状态？

-- 代码流程存在问题，在执行truncate tb_sys_schudual_record 之前，方法里对该表进行了其他操作。由于该方法被事务所管理，并且该方法还未执行完，事务不会被提交，此时在去truncate该表，就会被锁住。

-- 最终导致该错误的出现。

总结：

代码问题：操作同一张表时，应该注意表锁的问题

来源地址：https://blog.csdn.net/m0_55476995/article/details/129839711