超时机制

原创

igoodful 2022-08-03 06:22:23 博主文章分类：A-数据库之MySQL ©著作权

文章标签 java spring sql 文章分类 运维

©著作权归作者所有：来自51CTO博客作者igoodful的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql连接的超时问题：Communications link failure

org.springframework.dao.RecoverableDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 1,454,140 milliseconds ago.  The last packet sent successfully to the server was 1,454,141Inline
### The error occurred while1, consume_xiaomi_id = null, consume_order_id = null, lock_time = null where consume_order_id=? and status=2last packet successfully received from the server was 1,454,140 milliseconds ago.  The last packet sent successfully to the server was 1,454,141last packet successfully received from the server was 1,454,140 milliseconds ago.  The last packet sent successfully to the server was 1,454,141last packet successfully received from the server was 1,454,140 milliseconds ago.  The last packet sent successfully to the server was 1,454,14198)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
        at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
        at com.sun.proxy.$Proxy13.update(Unknown Source)
        at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
        at com.xiaomi.bss2.dao.MiCouponDao.unfreeze(MiCouponDao.java:23)
        at com.xiaomi.bss2.service.MiCouponService.unfreeze(MiCouponService.java:27)
        at com.xiaomi.bss2.main.Bss2ServiceImpl.cancelOrder(Bss2ServiceImpl.java:63)
        at com.xiaomi.bss2.main.Bss2ServiceImpl$$FastClassBySpringCGLIB$$67f6d028.invoke(<generated>)
        at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
        at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
        at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
        at com.xiaomi.bss2.main.Bss2ServiceImpl$$EnhancerBySpringCGLIB$$ea0735d6.cancelOrder(<generated>)
        at com.xiaomi.bss2.main.Bss2ServiceThriftImpl$1.handle(Bss2ServiceThriftImpl.java:35)
        at com.xiaomi.bss2.main.Bss2ServiceThriftImpl$1.handle(Bss2ServiceThriftImpl.java:32)
        at com.xiaomi.bss2.main.Bss2ServiceThriftImpl.handle(Bss2ServiceThriftImpl.java:87)
        at com.xiaomi.bss2.main.Bss2ServiceThriftImpl.cancelOrder(Bss2ServiceThriftImpl.java:32)
        at sun.reflect.GeneratedMethodAccessor233.invoke(Unknown Source)

原因分析：

MySQL连接时，服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该connection。connections如果空闲超过8小时，Mysql将其断开，
而DBCP连接池并不知道该connection已经失效，如果这时有Client请求connection，DBCP将该失效的Connection提供给Client，将会造成异常。
其中wait_timeout就是负责超时控制的变量，其时间为长度为28800s，就是8个小时，那么就是说MySQL的服务会在操作间隔8小时后断开，需要再次重连。