000036223 - RSA Adaptive Authentication (on Premise) - Unique constraint violation exception in scheduler log

Document created by RSA Customer Support Employee on May 9, 2018
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000036223
Applies ToRSA Product Set: Adaptive Authentication (OnPrem)
RSA Product/Service Type: Adaptive Authentication (OnPrem)
RSA Version/Condition: 7.x
 
IssueCustomer is seeing too many "unique constraint (RSA_CM.PK_CASE_LOG) violated" exceptions in the rsa/logs/aa_scheduler log.
 

ERROR [scheduler_Worker-3] ActiveCaseDaoImpl -
— The error occurred in dbconfig/oracle/mappings/ActiveCase.xml.
— The error occurred while applying a parameter map.
— Check the insertCaseLogBulk-InlineParameterMap.
— Check the statement (update failed).
— Cause: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (RSA_CM.PK_CASE_LOG) violated

com.ibatis.common.jdbc.exception.NestedSQLException:
— The error occurred in dbconfig/oracle/mappings/ActiveCase.xml.
— The error occurred while applying a parameter map.
— Check the insertCaseLogBulk-InlineParameterMap.


 
CauseThere could be two possibilities for this error to occur:

First, the expired cases move in the following order. The possibility can be after inserting the cases from active_cases to case_log (insertCaseLogBulk) the delete statement (removeExpiredCaseBulk) failed due to some DB issue and when again the insert statement ran it started throwing unique constraint error because records are already present in the case_log table.

sql.insert("insertCaseLogBulk", currentDBTime);
sql.insert("insertAttachmentBulk", currentDBTime);
sql.delete("removeExpiredAttachmentBulk", currentDBTime);
sql.delete("removeExpiredCaseBulk", currentDBTime);

The second possibility may be two threads (maybe two instances in the scheduler were switched on by the customer) are running at the same time and updating the same table with the same record.

Please execute the following query in Case Management database to determine any faulty records in ACTIVE_CASE table causing these exceptions

select * from RSA_CM.ACTIVE_CASE
where RSA_CM.Active_case.case_id in (select RSA_CM.case_log.case_log_id from RSA_CM.CASE_LOG);
ResolutionRequest the DBA to remove all the records from Case Management ACTIVE_CASE table returned by the query below.
For this particular customer, the query returned 4 problematic records causing this issue, once the 4 records were deleted from the ACTIVE_CASE table the issue is resolved.

select * from RSA_CM.ACTIVE_CASE where RSA_CM.Active_case.case_id in (select RSA_CM.case_log.case_log_id from RSA_CM.CASE_LOG);

Attachments

    Outcomes