search

alibaba / druid

阿里云计算平台DataWorks(https://help.aliyun.com/document_detail/137663.html) 团队出品，为监控而生的数据库连接池
https://github.com/alibaba/druid/wiki
Apache License 2.0
28.01k stars 8.59k forks source link

No operations allowed after statement closed是什么问题，怎么修复 #5385

Open ivanmissu opened 1 year ago

ivanmissu commented 1 year ago

druid版本 1.2.17 mysql驱动版本 mysql-connector-j 8.0.33 mysql服务端 5.7.33-36-log 主从 mysql使用驱动 com.mysql.jdbc.Driver mysql连接串 jdbc:mysql:replication://db-write,db-read/ssdb?createDatabaseIfNotExist=true&characterEncoding=utf8&useUnicode=true&useSSL=false&rewriteBatchedStatements=true (mysql连接串使用replication模式，不过在未使用replication模式时也会报错No operations allowed after statement closed) 操作均是spring-jdbc和hibernate操作

mysql参数配置 [mysqld] default_storage_engine=InnoDB max_connections=65535 wait_timeout=1814400 lower_case_table_names=1 log_error_verbosity=1 table_open_cache=128 max_connections=2000 max_connect_errors=6000 innodb_file_per_table=1 innodb_buffer_pool_size=1G max_allowed_packet=128M transaction_isolation=READ-COMMITTED innodb_flush_method=O_DIRECT innodb_lock_wait_timeout=1800 innodb_flush_log_at_trx_commit=0 group_concat_max_len=1024000 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION skip-name-resolve

druid参数配置 initialSize: 10 minIdle: 10 maxActive: 50 maxWait: 100 keepAlive: true removeAbandoned: true removeAbandonedTimeout: 10 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: false maxPoolPreparedStatementPerConnectionSize: 20

09:31:17.464 [ERROR] com.alibaba.druid.pool.DruidPooledPreparedStatement - [,68] - getMaxFieldSize error java.sql.SQLException: No operations allowed after statement closed. at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:98) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:90) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:64) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:74) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:82) at com.mysql.cj.jdbc.StatementImpl.getMaxFieldSize(StatementImpl.java:1508) at sun.reflect.GeneratedMethodAccessor114.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at com.mysql.cj.jdbc.ha.MultiHostConnectionProxy$JdbcInterfaceProxy.invoke(MultiHostConnectionProxy.java:107) at com.sun.proxy.$Proxy210.getMaxFieldSize(Unknown Source) at com.alibaba.druid.filter.FilterChainImpl.statement_getMaxFieldSize(FilterChainImpl.java:2898) at com.alibaba.druid.filter.FilterAdapter.statement_getMaxFieldSize(FilterAdapter.java:2561) at com.alibaba.druid.filter.FilterChainImpl.statement_getMaxFieldSize(FilterChainImpl.java:2896) at com.alibaba.druid.filter.FilterAdapter.statement_getMaxFieldSize(FilterAdapter.java:2561) at com.alibaba.druid.filter.FilterChainImpl.statement_getMaxFieldSize(FilterChainImpl.java:2896) at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.getMaxFieldSize(StatementProxyImpl.java:311) at com.alibaba.druid.pool.DruidPooledPreparedStatement.(DruidPooledPreparedStatement.java:66) at com.alibaba.druid.pool.DruidPooledConnection.prepareStatement(DruidPooledConnection.java:371) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1929) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1898) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2617) at org.hibernate.loader.Loader.doList(Loader.java:2600) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429) at org.hibernate.loader.Loader.list(Loader.java:2424) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at org.hibernate.internal.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:966) at io.maple.framework.core.dao.hibernate.HibernatePersisterImpl$2.doInHibernate(HibernatePersisterImpl.java:208) at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:381) at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:333) at io.maple.framework.core.dao.hibernate.HibernatePersisterImpl.getRow(HibernatePersisterImpl.java:191) at io.maple.framework.core.dao.hibernate.query.HQLFetcher.getOne(HQLFetcher.java:94) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerInfoServiceImpl.getCustomerByIdNumber(CustomerInfoServiceImpl.java:579) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerInfoServiceImpl.getCustomerByIdNumber(CustomerInfoServiceImpl.java:589) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerInfoServiceImpl$$FastClassBySpringCGLIB$$35a59ebb.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerInfoServiceImpl$$EnhancerBySpringCGLIB$$7100b556.getCustomerByIdNumber() at sun.reflect.GeneratedMethodAccessor415.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:338) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:70) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy141.getCustomerByIdNumber(Unknown Source) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerCheckCardServiceImpl.saveCheckCardRuleByIdNumber(CustomerCheckCardServiceImpl.java:242) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerCheckCardServiceImpl$$FastClassBySpringCGLIB$$8bdfc9c9.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:747) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:689) at cn.tripnx.ykt.enterprise.business.customer.service.impl.CustomerCheckCardServiceImpl$$EnhancerBySpringCGLIB$$5a638f40.saveCheckCardRuleByIdNumber() at sun.reflect.GeneratedMethodAccessor505.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:338) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:197) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:70) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy152.saveCheckCardRuleByIdNumber(Unknown Source) at cn.tripnx.ykt.enterprise.exporter.services.checkCard.CheckCardByIdNumberOrQrcodeServiceExporter.invoke(CheckCardByIdNumberOrQrcodeServiceExporter.java:81) at io.maple.framework.core.integration.base.KitBaseServiceExporter.service(KitBaseServiceExporter.java:29) at cn.tripnx.ykt.enterprise.exporter.controller.ApiController.service$original$fPnM4jnE(ApiController.java:155) at cn.tripnx.ykt.enterprise.exporter.controller.ApiController.service$original$fPnM4jnE$accessor$JwmeYRQZ(ApiController.java) at cn.tripnx.ykt.enterprise.exporter.controller.ApiController$auxiliary$SUUMvmbJ.call(Unknown Source) at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:86) at cn.tripnx.ykt.enterprise.exporter.controller.ApiController.service(ApiController.java) at sun.reflect.GeneratedMethodAccessor178.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:209) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) 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:871) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:777) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) at io.maple.framework.core.web.mvc.DispatcherServletEx.doDispatch(DispatcherServletEx.java:72) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:881) at javax.servlet.http.HttpServlet.service(HttpServlet.java:681) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:855) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61) at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:387) at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:114) 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.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 com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:114) 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:196) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:542) at org.apache.catalina.core.StandardHostValve.invoke$original$tqfV9A3c(StandardHostValve.java:135) at org.apache.catalina.core.StandardHostValve.invoke$original$tqfV9A3c$accessor$xPw0ZpD1(StandardHostValve.java) at org.apache.catalina.core.StandardHostValve$auxiliary$pPDeEtk4.call(Unknown Source) at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept(InstMethodsInter.java:86) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:364) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:624) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:831) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1673) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748) Caused by: com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62) at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:86) at com.mysql.cj.jdbc.StatementImpl.checkClosed(StatementImpl.java:336) at com.mysql.cj.jdbc.StatementImpl.getMaxFieldSize(StatementImpl.java:1506) ... 157 common frames omitted

ShangJihe commented 11 months ago

请问解决了吗？ 我遇到了同样的问题，好长时间都解决不了。 关键这个报错不是每次请求都会出现，一般请求5-20次会成功一次，其余均会报错。

druid版本 1.2.16 mysql驱动版本 mysql-connector-java 8.0.19 mysql版本： 8.0.31 mysql部署架构：单机； mysql使用驱动 com.mysql.cj.jdbc.Driver mysql连接串 jdbc:mysql:///?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&allowMultiQueries=true&useSSL=false 代码架构：springMVC+mybatis

mojodevops commented 8 months ago

Has the problem been fixed? May be you should check the code about statement close to see if there was an exception happened.