Agent reconnects in a loop if Report cannot be saved because of ORA-02291 FK_RH_AH violated
Article ID: 273194
Updated On:
Products
Issue/Introduction
An Agent reconnects in loop (disconnect and then reconnect) after receiving this error about the FK_RH_AH:
20230615/075349.551 - 119 U00003590 UCUDB - DB error: '23000', 'ORA-02291: integrity constraint (UC4I.FK_RH_AH) violated - parent key not found
20230615/075349.551 - 119 ', '2291', 'java.sql.SQLIntegrityConstraintViolationException'
20230615/075349.551 - 119 SQL Statement which caused this DB error:
20230615/075349.551 - 119 U02012075 Set bind parameter '1' of type 'Integer' to value 'RUNID_NUMBER'
20230615/075349.551 - 119 U02012075 Set bind parameter '2' of type 'Integer' to value '0'
20230615/075349.552 - 119 U02012075 Set bind parameter '3' of type 'Integer' to value '8000'
20230615/075349.552 - 119 U02012075 Set bind parameter '4' of type 'Integer' to value '15'
20230615/075349.552 - 119 U02012075 Set bind parameter '5' of type 'Integer' to value '0'
20230615/075349.552 - 119 U02012075 Set bind parameter '6' of type 'Integer' to value '1'
20230615/075349.552 - 119 U02012075 Set bind parameter '7' of type 'Integer' to value '0'
20230615/075349.552 - 119 U02012075 Set bind parameter '8' of type 'Timestamp' to value '2023-06-15 05:53:49.548'
20230615/075349.552 - 119 U02012075 Set bind parameter '9' of type 'String' to value 'PLOG'
20230615/075349.553 - 119 U02012075 Set bind parameter '10' of type 'Integer' to value '0'
20230615/075349.553 - 119 U02012075 Set bind parameter '11' of type 'Integer' to value '0'
20230615/075349.553 - 119 INSERT INTO RH (RH_AH_IDNR,RH_ARCHIVEFLAG,RH_BLKSIZE,RH_CLIENT,RH_DELETEFLAG,RH_FILEINDB,RH_FILEONAGENT,RH_TimeStamp1,RH_Type,RH_USERSLOGIN,RH_XMLFLAG) VALUES (?,?,?,?,?,?,?,?,?,?,?)
20230615/075349.553 - 119 U00003407 Client connection 'CP001#00000011' from 'xxx.xxx.xxx.xxx:yyyy' has logged off from the Server.
20230615/075349.554 - 36 U00003438 Error in connection 'Agent AGENTNAME'. 'org.eclipse.jetty.websocket.api.WebSocketException' function: Error 'Session closed'
20230615/075349.554 - 36 U00045014 Exception 'org.eclipse.jetty.websocket.api.WebSocketException: "Session closed"' at 'org.eclipse.jetty.websocket.common.WebSocketSession.outgoingFrame():350'.
20230615/075349.554 - 36 U00003430 The connection to Agent 'AGENTNAME' was not found.
20230615/075349.558 - 36 U00003397 Agent 'AGENTNAME' logged off (client connection='CP001#00000011').
After checking the database,it is found that the problem is due to the fact that the related entry is missing from the RH and AH table:
SQL used:
select * from ah join rh on rh.RH_AH_Idnr = ah.AH_Idnr where ah.AH_Idnr like 'RUNID_NUMBER';
The PLOG file stays in the Filesystem and if the agent starts it tries to process all PLOG files from the filesystem. The RUNID is not found any more in the AH table because of strict reorg settings for the AH table.
Environment
Release : 21.x
Component: Automation Engine
Cause
Defect: 'ORA-02291: integrity constraint (UC4I.FK_RH_AH) violated - parent key not found
This happens because the report task still exists in EH and triggers REP_GET when the agent starts but the RunId of the corresponding JOBS task does not exist anymore.
The JCP should handle this error case better and send back REP_QUIT with a message number to abort the report transfer.
Resolution
Update to a fix version listed below or a newer version if available.
Fix version:
Component(s): Automation Engine
Automation.Engine 21.0.8 HF1 - Available
Additional Information
Bug ID: AE-33192
Public Description: A problem has been fixed where an agent reconnects in a loop if a report cannot be created in the database because of foreign key constraint error "FK_RH_AH".
The JCP process wrote a forced trace and closed the agent connection.
Feedback
