异常：com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Love The Way You Lie 2022-05-30 04:49 204阅读 0赞

### 异常描述 ###

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    
    The last packet successfully received from the server was 340,065 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_45] at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[?:1.8.0_45] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_45] at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[?:1.8.0_45] at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:988) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3552) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3452) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3893) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962) ~[mysql-connector-java-5.1.39.jar!/:5.1.39] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2714) ~[druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.wall.WallFilter.preparedStatement_executeQuery(WallFilter.java:621) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeQuery(FilterEventAdapter.java:465) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeQuery(FilterChainImpl.java:2711) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.executeQuery(PreparedStatementProxyImpl.java:145) [druid-1.0.23.jar!/:1.0.23] at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeQuery(DruidPooledPreparedStatement.java:227) [druid-1.0.23.jar!/:1.0.23] at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.getResultSet(Loader.java:2117) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.doQuery(Loader.java:919) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.doList(Loader.java:2617) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.doList(Loader.java:2600) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.Loader.list(Loader.java:2424) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) [hibernate-core-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) [hibernate-entitymanager-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:529) [hibernate-entitymanager-5.0.9.Final.jar!/:5.0.9.Final] at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:54) [hibernate-entitymanager-5.0.9.Final.jar!/:5.0.9.Final] at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:206) [spring-data-jpa-1.10.2.RELEASE.jar!/:?] at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78) [spring-data-jpa-1.10.2.RELEASE.jar!/:?] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:102) [spring-data-jpa-1.10.2.RELEASE.jar!/:?] at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:92) [spring-data-jpa-1.10.2.RELEASE.jar!/:?] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482) [spring-data-commons-1.12.2.RELEASE.jar!/:?] at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) [spring-data-commons-1.12.2.RELEASE.jar!/:?] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) [spring-data-commons-1.12.2.RELEASE.jar!/:?] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) [spring-tx-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:280) [spring-tx-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) [spring-tx-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) [spring-tx-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) [spring-data-jpa-1.10.2.RELEASE.jar!/:?] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) [spring-aop-4.3.2.RELEASE.jar!/:4.3.2.RELEASE] at com.sun.proxy.$Proxy145.getByAccessToken(Unknown Source) [?:?]

### 异常分析 ###

mysql数据库在使用连接池时，MySQL服务器默认的“wait\_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接，而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错，重启后OK（重启重新获取连接）。

### 异常解决 ###

应用访问频率高的话不会暴露出该问题，避免该问题可以提升访问数据库频率（保证28800s内有新的数据库请求——这个一般说了等于白说）或者增加等待时长：

//修改为1天
    wait_timeout=86400
    interactive_timeout=86400