Support Article
RuleSync does not happen in multiple nodes after Pega7 upgrade
SA-3506
Summary
A developer has completed an upgrade from PRPC62SP1 to PRPC716. The PRPC environment is multi-node. After completing the update, developers observe that rule changes made on one node do not propogate to the other nodes in the environment.
Error Messages
The following error messages are observed in the PRPC logs of all PRPC nodes:
2014-11-07 16:21:35,613 [ server1] [ STANDARD] [ ] [ ] (l.access.ConnectionManagerImpl) ERROR - Not returning connection 1232 for database "pegadata" to the pool as it previously encountered the following error
.
.
2014-11-07 16:21:35,633 [ server1] [ STANDARD] [ ] [ ] ( internal.access.DatabaseImpl) ERROR - [setDataNodeState] Exception while updating system state. Message: Database-General Problem encountered executing a stored procedure 1403 02000 ORA-01403: no data found
.
.
com.pega.pegarules.pub.database.DatabaseException: Database-General Problem encountered executing a stored procedure 1403 02000 ORA-01403: no data found
ORA-06512: at "DATA7.SPPR_SYS_UPDATESCACHE_GETTIME", line 8
ORA-06512: at line 1
From: (BE26E4B0904BCB79BA720F5361A706F94:(PREnvironment))
SQL: {call DATA7.sppr_sys_updatescache_gettime (?,?,?)}
SQL Inserts: <SYSTEM-STATUS-NODES DEV!NJ095334!F891F89B3DCF8E486C7C05E41CEBD7BA> <1969-12-31 19:00:00.0> <1969-12-31 19:00:00.0>
Caused by SQL Problems.
Problem #1, SQLState 02000, Error code 1403: java.sql.SQLException: ORA-01403: no data found
ORA-06512: at "DATA7.SPPR_SYS_UPDATESCACHE_GETTIME", line 8
ORA-06512: at line 1
.
.
2014-11-07 16:21:35,633 [ server1] [ STANDARD] [ ] [ ] ( internal.mgmt.PRNodeImpl) ERROR - Error setting initial system state.
.
.
2014-11-07 16:21:35,644 [ server1] [ STANDARD] [ ] [ ] ( internal.mgmt.PRNodeImpl) ERROR - [ Pulse - Indexer - Cleaner ] Disabling System Pulse, System Indexer and System Cleaner.
Steps to Reproduce
To reproduce this issue, perform the following:
1. Upgrade from PRPC 6.2 SP1 to 7.1.6 (split-schema).
2. Configure multiple PRPC nodes
3. Permit development to occur on all nodes.
4. Observe the condition.
Root Cause
User were getting “ORA-01403: no data found” while executing PRPC Stored Procedure ‘SPPR_SYS_UPDATECACHE_GETTIME’ that returns the last pulse time and the current database time. ORA-01403 was reported if the SELECT INTO statement doesn't return at least one row.
SELECT INTO clauses are standard SQL queries which pull a row or set of columns from a database, and put the retrieved data into variables which have been predefined.
In Pega7, we have below SELECT INTO query in the Stored Procedure with MIN keyword that returns at least a 'null' result even if there were no records found, in contrast with the following SQL statement from PRPC 6.x Stored Procedure where the MIN keyword is *not* specified and would not return any result, throws ORA-01403.
Pega7 version:
select min(pyLastPulseDateTime) into SPlastPulseTime from DATA7.pr_sys_statusnodes where pzInsKey = SPnodePZInsKey; SPlastPulseTime := nvl(SPlastPulseTime, to_date('19700101', 'yyyymmdd'));
Pega6 version:
select pyLastPulseDateTime into SPlastPulseTime from DATA7.pr_sys_statusnodes where pzInsKey = SPnodePZInsKey; SPlastPulseTime := nvl(SPlastPulseTime, to_date('19700101', 'yyyymmdd'));
This confirms that user has the Stored Procedure definition/code from previous PRPC version still in place under the Data schema instead of new one post the upgrade. User has the correct/latest Stored Procedure wrongly created in Rules schema instead of Data.
Resolution
Reported issue was resolved after user has deleted older version of the Stored Procedure - SPPR_SYS_UPDATECACHE_GETTIME from Data schema and moved the newer version of Stored Procedure (correct one) from their Rules schema to Data schema.
Published January 31, 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.