SA-27395

Summary

Work Objects are getting deleted intermittently with Pega 7.1.8.

Error Messages

Below is log snippet
 
2016-08-08 02:16:09,100 [   PegaRULES-Batch-1] [  STANDARD] [     abcFW:01.01.01] (WithErrorHandling.Work_.Action) ERROR  abc2rty -  Error in commit()
com.pega.pegarules.pub.database.DuplicateKeyException: Database-Saver-Insertfail
From: (BD8E0D06C66E7A64E27B871E8F36F895C)
               SQL: INSERT INTO PEGADATA.pc_History_abc_FW_abFW_WORK (pzInsKey , pxCommitDateTime , PXADDEDBYID , PXADDEDBYSYSTEM , PXASSIGNMENTELAPSEDTIME , PXASSIGNMENTPASTDEADLINE , PXASSIGNMENTPASTGOAL , PXCOVERINSKEY , PXEFFORTACTUALDELTA , PXFLOWACTION , PXHISTORYFORREFERENCE , PXINSNAME , PXLATITUDE , PXLONGITUDE , PXOBJCLASS , PXRESOLUTIONCOSTDELTA , PXTASKELAPSEDTIME , PXTIMECREATED , PXTIMEFLOWSTARTED , PYASSIGNEDTO , PYASSIGNEDTOWORKGROUP , PYASSIGNMENTCLASS , PYFLOWKEY , PYFLOWNAME , PYFLOWTYPE , PYHISTORYTYPE , PYLABEL , PYMEMO , PYMESSAGEKEY , PYPERFORMACTIONTIME , PYPERFORMASSIGNMENTTIME , PYPERFORMER , PYPERFORMTASKTIME , PYSUBSTITUTIONVALUES , PYTASKID , PYTASKNAME , PYWORKCLASS , pzPVStream) VALUES (? , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
               SQL Inserts: <HISTORY-abc-FW-abFW-WORK abDC-FW-abFW-WORK!20160808T021607.849 GMT>        <abc>      <xyzProd>     <<null>>               <<null>>               <<null>>               <<null>>               <<null>>               <<null>>               <abc-FW-abcFW-WORK>      <abDC-FW-abFW-WORK!20160808T021607.849 GMT>    <<null>>               <<null>>               <History-abDC-FW-abFW-Work>         <<null>>               <<null>>               <2016-08-08 02:16:07.849>          <<null>>               <<null>>               <<null>>               <<null>>               <<null>>               <pyCreateSubCases>       <<null>>               <<null>>               <<null>>               <Ran pxAddChildWork>  <Ran pxAddChildWork>               <<null>>               <<null>>               <abc2rty>      <<null>>               <<null>>               <<null>>               <<null>>               <<null>>               <<stream>>
 
Caused by SQL Problems.
Problem #1, SQLState 23000, Error code 1: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (PEGADATA.HISTORY_abDC_FW_abcFW_WORK_PK) violated
 
 

Steps to Reproduce

1) Create a Work Object using  SOAP Request.
2)  Associated Workobject Flow gets started
3) Configure an integration with the external systems in the flow which sends a response to the external system with the primary or Cover case Id details.
4) External system sends acknowledgment to the PRPC application regarding the primary case Id details.
5) Search for the Primary case details in the application however it is not found as the case is deleted from the application.
 
 

Root Cause

Primary case creation is failing when there is an issue with inserting a History record while creating child work object using pxAddchildWork.

-  The reason behind the failure of the history entry of the child case creation is because of the activity pxAddChildWork does not use unique pagename for nested child case instead it uses a default page pyWorkPage
-   Because of unique key constraint issue while updating history for child case, System rolls back the process of primary case creation because there are no commits till the child case is comitted.  But before the primary case creation process is comlplete or comitted application is sending and receiving an acknowledgment of the primary case creation with the third party  and due to which when user searches for the primary case id it is not present in the application as it is already rolled back. 

-  While calling pxAddchildWork activity the child case details are present on the pagelist property but not on the pyWorkPage; check the below screen shot for code.
However while updating the history table application is checking for the child case details on pyWorkPage instead of pagelist property but as there is no data present on the pyWorkPage the history table is entered with null ID in pzInskey value for all the child cases.

but being on multinode environment sporadically while history entry is made the unique constraint exception is thrown as there is no child ID to differentiate the subcases  so application is unable to differentiate the child cases causing rollback of the complete case creation process that involves creation of primary case and also child case creation 

Note: This is not failure with the child case creation but problem while updating history record with the null ID in pzinskey values though the subcase or child IDs are already generated.












 

Resolution

Apply HFix-25425.