Layer7 API Management

Expand all | Collapse all

Audit Purge Issues

Jump to Best Answer
  • 1.  Audit Purge Issues

    Posted 12-04-2019 02:37 AM
    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.


  • 2.  RE: Audit Purge Issues
    Best Answer

    Posted 12-04-2019 10:12 AM
    Hi Shabaz,

    At what time of day are you running the audit purge script?  It is suggested to run that during off peak hours when there is minimal requests coming into the gateway.
    Are you running the script manually or via a cron job?  How many audits are you attempting to delete at once?  The default is 5000.  Did you modify this at all?
    How many audits are currently within the audit_main table?  Are you running the audit_purge script on a regular basis or is this the first time you are attempting to execute it?

    Do you need any of the existing audits within the DB?  If not, then perhaps you may want to truncate the audit tables to completely delete everything and then be sure to use the audit purge script going forward on a regular basis.  We can give you the instructions to truncate the audit tables if that is an approach you want to take.

    Daren




  • 3.  RE: Audit Purge Issues

    Posted 12-09-2019 01:18 AM
    Hi Darren,
    We are running the audit purge script at 11.00 PM daily, via cron job and deleting 45000 audit records at once.
    But we are facing the issue between 1.00 AM - 2.00 AM daily.

    Also I have few doubts about the gateway's behaviour when the script is running.
    Does the gateway stops processing the incoming requests as the customers are facing the timeout issues at that particular time.?
    Have checked the dashboard as well it shows no requests have reached the gateway during that time.


    Thanks,
    Shabaz.


  • 4.  RE: Audit Purge Issues

    Posted 12-09-2019 08:49 AM
    Hi,
    We also experienced problems with purge script, with deadlocks, timeouts, and so on.
    Resolution comes in 3 flavors:
    - Try to avoid logging into audit events (especially in Production) and log into dedicated disk log files. We know have fewer than 2K entries into audit events.
    Disk log files can have a larger retention period without impacting production. You can even decide to have one API logging into a 180days or more retention period shoud you need to.
    - Lower down number of records to delete on one pass, and, if possible, reduce number of days to keep audits. We have as for now 1000 records and 2 retention days.
    - Sending audits to an external system such as Kibana, Splunk, or else, and optionally remove audit events recording into ssg database.

    To be compatible with Container gateway, we are using a Service (recursive)+Scheduled task to clean audit events, instead of crontab job+shell script.



  • 5.  RE: Audit Purge Issues

    Posted 12-09-2019 05:09 PM
    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