Support Article
Agents do not start after upgrade - unique constrant error
SA-30204
Summary
Administrator notices an issue with Pega agents failing to start up, including: License Compliance, Master-for-Agent, Master-for-Requestors, Node, Passivation and Usage.
Error Messages
2016-10-23 11:42:10,027 [.PRPCWorkManager : 4] [ STANDARD] [ <your_app>] ( internal.async.Agent) ERROR - Commit threw an exception, aborting Rule-Agent-Queue lookup
com.pega.pegarules.pub.database.DuplicateKeyException: Database-Saver-UpdateInsertfail Index-Reference 1 23000 ORA-00001: unique constraint (<your_schema>.PR_INDEX_REFERENCE_PK) violated
DatabaseException caused by prior exception: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (<your_schema>.PR_INDEX_REFERENCE_PK) violated
| SQL Code: 1 | SQL State: 23000
DatabaseException caused by prior exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (<your_schema>.PR_INDEX_REFERENCE_PK) violated
| SQL Code: 1 | SQL State: 23000
From: (BB4768B55E026A639FCFCEBC7E3D0FD47:(Master For New Agents))
SQL: INSERT INTO <your_schema>.pr_index_reference (pzInsKey , pzTenantId , pxCommitDateTime , "PXCREATEDATETIME" , "PXCREATEOPERATOR" , "PXCREATEOPNAME" , "PXCREATESYSTEMID" , "PXINDEXCOUNT" , "PXINDEXPURPOSE" , "PXINSINDEXEDCLASS" , "PXINSINDEXEDKEY" , "PXINSNAME" , "PXJAVACLASS" , "PXJAVAMETHODNAME" , "PXOBJCLASS" , "PXPAGENAME" , "PXREFERENCINGDESCRIPTION" , "PXREFERENCINGINSNAME" , "PXREFERENCINGRULESETNAME" , "PXREFERENCINGRULESETVERSION" , "PXRULECLASSNAME" , "PXRULEFAMILYNAME" , "PXRULEOBJCLASS" , "PXSAVEDATETIME" , "PXUPDATEDATETIME" , "PXUPDATEOPERATOR" , "PXUPDATEOPNAME" , "PXUPDATESYSTEMID" , "PYLABEL" , "PYRULENAME") VALUES (? , COALESCE((SELECT MAX(pzTenantId) FROM PEGA_UPG_RULES721.pr_index_reference WHERE PZINSKEY = ? GROUP BY (PZINSKEY)), 'shared') , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
SQL Inserts: <INDEX-REFERENCE DATA-AGENT-QUEUE PEGA-PROCOM <...your data inserts> >
Caused by SQL Problems.
Problem #1, SQLState 23000, Error code 1: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (<your_schema>.PR_INDEX_REFERENCE_PK) violated
Problem #2, SQLState 23000, Error code 1: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (<your_schema>.PR_INDEX_REFERENCE_PK) violated
2016-10-23 11:42:10,029 [.PRPCWorkManager : 4] [ STANDARD] [ <your_app>] ( internal.async.Agent) WARN - Unable to perform background processing due to a critical failure; there will be no attempts to execute processing for the next 600 seconds.
2016-10-23 11:52:10,030 [PRPCWorkManager : 13] [ STANDARD] [ <your_app>] ( internal.async.Agent) INFO - System date: Sun Oct 23 11:52:10 EDT 2016 Total memory: 4,294,967,296 Free memory: 3,419,987,936 Requestor Count: 10 Shared Pages memory usage: 0%
Steps to Reproduce
Not Applicable
Root Cause
A defect in Pegasystems’ code or rules. There was data left in tables from a PRPC 6.x upgrade causing duplicate key errors.
Resolution
Perform the following local-change:
Remove old entries from the PR_INDEX_REFERENCE table to resolve the issue.
Published December 19, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.