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 more
This can occur on rare occasions due to the way the audit hints are created.
Knowledge Base

Foreign key constraint violation during Audit Event history purge

Problem

Solution

Related articles
