Cleanup Submitted Tasks fails with PRIMARY KEY constraint violation PK_archive_runtimeStatusDetail12

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

Seeing the following error when executing clear up submitted tasks.

When executing the "Cleanup Submitted Tasks" task in Identity Manager, the task fails and the following error is recorded in the logs or View Submitted Tasks (VST): Task "Cleanup Submitted Tasks": Failed to execute CleanupSubmittedTasksEvent. ERROR MESSAGE: Violation of PRIMARY KEY constraint 'PK_archive_runtimeStatusDetail12'. Cannot insert duplicate key in object 'dbo.archive_runtimeStatusDetail12'. The duplicate key value is (XXXXXX).

In Oracle environments, the error appears as: ORA-00001: unique constraint (IAMARCHIVE.PK_ARCH_RUNTIMESTATUSDETAIL12) violated

This issue is frequently reported following database migrations, manual restores of task persistence data, or when database replicas become out-of-sync.

 

Environment

• Product: Identity Manager
• Releases: 14.4, 14.5
• Database: Oracle, Microsoft SQL Server

Cause

The error occurs because the cleanup process attempts to move a task record from the runtime table (runtimeStatusDetail12) to the archive table (archive_runtimeStatusDetail12), but a record with the same ID already exists in the archive destination. This data inconsistency typically results from:

1. Migrating old archive data into a new Identity Manager environment.
2. Database replicas being out of synchronization.
3. Partial database restores where the runtime store was rolled back but the archive was not (or vice versa).

Resolution

If the Archive information is not needed, for example shortly after the initial installation, or in non-production environments, drop the archive tables, denoted by the ARCHIVE_ in the database and restart Identity Manager to recreate those tables. 


If the Archive information is needed, for example in Production environments, please see KB 44246 How to find duplicates for IM tasksession12_5 and archive_tasksession12_5__clean_up for information on how to locate and cleanup the duplicate information.