DB Trimming runs into - Cannot insert duplicate key in object

book

Article ID: 211093

calendar_today

Updated On:

Products

CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort)

Issue/Introduction

DB trimming fails with footprints like such. 

2021-03-05 00:00:01,174 CET : [DefaultQuartzScheduler_Worker-5] : ERROR : trimmingLogger : Error processing table: ARWFGENERICCRED
com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'UQ__ARWFGENE__35AA10360D81967A'. Cannot insert duplicate key in object 'dbo.ARWFGENERICCRED_AR'. The duplicate key value is (1:247511).


 

Cause

Unique constraint on the ARWFGENERICCRED_AR table as ARWFGENERICCRED is being trimmed. Due to trimming the DB trimming tool is archiving to ARWFGENERICCRED_AR table before trimming the records in ARWFGENERICCRED table but the CREDID column in ARWFGENERICCRED_AR table needs to be unique. This conflict on CREDID leads to the issue. 

Environment

Release : 9.1

Component : AuthMinder(Arcot WebFort)

Resolution

The fix is to delete the Unique Key constraint (in this example failure - 'UQ__ARWFGENE__35AA10360D81967A') on the ARWFGENERICCRED_AR table. 

A windows MS SQL  DB related screenshot showing unique Key as "UQ_.......". Right click and delete this Unique Key Constraint. 

 

Run the DB trim tool post deleting the Unique Key Constraint called in the failure. This will allow the DB trimming tool to run to completion. 

 

Additional Information

Just FYI - Note that the ARWFGENERICCRED_AR table when created has created this Unique Key Constraint with respect to CREDID column. 

CREATE TABLE ARWFGENERICCRED_AR
(
        DMDV  INTEGER  NOT NULL,
        CREDID  VARCHAR(64)  NOT NULL  UNIQUE,
        USERREFID  INTEGER  NOT NULL,
        ORGNAME  VARCHAR(256)  NOT NULL,
        CREDNAME  VARCHAR(32)  NOT NULL,
        VALIDITYSTARTDATE  DATETIME  NOT NULL,
        VALIDITYENDDATE  DATETIME  NOT NULL,
        STRIKECOUNT  INTEGER  DEFAULT  0,
        LASTSTRIKEDATE  DATETIME,
        LASTSUCCESSDATE  DATETIME,
        CREDSTATUS  INTEGER  NOT NULL,
        TRANSALGO  INTEGER  NOT NULL,
        DATECREATED  DATETIME  NOT NULL,
        DATEMODIFIED  DATETIME,
        CREDENTIAL  VARCHAR(2048)  NOT NULL,
        NOTES  VARCHAR(1024),
        PROFILENAME  VARCHAR(128)  NOT NULL,
        PROFILEVERSION  INTEGER  NOT NULL
        -- CONSTRAINT  PK_ARWFGENERICCRED  PRIMARY KEY  (USERREFID, CREDNAME)
); 

Attachments