com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure MYSQL8小时问题

IT黑名单 2017-12-12 16:43:25

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



转载请注明来源【IT黑名单

本文链接:http://blog.itblacklist.cn/20171212/8464.html

© Copyright 2016 IT黑名单 Inc.All Rights Reserved. 豫ICP备15018592号-2