'fk_audit_entry_changeset' reference constraint error observed when crowd attempts to remove the stale audit entry
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
DB connected to Crowd encounters an increase in size regularly, which is resumed back to its original size after the application restart.
Environment
Crowd 5.1.2 and MySQL
Symptoms
"fk_audit_entry_changeset" errors are observed on the logs when Crowd attempts to delete an entry in the 'cwd_audit_log_entry' table without first deleting a corresponding entry in the 'cwd_audit_log_changeset' table
Diagnosis
Below are errors in the confluence-atlassian.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
2023-04-16 00:19:03,312 Caesium-2-2 ERROR [engine.jdbc.spi.SqlExceptionHelper] The DELETE statement conflicted with the REFERENCE constraint "fk_audit_entry_changeset". The conflict occurred in database "crowddb_hi2vmtest9", table "dbo.cwd_audit_log_entry", column 'changeset_id'.
2023-04-16 00:19:03,312 Caesium-2-2 WARN [crowd.manager.audit.AuditLogPruner] Could not remove stale audit log entries
javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1681)
at com.atlassian.crowd.dao.audit.AuditDAOHibernate.removeChangesetsOlderThan(AuditDAOHibernate.java:75)
at com.atlassian.crowd.manager.audit.AuditServiceImpl.removeEntriesOlderThan(AuditServiceImpl.java:99)
at com.atlassian.crowd.manager.audit.AuditServiceImpl.removeStaleEntries(AuditServiceImpl.java:89)
at sun.reflect.GeneratedMethodAccessor704.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:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)Cause
During an upgrade, if the database backup from an old instance is taken using the data dump utility while the database is active, (also known as a "hot dump") , we can see database constraint errors in the logs.
This usually happens when Crowd is upgraded from Crowd version 3.2.
The issue can persist across upgrades.
Solution
Take a backup of the Crowd Database.
Stop Crowd.
ℹ️All the queries (MySQL) need to be executed in the same sequence
Backup the below tables (Once the Table is recreated with proper constraints, we can reinsert the data from the backup tables if needed.)
1
2
3
SELECT * INTO cwd_audit_log_changeset_bck FROM cwd_audit_log_changeset;
SELECT * INTO cwd_audit_log_entry_bck FROM cwd_audit_log_entry;
SELECT * INTO cwd_audit_log_entity_bck FROM cwd_audit_log_entity;Drop the 3 tables in the below sequence.
1
2
3
drop table CWD_AUDIT_LOG_ENTRY;
drop table CWD_AUDIT_LOG_ENTITY;
drop table cwd_audit_log_changeset;Run the below DDL to recreate these tables with proper index and constraint.
For Table CWD_AUDIT_LOG_CHANGESET
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE crowd.dbo.cwd_audit_log_changeset ( id numeric(19,0) NOT NULL, audit_timestamp numeric(19,0) NOT NULL, author_type varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, author_id numeric(19,0) NULL, author_name varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, event_type varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, ip_address varchar(45) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, event_message varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, event_source varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, CONSTRAINT PK__cwd_audi__3213E83FF210ECC8 PRIMARY KEY (id));
CREATE NONCLUSTERED INDEX idx_audit_authid ON dbo.cwd_audit_log_changeset ( author_id ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_authname ON dbo.cwd_audit_log_changeset ( author_name ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_authtype ON dbo.cwd_audit_log_changeset ( author_type ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_ip ON dbo.cwd_audit_log_changeset ( ip_address ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_source ON dbo.cwd_audit_log_changeset ( event_source ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_timestamp ON dbo.cwd_audit_log_changeset ( audit_timestamp ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_auth_eventtype ON dbo.cwd_audit_log_changeset ( event_type ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;For Table CWD_AUDIT_LOG_ENTITY
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE crowd.dbo.cwd_audit_log_entity ( id numeric(19,0) NOT NULL, entity_type varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, entity_id numeric(19,0) NULL, entity_name varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL, is_primary char(1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, changeset_id numeric(19,0) NOT NULL, CONSTRAINT PK__cwd_audi__3213E83F3702AA11 PRIMARY KEY (id) );
CREATE NONCLUSTERED INDEX idx_audit_entid ON dbo.cwd_audit_log_entity ( entity_id ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_entname ON dbo.cwd_audit_log_entity ( entity_name ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_enttype_id ON dbo.cwd_audit_log_entity ( entity_type ASC , entity_id ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_audit_enttype_name ON dbo.cwd_audit_log_entity ( entity_type ASC , entity_name ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_changeset_entity ON dbo.cwd_audit_log_entity ( changeset_id ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
-- crowd.dbo.cwd_audit_log_entity foreign keys
ALTER TABLE crowd.dbo.cwd_audit_log_entity ADD CONSTRAINT fk_changeset_entity FOREIGN KEY (changeset_id) REFERENCES crowd.dbo.cwd_audit_log_changeset(id) ON DELETE CASCADE;For Table CWD_AUDIT_LOG_ENTRY
1
2
3
4
5
6
7
CREATE TABLE crowd.dbo.cwd_audit_log_entry ( id numeric(19,0) NOT NULL, property_name varchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, changeset_id numeric(19,0) NOT NULL, old_value text COLLATE SQL_Latin1_General_CP1_CS_AS NULL, new_value text COLLATE SQL_Latin1_General_CP1_CS_AS NULL, CONSTRAINT PK__cwd_audi__3213E83FCDE91A69 PRIMARY KEY (id) );
CREATE NONCLUSTERED INDEX idx_audit_propname ON dbo.cwd_audit_log_entry ( property_name ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
CREATE NONCLUSTERED INDEX idx_entry_changeset ON dbo.cwd_audit_log_entry ( changeset_id ASC ) WITH ( PAD_INDEX = OFF ,FILLFACTOR = 100 ,SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , STATISTICS_NORECOMPUTE = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY ] ;
-- crowd.dbo.cwd_audit_log_entry
foreign keys ALTER TABLE crowd.dbo.cwd_audit_log_entry ADD CONSTRAINT fk_audit_entry_changeset FOREIGN KEY (changeset_id) REFERENCES crowd.dbo.cwd_audit_log_changeset(id);Now, you can restore the table data from the backup tables (If needed). Please execute the below SQL:
1
2
3
insert into CWD_AUDIT_LOG_CHANGESET select * from cwd_audit_log_changeset_bck;
insert into CWD_AUDIT_LOG_ENTITY select * from cwd_audit_log_entity_bck;
insert into CWD_AUDIT_LOG_ENTRY select * from cwd_audit_log_entry_bck; commit;Now restart the Crowd and validate the audit error entry in the logs.
If everything is working as expected after some round of testing, you can drop the backup tables from the Database.
1
2
3
drop table CWD_AUDIT_LOG_ENTRY_bck;
drop table CWD_AUDIT_LOG_ENTITY_bck;
drop table CWD_AUDIT_LOG_CHANGESET_bck;You may work with your DBA to implement the changes.
In case the issue still persists, need to check the data inconsistency.
Was this helpful?