000032962 - Review result fails to save the changes made in RSA Via Lifecycle and Governance with error ORA-00001: unique constraint (AVUSER.PK_T_REVIEW_COMP_CH_ID) violated error

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000032962
Applies ToRSA Product Set: Via Lifecycle and Governance (Via L&G)
RSA Product/Service Type: All
 
IssueAfter maintaining or revoking the review item, when you try to save or sign off the changes, it will fail to save the changes with the error shown below:
User-added image
The text of the error is:
 

Error editing the Review

Failed to save the review components.

You will observe the error below in the aveksaServer.log  Accessing the aveksaServer.log file for RSA Lifecycle and Governance (L&G):
ERROR (SaveReviewChangesThread11757-14313266) [com.aveksa.gui.pages.review.review.newEdit.SaveReviewChangesThread]
Failed to signoff review components
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
Caused by: com.aveksa.server.db.PersistenceException: insert into T_AV_REVIEW_COMP_CHANGES
("REVIEW_COMPONENT_ID", "CREATION_DATE", "REVIEWER", "STATE", "COMMENTS", "EXPIRATION_DATE",
"OPERATION_TYPE", "COMMITTED_HISTORY_ID", "ROLE_TYPE", "ID") values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
at com.aveksa.server.db.persistence.PersistenceServiceProvider.processInGivenTXUsingUpdate
(PersistenceServiceProvider.java:1905)
at com.aveksa.server.db.PersistenceManager.processInGivenTXUsingUpdate
(PersistenceManager.java:312)
at com.aveksa.server.review.Review.savePassedReviewComponent(Review.java:2221)
... 3 more
------------------------------------------------------------------------------------------------------------------
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint
(AVUSER.PK_T_REVIEW_COMP_CH_ID) violated

CauseExecute the following queries on the database with the avuser[database user] login using SQL Developer or through a remote connection and using the “sqlplus username/password” command.
SELECT max(id) FROM t_av_review_comp_changes;
SELECT REVIEW_COMP_CHANGE_SEQUENCE.nextVal FROM dual;

You will find the value returned from the second query is smaller than the value from first one. The value from second query should be ideally greater than or equal to the value from the first. The max IDs need to be in sync with the sequence.

 
Resolution
  1. After executing the queries above, execute query in step 2 using the avuser[database user] login using
  • SQL Developer; or
  • A remote connection and using “sqlplus username/password” command.
  1. If the result of the second query is less than the value of the results of the first query, increment the sequence as required.  To do this, execute the query below:
ALTER SEQUENCE REVIEW_COMP_CHANGE_SEQUENCE INCREMENT BY <value>;

Increment the REVIEW_COMP_CHANGE_SEQUENCE by a value so that it becomes more than value shown from the first query.  For example,

ALTER SEQUENCE REVIEW_COMP_CHANGE_SEQUENCE INCREMENT BY 100;

  1. Restart the server after running the ALTER command since sequence values are cached.

Attachments

    Outcomes