Duplicate TASKSESSIONID entries causing PRIMARY KEY constraint violation in archive_tasksession12_5

Products

• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Identity Suite

Issue/Introduction

When executing the "Cleanup Submitted Tasks" task in Identity Manager, the process fails to move records to the archive database. The following error is recorded in the logs or View Submitted Tasks (VST):

ERROR MESSAGE: Violation of PRIMARY KEY constraint 'PK_archive_tasksession12_5'. Cannot insert duplicate key in object 'dbo.archive_tasksession12_5'. The duplicate key value is (XXXXX).

In Oracle environments, the error may appear as: ORA-00001: unique constraint (IAMARCHIVE.PK_ARCH_TASKSESSION12_5) violated

This occurs when a TASKSESSIONID in the runtime TASKSESSION12_5 table already exists in the ARCHIVE_TASKSESSION12_5 table.

Environment

• Product: Identity Manager, Identity Suite
• Releases: 14.1, 14.2, 14.3, 14.4, 14.5
• Database: Microsoft SQL Server, Oracle

Cause

The primary key for these tables is the TASKSESSIONID. Overlapping IDs typically occur during:

• Database migrations where archive data is imported into a new environment.
• Database separation (splitting a joint DB into separate Runtime and Archive instances).
• Identity seed resets in the database following a partial restore or selective table copy.
• Out-of-sync database replicas.

Resolution

Instructions: 

How to find duplicates for IM tasksession12_5 and archive_tasksession12_5

Example of data in the ARCHIVE_TASKSESSION12_5 Table Note: Primary Key is TASKSESSIONID

Example of data in the TASKSESSION12_5 Table Note: Primary Key is TASKSESSIONID

Find Duplicates with SQL Query

SELECT a.TASKSESSIONID, b.TASKSESSIONID FROM ARCHIVE_TASKSESSION12_5 a, TASKSESSION12_5 b where a.TASKSESSIONID=b.TASKSESSIONID;

For comparison: An example of reverse  query; tasksessionid that do not match

 

Additional Information

Before do changes in DB ask help from DBA to backup and help with SQL commands.