Problem

When Crowd attempts to remove stale audit log entries it fails due to a foreign key constraint and the following error message is thrown in the atlassian-crowd.log file

2018-12-11 00:00:00,038 Caesium-1-4 WARN [crowd.manager.audit.AuditServiceImpl] Could not remove stale audit log entries
org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:189)
...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on
 table "cwd_audit_log_changeset" violates foreign key constraint 
"fk_4bgmmrjg4ggo6k2vchld0jaaj" on table "cwd_audit_log_entry"
  Detail: Key (id)=(xxxxxx) is still referenced from table "cwd_audit_log_entry".

Diagnosis

This should only affect versions of Crowd earlier than 3.2.x

Cause

Crowd is attempting to delete an entry in the 'cwd_audit_log_entry' table without first deleting a corresponding entry in the 'cwd_audit_log_changeset' table.

Workaround

• Drop and recreate the constraint, adding ON DELETE CASCADE to automatically remove the corresponding entries (as later releases of Crowd are configured out of the box)

  • The current example is for Postgres. Additional examples will be provided for other supported DBMS soon.

  1. Stop Crowd

  2. Drop the constraint

     ALTER TABLE cwd_audit_log_entry DROP CONSTRAINT fk_4bgmmrjg4ggo6k2vchld0jaaj;

  3. Recreate the constraint

     ALTER TABLE cwd_audit_log_entry ADD CONSTRAINT "fk_4bgmmrjg4ggo6k2vchld0jaaj" FOREIGN KEY (changeset_id) REFERENCES cwd_audit_log_changeset(id) ON DELETE CASCADE;

  4. Start Crowd
     

     Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Resolution

• Upgrade Crowd to a version which is not affected by this issue, 3.2.x or above