Product

Identity Governance

Issue details

IG – Import Users throws unique constraint violation (duplicate user records)

Error Message

ERROR [stderr] (import-connector thread-3) org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [ INSERT INTO Users (UserID, DatabaseID, PersonID, UserName, Organization, OrganizationType) VALUES (?, ?, ?, ?, ?, ?) ]; Violation of UNIQUE KEY constraint ‘IX_Users’. Cannot insert duplicate key in object ‘dbo.Users’.

Cannot insert duplicate key in object ‘dbo.Users’. The duplicate key value is (<ID>, <UID>).

11:36:33,644 ERROR [stderr] (import-connector thread-3)   at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)

Possible Cause(s)

The users.udb file has been generated in Pentaho Data Ingestion (Kettle Script) by using application Input files, merging them with IdM, AD and HR databases.

Kettle script creates Users.udb, Resources.rdb and UserResources.csv. 

These files are imported into the Application Universe in Identity Governance.

The data file users.udb contains users with the same PersonID in different cases but Identity Governance database is set up as case insensitive.

Solution/Fix

The PDI script should be fixed to convert the PersonId value of all users to lowercase and then consider using a Unique Rows step to remove the duplicate users before the users.udb file is generated from the script.

After the users.udb is fixed, delete the existing master and model configurations from Application connector in Identity Governance and re-import the users and resources.

Workaround

Delete duplicate records from Users.udb and reimport the data into Identity Governance.

Reference

NA

Author

Ketaki Mujumdar