Crowd fails to remove stale audit log entries due to foreign key constraint
Platform Notice: Data Center Only - This article only applies to Atlassian apps on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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.
Solution
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.
Stop Crowd
Drop the constraint
ALTER TABLE cwd_audit_log_entry DROP CONSTRAINT fk_4bgmmrjg4ggo6k2vchld0jaaj;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;Start Crowd
Always back up your data before making any database modifications. 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
Was this helpful?