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)
Release : 14.4
Component : CA Identity Manager
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.
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>