com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

概述：mysql版本5.6，错误信息如下：

 

### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 690,750,831 milliseconds ago.  The last packet sent successfully to the server was 690,750,833 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.

; SQL []; The last packet successfully received from the server was 690,750,831 milliseconds ago.  The last packet sent successfully to the server was 690,750,833 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.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 690,750,831 milliseconds ago.  The last packet sent successfully to the server was 690,750,833 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.

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:371)

at com.sun.proxy.$Proxy30.insert(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:240)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)

at com.sun.proxy.$Proxy42.add(Unknown Source)

at com.atme8.order.serviceImpl.OrderProviderService.add(OrderProviderService.java:83)

at sun.reflect.GeneratedMethodAccessor131.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

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:281)

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.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)

at com.sun.proxy.$Proxy47.add(Unknown Source)

at com.alibaba.dubbo.common.bytecode.Wrapper3.invokeMethod(Wrapper3.java)

at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46)

at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72)

at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53)

at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:65)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:70)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:132)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38)

at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91)

at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:113)

at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84)

at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170)

at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52)

at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)

at java.lang.Thread.run(Thread.java:745)

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 690,750,831 milliseconds ago.  The last packet sent successfully to the server was 690,750,833 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.

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

at java.lang.reflect.Constructor.newInstance(Constructor.java:526)

at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)

at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3291)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1938)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)

at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)

at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086)

at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1365)

at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:44)

at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:69)

at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)

at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)

at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)

at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)

at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)

at sun.reflect.GeneratedMethodAccessor88.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:606)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:358)

... 46 common frames omitted

Caused by: java.net.SocketException: Broken pipe

at java.net.SocketOutputStream.socketWrite0(Native Method)

at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)

at java.net.SocketOutputStream.write(SocketOutputStream.java:159)

at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)

at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)

at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3272)

... 62 common frames omitted

 

 

根据日志信息，说明链接时间比wait_timeout参数设置的时间要长，那么需要知道现在数据库设置的改参数是多长时间：

mysql﹥ SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

| Variable_name | Value |

+---------------+---------+

| wait_timeout | 28800 |  

该值是28800秒，即8小时。

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接，而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致上面的报错。

 

解决方法：

1、修改/etc/my.cnf： 

[mysqld]  

wait_timeout=31536000  

interactive_timeout=31536000  

将过期时间修改为1年。 

 

2、在连接URL上添加参数：&autoReconnect=true&failOverReadOnly=false 

我使用的是第一种方式，如果不会改修改本篇日志说明相应的配置已经起作用。

以下是我改完的数据库配置：

spring.datasource.url=jdbc:mysql://127.0..1:3306/sevenspay_dev?characterEncoding=utf-8&useUnicode=true&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.max-idle=10
spring.datasource.max-wait=60000
spring.datasource.min-idle=5
spring.datasource.max-active=20
spring.datasource.initial-size=5
spring.datasource.validation-query=SELECT 1
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=18800
spring.datasource.pool-prepared-statements=true
spring.datasource.max-open-prepared-statements=20

 

3、MySQL5可以考虑用druid连接池或者proxool连接池。

 

附上spring datasource的所有参数说明：

https://segmentfault.com/a/1190000004316491#articleHeader0