Dear Shabaz,
If you already have big audit tables before you install the audit purge script, this is expected behaviour.
The reason is, the batch deletion is very costly. If you monitor mysql when the audit purge is running, you can see the mysql is 100% busy, and you may find long time queries in mysql slow log.
If the mysql performance is not good, it will pause the gateway, or even crash the gateway.
First of all, we don't recommend to enable audit on prod env, it costs too much -- it can impact gateway performance up to 75% (note that I'm talking about auditing, not the audit purge script) , and anything in audits is in the ssg log as well, by default. It's not a "must" to keep audits.
If you have to keep audit data, you need your DBA to clean up the audit tables before install the audit purge script , ie. only keep the data you want to keep. If you only rely on audit purge script, it may take weeks, or even months to remove all the data you don't want to keep, for example, you only want to keep 7 days of data, but the database already has 2 years of data, the audit purge script by default only remove 5000 records each time, so it needs to run many times to remove the unwanted 2 years of data. And during this period, each time it runs, the gateway will be badly impacted.
In my opinion, the data maintenance should be a job of DBA, the tool(s) we provided can only work well if you have a DBA who understand how it works.
Regards,
Mark
Original Message:
Sent: 12-04-2019 02:36 AM
From: Shabaz Shaikh
Subject: Audit Purge Issues
Hi All,
Gateway is unable to process the incoming requests when the audit purge script is running,below is the error :
2019-12-04T01:07:01.518+0530 WARNING 2053086 org.hibernate.util.JDBCExceptionReporter: SQL Error: 0, SQLState: 080032019-12-04T01:07:01.518+0530 WARNING 2053086 org.hibernate.util.JDBCExceptionReporter: SQL Error: 0, SQLState: 080032019-12-04T01:07:01.518+0530 SEVERE 2053086 org.hibernate.util.JDBCExceptionReporter: No operations allowed after statement closed.2019-12-04T01:07:01.524+0530 SEVERE 2053086 org.apache.catalina.core.ContainerBase.[ssg].[hostname].[/].[SoapMessageProcessingServlet]: Servlet.service() for servlet SoapMessageProcessingServlet threw exceptioncom.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed. at sun.reflect.GeneratedConstructorAccessor5514.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.Util.getInstance(Util.java:386) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:458) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2390) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1997) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1468) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70) at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2434) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2874) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383) at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133) at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723) at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:393) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:120) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.$Proxy177.save(Unknown Source) at com.l7tech.server.audit.AuditContextImpl.a(Unknown Source) at com.l7tech.server.audit.AuditContextImpl.a(Unknown Source) at com.l7tech.server.audit.AuditContextFactory.doWithNewAuditContext(Unknown Source) at com.l7tech.server.SoapMessageProcessingServlet.service(Unknown Source) at javax.servlet.http.HttpServlet.service(HttpServlet.java:770) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:646) at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:436) at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:342) at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:302) at com.l7tech.server.transport.http.HttpNamespaceFilter.doFilter(Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.l7tech.server.WsdlFilter.doFilter(Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.l7tech.server.transport.http.ConnectionIdFilter.doFilter(Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.l7tech.server.transport.http.InputTimeoutFilter.doFilter(Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.l7tech.server.log.HybridDiagnosticContextServletFilter.doFilter(Unknown Source) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:234) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:181) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at com.l7tech.server.tomcat.ResponseKillerValve.invoke(Unknown Source) at com.l7tech.server.tomcat.ConnectionIdValve.invoke(Unknown Source) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:295) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:610) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:410) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748)
Please suggest.
Thanks,
Shabaz.