Foreign key constraint violation during Audit Event history purge
Problem
If log output for JEMH is enabled, the following (or similar) may be seen in the log output:
2019-02-08 12:45:07,348 JEMH-Housekeeping-Worker-1 ERROR [emh.ui.ao.JEMHActiveObjectServiceImpl] Unable to deleteAuditEventsOlderThan(): There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.4.20
- minor version:4
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: update or delete on table "AO_78C957_AUDITEVENTS" violates foreign key constraint "fk_ao_78c957_audithint_audit_event_id" on table "AO_78C957_AUDITHINT"
Detail: Key (ID)=(2) is still referenced from table "AO_78C957_AUDITHINT".
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.4.20
- minor version:4
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: update or delete on table "AO_78C957_AUDITEVENTS" violates foreign key constraint "fk_ao_78c957_audithint_audit_event_id" on table "AO_78C957_AUDITHINT"
Detail: Key (ID)=(2) is still referenced from table "AO_78C957_AUDITHINT".
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.deleteWithSQL(EntityManagedActiveObjects.java:118)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.deleteWithSQL(TenantAwareActiveObjects.java:281)
at sun.reflect.GeneratedMethodAccessor938.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:302)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
at com.sun.proxy.$Proxy4875.deleteWithSQL(Unknown Source)
at com.javahollic.jira.emh.ui.ao.JEMHActiveObjectServiceImpl.deleteAuditEventsOlderThan(JEMHActiveObjectServiceImpl.java:1617)
at com.javahollic.jira.emh.service.scheduler.jobs.HousekeepingJob.purgeAuditHistory(HousekeepingJob.java:342)
at com.javahollic.jira.emh.service.scheduler.jobs.HousekeepingJob.clearAuditHistory(HousekeepingJob.java:283)
at com.javahollic.jira.emh.service.scheduler.jobs.HousekeepingJob.access$200(HousekeepingJob.java:34)
at com.javahollic.jira.emh.service.scheduler.jobs.HousekeepingJob$2.run(HousekeepingJob.java:192)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
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)
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "AO_78C957_AUDITEVENTS" violates foreign key constraint "fk_ao_78c957_audithint_audit_event_id" on table "AO_78C957_AUDITHINT"
Detail: Key (ID)=(2) is still referenced from table "AO_78C957_AUDITHINT".
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:288)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:135)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at net.java.ao.EntityManager.deleteWithSQL(EntityManager.java:514)
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.deleteWithSQL(EntityManagedActiveObjects.java:116)
... 31 moreThis can occur on rare occasions due to the way the audit hints are created.