线上真实案例之执行一段逻辑后报错Communications link failure

本文链接：https://blog.csdn.net/weixin_36152775/article/details/137997278

1.问题发现

在开发某个项目的一个定时任务计算经销商返利的功能时，有一个返利监测的调度，如果某一天返利计算调度失败了，需要重新计算，这个监测的调度就会重新计算某天的数据。

在UAT测试通过，发布生产后，发现过几天就报错了，报错日志如下：

Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6d2b4a] from current transaction
==>  Preparing: delete from store_rebate_calculate_log where day_str = ?
==> Parameters: 20240418(String)
2024-04-20 11:31:06.092 WARN  [http-nio-8184-exec-7]com.zaxxer.hikari.pool.ProxyConnection.checkException:156 -HikariPool-1 - Connection com.mysql.cj.jdbc.ConnectionImpl@1a8443 marked as broken because of SQLSTATE(08S01), ErrorCode(0)
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 167,833 milliseconds ago. The last packet sent successfully to the server was 168,881 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1414)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1399)
	at com.mysql.cj.jdbc.ClientPreparedStatement.checkReadOnlySafeStatement(ClientPreparedStatement.java:307)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:317)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy269.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
	at com.sun.proxy.$Proxy267.update(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
	at com.sun.proxy.$Proxy266.update(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
	at com.sun.proxy.$Proxy266.update(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.delete(DefaultSqlSession.java:209)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	at com.sun.proxy.$Proxy173.delete(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:304)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:69)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy185.deleteByDay(Unknown Source)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl.calculateRebateDetail(StoreRebateCalculateServiceImpl.java:108)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl.monitorRebateCalculate(StoreRebateCalculateServiceImpl.java:256)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl$$FastClassBySpringCGLIB$$446442cd.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
	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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl$$EnhancerBySpringCGLIB$$6f0ec506.monitorRebateCalculate(<generated>)
	at com.au.sa.storeRebate.rebateDetail.controller.StoreRebateCalculateController.calculateMonitorSync(StoreRebateCalculateController.java:36)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	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:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
	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 com.au.sa.sso.filter.BaseFilter.doFilter(BaseFilter.java:115)
	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.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
	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.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	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.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108)
	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.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	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:197)
	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.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
	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:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	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:745)
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 167,833 milliseconds ago. The last packet sent successfully to the server was 168,881 milliseconds ago.
	at sun.reflect.GeneratedConstructorAccessor124.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:539)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:703)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:642)
	at com.mysql.cj.protocol.a.NativeProtocol.sendCommand(NativeProtocol.java:128)
	at com.mysql.cj.NativeSession.sendCommand(NativeSession.java:311)
	at com.mysql.cj.NativeSession.queryServerVariable(NativeSession.java:1008)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:1406)
	... 132 common frames omitted
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:52)
	at com.mysql.cj.protocol.a.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:41)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:54)
	at com.mysql.cj.protocol.a.MultiPacketReader.readHeader(MultiPacketReader.java:44)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:533)
	... 138 common frames omitted
    
2024-04-20 11:31:06.133 INFO  [http-nio-8184-exec-7]o.s.beans.factory.xml.XmlBeanDefinitionReader.loadBeanDefinitions:317 -Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2024-04-20 11:31:06.314 INFO  [http-nio-8184-exec-7]org.springframework.jdbc.support.SQLErrorCodesFactory.<init>:127 -SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
2024-04-20 11:31:06.338 WARN  [http-nio-8184-exec-7]org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes:218 -Error while extracting database name - falling back to empty error codes
org.springframework.jdbc.support.MetaDataAccessException: Error while extracting DatabaseMetaData; nested exception is java.sql.SQLException: Connection is closed
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:342)
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:366)
	at org.springframework.jdbc.support.SQLErrorCodesFactory.getErrorCodes(SQLErrorCodesFactory.java:212)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.setDataSource(SQLErrorCodeSQLExceptionTranslator.java:134)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.<init>(SQLErrorCodeSQLExceptionTranslator.java:97)
	at org.mybatis.spring.MyBatisExceptionTranslator.lambda$new$0(MyBatisExceptionTranslator.java:55)
	at org.mybatis.spring.MyBatisExceptionTranslator$$Lambda$142/11311981.get(Unknown Source)
	at org.mybatis.spring.MyBatisExceptionTranslator.initExceptionTranslator(MyBatisExceptionTranslator.java:106)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy173.delete(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:304)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:69)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
	at com.sun.proxy.$Proxy185.deleteByDay(Unknown Source)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl.calculateRebateDetail(StoreRebateCalculateServiceImpl.java:108)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl.monitorRebateCalculate(StoreRebateCalculateServiceImpl.java:256)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl$$FastClassBySpringCGLIB$$446442cd.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
	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.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	at com.au.sa.storeRebate.storeRebateCalculate.service.impl.StoreRebateCalculateServiceImpl$$EnhancerBySpringCGLIB$$6f0ec506.monitorRebateCalculate(<generated>)
	at com.au.sa.storeRebate.rebateDetail.controller.StoreRebateCalculateController.calculateMonitorSync(StoreRebateCalculateController.java:36)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	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:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
	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 com.au.sa.sso.filter.BaseFilter.doFilter(BaseFilter.java:115)
	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.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:110)
	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.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
	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.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:108)
	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.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81)
	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:197)
	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.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:106)
	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:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1459)
	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:745)
Caused by: java.sql.SQLException: Connection is closed
	at com.zaxxer.hikari.pool.ProxyConnection$ClosedConnection$1.invoke(ProxyConnection.java:469)
	at com.sun.proxy.$Proxy268.getMetaData(Unknown Source)
	at com.zaxxer.hikari.pool.HikariProxyConnection.getMetaData(HikariProxyConnection.java)
	at org.springframework.jdbc.support.JdbcUtils.extractDatabaseMetaData(JdbcUtils.java:331)
	... 99 common frames omitted

其中核心的错误就以下几个：

com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 167,833 milliseconds ago. The last packet sent successfully to the server was 168,881 milliseconds ago.

Caused by: java.sql.SQLException: Connection is closed

简单来说，就是数据库连接突然断开了或者失效了。

代码的主要逻辑如下。

这个是计算返利的方法

public String calculateRebateDetail(String dateStr) {
        boolean success = true;
        String lockKey = "STORE_REBATE_CALCULATE_LOCK";
        JSONObject result = new JSONObject();
        int count = 0;
        int totalNum = 0;
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();

        try {
            Long val = jedisCluster.setnx(lockKey, SaafDateUtils.convertDateToString(new Date()));
            //默认一个小时自动失效
            jedisCluster.expire(lockKey, 3600);
            if (val == 0) {
                success = false;
                return SToolUtils.convertResultJSONObj(SUCCESS_STATUS, "统计任务正在执行", 0, null).toString();
            }
            if (StringUtils.isEmpty(dateStr)) {
                //默认取前一天
                dateStr = DateUtil.date2Str(DateUtil.addDay(new Date(), -1), "yyyyMMdd");
            }
            //1.删除前一天或者日期参数（yyyyMMdd）的门店的计算追踪表和返利明细表的数据
            storeRebateCalculateLogDao.deleteByDay(dateStr);//报错的地方
            storeRebateDetailDao.deleteByDay(dateStr);

            //2.查询DMP的收货明细记录，获取前一天或者日期参数门店的收货明细ID最小值、最大值、总数量
            ...//省略代码逻辑

            //3.将月份、日期、最大值、最小值，总条数，计算状态存储到计算追踪表
            ...//省略代码逻辑

            //4.按每页200条，根据ID升序分批获取前一天门店收货明细记录，并计算返利
            ...//省略代码逻辑（这一段执行时间超过2分钟）

            //处理退货数据，插入退货明细表中
            saveReturnDetail(dateStr);

            //5.前一天的数据全部处理完成后，更新追踪表的状态为已完成
            storeRebateCalculateLogService.updateCalculateLogStatus(storeRebateCalculateLog);

            stopWatch.stop();
            double totalTimeSeconds = stopWatch.getTotalTimeSeconds();
        } finally {
            if (success) {
                jedisCluster.del(lockKey);
            }
        }
    }

下面是检测的调度执行的方法

public String monitorRebateCalculate() {
        Date curDate = new Date();
        //当月1号
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(curDate);
        //获得本月第一天
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        Date firstDate = calendar.getTime();
        int totalNum = 0;
        if (DateUtil.getDay(firstDate) < DateUtil.getDay(curDate)) {
            //查询这个月1号到前一天有没有没有计算返利或者需要重新计算的数据
            Date monitorDate = null;
            List<StoreRebateCalculateLog> logList = storeRebateCalculateLogService.selectByDayRange(DateUtil.date2Str(firstDate, "yyyyMMdd"), DateUtil.date2Str(curDate, "yyyyMMdd"));
            Map<String, StoreRebateCalculateLog> dayMap = new HashMap<>();
            if(CollectionUtils.isNotEmpty(logList)){
                for (StoreRebateCalculateLog storeRebateCalculateLog : logList) {
                    dayMap.put(storeRebateCalculateLog.getDayStr(), storeRebateCalculateLog);
                }
            }
            //循环监测的日期
            for (monitorDate = firstDate; monitorDate.before(curDate); monitorDate = DateUtil.addDay(monitorDate, 1)) {
                String dayStr = DateUtil.date2Str(monitorDate, "yyyyMMdd");
                if (!dayMap.containsKey(dayStr)) {
                    //原来没有生成记录，要重算
                    this.calculateRebateDetail(dayStr);
                    totalNum++;
                } else {
                    StoreRebateCalculateLog storeRebateCalculateLog = dayMap.get(dayStr);
                    if (!StoreRebateConstant.STATUS_END.equals(storeRebateCalculateLog.getStatus()) || NEED_REPEAT_CAL_FLAG_TRUE.equals(storeRebateCalculateLog.getNeedRepeatCalFlag())) {
                        //状态不是已完成或者需要重新算
                        this.calculateRebateDetail(dayStr);
                        totalNum++;
                    }
                }
            }
        }
        JSONObject result = new JSONObject();
        result.put("syncStatus", "success");
        result.put("syncMsg", "处理成功");
        return SToolUtils.convertResultJSONObj(SUCCESS_STATUS, "处理成功", totalNum, new JSONArray().fluentAdd(result)).toString();

    }

这里循环当月从1号到当前日期的这些天，是否有需要重新计算或者原来没有计算的，是循环执行的一个过程。这里我们不需要了解这里面的代码逻辑，只需要知道，这里可能循环调用多次上面的calculateRebateDetail方法就可以了。

从上面的日志可以看出，报错的位置就是在calculateRebateDetail方法中108行这个地方通过DAO操作删除的时候：

storeRebateCalculateLogDao.deleteByDay(dateStr);//报错的地方

2.问题定位

开始在CSDN上搜索这个问题的解决方案，可是大部分都说的是数据库连接池的问题，这里我们项目中用的连接池类型是HikariDataSource，

@Primary
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.master.hikari")
    public DataSource dataSource(@Qualifier("masterDataSourceProperties")DataSourceProperties properties) {
        return DataSourceBuilder.create(properties.getClassLoader())
                .type(HikariDataSource.class)
                .driverClassName(properties.determineDriverClassName())
                .url(properties.determineUrl())
                .username(properties.determineUsername())
                .password(properties.determinePassword())
                .build();
    }

连接池配置的信息如下：

这里跟连接超时或者失效的几个重要的参数都标记出来了，然后查看数据库的连接变量配置：

可以看出数据库的超时时间28800秒，即8个小时，而我们应用配置的连接超时时间是60秒，即1分钟，远远小于数据库配置的连接失效时间，不可能出现应用发现连接没有失效而去重新用这个连接的情况。

而且更蹊跷的是，数据库的配置参数和应用的配置参数生产环境和UAT环境一模一样的，可是UAT环境没有重现这个问题，那么到底是什么原因呢？

经过一番折腾，后面与运维人员的沟通中发现，在应用连接数据库的中间还存在一个负载均衡，而负载均衡配置的连接失效时间在UAT环境和生产环境是不一样的，在生产环境是2分钟，而UAT环境是10分钟。

这下豁然开朗了，原来返利计算过程的时间是超过2分钟的，但是小于10分钟，所以在生产环境执行删除操作的时候，报错了，而UAT环境没有问题。

可是问题才刚到一半，这个是什么原因，又怎么解决呢？难道这个代码写法哪里有问题？这个好像也是个很正常的写法，我们用的是连接池，按道理数据库连接的使用和释放等操作都是连接池在给我们操作的，而我们也不能单独去改生产环境的负载均衡配置和数据库参数配置。

3.问题解决

又是经过几番折腾，好在Mybatis调试模式详细打印了这个数据库操作的相关日志，从上面的日志中第一行就可以看到：

Fetched SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6d2b4a] from current transaction
==>  Preparing: delete from store_rebate_calculate_log where day_str = ?

在执行这个删除操作时，取的是前面用过的一个sqlSession，而这个sqlSession因为失效了，导致执行下面的删除操作时报错。

可是细看日志发现，在后面执行别的数据库操作时有时候取的是已有的sqlSession，有时是重新建了一个sqlSession。

这是什么原因呢？我们是不是可以利用这个特点解决我们的问题呢？比如让这个删除操作也创建一个新的sqlSession。

后面经过对比，发现别的sql操作新建了sqlSession的都是在调用的service的方法，于是我就把这个DAO直接操作删除的代码放到了service中，然后调用service中的方法，发现，果然可以了。于是我干脆把这个方法中所有涉及到数据库操作的方法直接用DAO操作的全部换成了service操作。

//1.删除前一天或者日期参数（yyyyMMdd）的门店的计算追踪表和返利明细表的数据
            storeRebateCalculateLogService.deleteByDay(dateStr);
            storeRebateDetailService.deleteByDay(dateStr);

本以为问题到此圆满解决了，你肯定也会以为解决了。可是实际上问题又来了。

发现在后面的某个查询语句又报错了，同样的错误，这里我就没有贴日志及代码了，只分析问题原因。

从日志中可以看出，尽管用的是service方法，但是也是用的已有的sqlSession，而不是新建的sqlSession，这是什么原因呢？

我们知道改用service调用，其实是想把这个事务交给spring管理，而这个涉及到事务的传播特性，项目中事务配置如下：

<!-- 配置事务的传播特性  -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="insert*" propagation="REQUIRED" rollback-for="Exception"/>
            <tx:method name="save*" propagation="REQUIRED" rollback-for="Exception"/>
            <tx:method name="update*" propagation="REQUIRED" rollback-for="Exception"/>
            <tx:method name="delete*" propagation="REQUIRED" rollback-for="Exception"/>
            <tx:method name="find*" propagation="SUPPORTS"/>
            <tx:method name="*" propagation="SUPPORTS"/>
        </tx:attributes>
    </tx:advice>

可以看出，增删改的方法用的是propagation="REQUIRED"，而查询的方法用的是propagation="SUPPORTS"，REQUIRED意味着当前如果有事务就会共用，而SUPPORTS是如果当前有事务，就共用事务，没有就以非事务方式运行。

后面通过跟踪Mybatis源码，发现只有在这几个传播特性时，才会新建sqlSession

SUPPORTS的特性是不会重新新建sqlSession的

至此，我们基本上所有的疑问全没有了，这样也能解释为什么只是用Service来做查询操作没有用了，因为项目中配置的查询方法的事务传播特性为SUPPORTS的缘故。

疑问是没有了，我怎么解决这个问题呢？直接改配置的事务传播特性？这样可能会对别的操作有影响，我采用的是声明式事务注解的办法，在这个查询方法对应的DAO上加上@Transactional注解就好了，因为@Transactional默认的事务传播特性为Propagation.REQUIRED，所以是没有问题的。