SQLException occurred: A duplicate key violation has occurred

book

Article ID: 127875

calendar_today

Updated On:

Products

CA Identity Manager CA Identity Governance CA Identity Portal CA Risk Analytics CA Secure Cloud SaaS - Arcot A-OK (WebFort) CLOUDMINDER ADVANCED AUTHENTICATION CA Secure Cloud SaaS - Advanced Authentication CA Secure Cloud SaaS - Identity Management CA Secure Cloud SaaS - Single Sign On

Issue/Introduction

When trying to create new approval workflow process you are unable to save any activity. It always throws an error "SQLException occurred: A duplicate key violation has occurred".
It fails when also trying to create a copy and save an existing workflow process.
 

Cause

If there is a duplicate key error, the root cause is due to the Workpoint IDs being out of Sync. Resolutions include (1) running IdCheck successfully and clearing Workpoint Cache in the server or (2) change the Workpoint DB value to a new 4 character value.

The typically issue reported for  CA/Broadcom Identity Suite is for a new install of the product, which populates the Workpoint database using the Workpoint DB identifier of ‘WPDS’. When the product starts up the first time, the application will create the Workpoint database tables.

The tables are created and initialize the WPDS IDs to 1. Next the application imports the Workpoint Archive of IDM base Workpoint processes. The Archive was built with the DB identifier of ‘WPDS’. Since it is the same as the initialized DB identifier, in Workpoint 3.52, you need to run the IdCheck to resync the Workpoint IDs to set them to the max value in the database after the import. In later versions, the IdCheck is part of the import application. Once the IdCheck runs, the application server must be restarted or the Workpoint cache must be cleared. This forces the Workpoint Server to reinitialize cache with the correct next ID that was just updated by IdCheck in the database.

The only resolution for Workpoint within Identity Suite is the IdCheck tool to resync the WPDS Database identifier. Changing the Workpoint DB identifier is not a valid option as we believe the IDM product requires the Workpoint ID to be ‘WPDS’. The IdCheck.bat can be used to run it externally, and it is provided with the Workpoint core installation. You will need to validate that you have the Database Vendor jars in its classpath and that the name of the jar is correct in the bat file.
 

Environment

Release:
Component: IDMGR

Resolution

The only resolution for Workpoint within Identity Suite is the IdCheck tool to resync the WPDS Database identifier. The IdCheck.bat can be used to run  externally, and it is provided with the Workpoint core installation. You will need to validate that you have the Database Vendor jars in its classpath and that the name of the jar is correct in the bat file.

1. Run IDCheck.bat (located in the Identity Manager home /tools location)
2. Restart the application server.