ORA-00001: unique constraint violation

Products

• CA Identity Suite

Issue/Introduction

Since the upgrade to 14.4.1 create user tasks randomly fail with the following exception: ORA-00001: unique constraint violation

2023-02-03 11:17:30,278 ERROR [stderr] (Thread-4366 (ActiveMQ-client-global-threads)) Caused by: Error : 1, Position : 0, Sql = INSERT INTO TESTTABLE (TESTTABLE.IP, TESTTABLE.USERNUMBER) VALUES (:1 , :2 ), OriginalSql = INSERT INTO TESTTABLE (TESTTABLE.IP, TESTTABLE.USERNUMBER) VALUES (?, ?), Error Msg = ORA-00001: unique constraint violation)

Environment

Release : 14.4

Component : CA Identity Manager

Cause

The problem was due to the GetValues operation defined in the directory.xml. This query is used to lookup the unique usernumber created during table insert in a create user event which is then used to update related tables like TESTTABLE. During bulkload operations this looking often returned a higher ID than the currently created ID during insert of the user in the DB.

Resolution

The problem was solved by changing the definition of the operation in the directory.xml file of the user store:

From:
<Operation name="GetValues" value="SELECT MAX(TESTTABLE.USERNUMBER) FROM TESTTABLE"/>

To:
<Operation name="GetValues" value="SELECT TESTTABLE.USERNUMBER FROM TESTTABLE WHERE TESTTABLE.USERNUMBER=?">
  <Parameter name="TESTTABLE.USERNUMBER"/>
</Operation>