time=2019-01-02T09:57:53.827+0800`appName=uc`hostName=docker-20`logLevel=ERROR`msg=Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 305,082,702 milliseconds ago. The last packet sent successfully to the server was 305,082,702 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 involve com.yushi.wogame.uc.mapper.UserAppTokenMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE user_app_token SET token=?, ctime=?, utime=? WHERE uid=?
### Cause: com.mysql.cj.jdbc.exceptions.CommunicationsException: The last packet successfully received from the server was 305,082,702 milliseconds ago. The last packet sent successfully to the server was 305,082,702 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 305,082,702 milliseconds ago. The last packet sent successfully to the server was 305,082,702 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 305,082,702 milliseconds ago. The last packet sent successfully to the server was 305,082,702 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.] with root cause java.net.SocketException: 断开的管道 (Write failed)
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.cj.protocol.a.SimplePacketSender.send(SimplePacketSender.java:55)
at com.mysql.cj.protocol.a.TimeTrackingPacketSender.send(TimeTrackingPacketSender.java:50)
at com.mysql.cj.protocol.a.NativeProtocol.send(NativeProtocol.java:577)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:647)
at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:132)
at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:321)
at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:1075)
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1416)
at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1408)
at com.mysql.cj.jdbc.ClientPreparedStatement.checkReadOnlySafeStatement(ClientPreparedStatement.java:328)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:338)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
at io.shardingjdbc.core.jdbc.core.statement.MasterSlavePreparedStatement.execute(MasterSlavePreparedStatement.java:110)
at sun.reflect.GeneratedMethodAccessor119.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy198.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
at sun.reflect.GeneratedMethodAccessor200.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
at com.sun.proxy.$Proxy196.update(Unknown Source)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
at sun.reflect.GeneratedMethodAccessor205.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
at com.sun.proxy.$Proxy132.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:73)
at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
at com.sun.proxy.$Proxy148.updateById(Unknown Source)
at com.yushi.wogame.uc.service.uc.impl.UserAppTokenServiceImpl.saveOrUpdateAppToken(UserAppTokenServiceImpl.java:36)
at com.yushi.wogame.uc.service.uc.impl.UserAppTokenServiceImpl$$FastClassBySpringCGLIB$$d87747ec.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:684)
at com.yushi.wogame.uc.service.uc.impl.UserAppTokenServiceImpl$$EnhancerBySpringCGLIB$$46697490.saveOrUpdateAppToken(<generated>)
at com.yushi.wogame.uc.service.uc.impl.SessionManagerServiceImpl.login(SessionManagerServiceImpl.java:233)
at com.yushi.wogame.uc.controller.UserSessionController.registerViaOauthAndLogin(UserSessionController.java:105)
at sun.reflect.GeneratedMethodAccessor211.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:215)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:142)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:800)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:998)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:901)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:875)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.web.trace.servlet.HttpTraceFilter.doFilterInternal(HttpTraceFilter.java:90)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:154)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.filterAndRecordMetrics(WebMvcMetricsFilter.java:122)
at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:107)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:770)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1415)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:748)
出现错误的原因：数据库连接的属性 testOnBorrow设置为false(默认设置为false) testOnBorrow=false由于不检测池里连接的可用性，于是假如连接池中的连接被数据库关闭了，应用通过连接池getConnection时，都可能获取到这些不可用的连接，且这些连接如果不被其他线程回收的话，它们不会被连接池被废除，也不会重新被创建，占用了连接池的名额，项目本身作为服务端，数据库链接被关闭，客户端调用服务端就会出现大量的timeout，客户端设置了超时时间，然而主动断开，服务端必然出现close_wait。
解决办法：
在mysql 5版本之前，可以使用autoReconnect=true。
不建议去修改mysql数据库的wait_timeout配置项： show global variables like 'wait_timeout';
最后贴出我在测试环境下的数据库配置，仅供参考，其中设置test-on-borrow = true 比较消耗性能，有待商榷。 至于使用参数 和 结合使用，还有待测试验证。
# mysql 数据源配置
datasource:
default-auto-commit: true
auto-commit: true
maximum-pool-size: 100
max-idle: 10
max-wait: 10000
min-idle: 5
initial-size: 5
validation-query: SELECT 1
# 默认是false
test-on-borrow: true
test-while-idle: true
# 在空闲时 每个1小时 访问一下数据库，避免连接池中的连接因超时而失效
time-between-eviction-runs-millis: 3600000
minEvictableIdleTimeMillis: 300000
连接url的配置如下
url: jdbc:mysql://{ip}:{port}/{dbname}?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
