Support Article
Hotfix not working in production
SA-24953
Summary
Any HotFix installation fails with the DatabaseException ORA-00904: "PYSTATUS": invalid identifier on pr_data_admin table, after installing hybrid HFix-5601.
Error Messages
<Date> [http-bio-8181-exec-5] [-Installer] [ abcxy:01.07] (ga_importexport_pximportexport) ERROR <IP Address> XEAA29Z - Failed to count number of Instances for class - Data-Admin-Products-Installed-HFix
com.pega.pegarules.pub.database.DatabaseException: There was a problem getting a list: code: 904 SQLState: 42000 Message: ORA-00904: "PYSTATUS": invalid identifier
From: (HBC6C0387F26379688B6C6A52EF13FCB1)
SQL: select count(*) as "COUNT" from pr_data_admin where lower(pyStatus) = 'uncommitted'
SQL Inserts:
Caused by SQL Problems.
Problem #1, SQLState 42000, Error code 904: java.sql.SQLSyntaxErrorException: ORA-00904: "PYSTATUS": invalid identifier
at com.pega.pegarules.data.internal.access.ExceptionInformation.createExceptionDueToDBFailure(ExceptionInformation.java:167)
at com.pega.pegarules.data.internal.access.DatabaseImpl.list(DatabaseImpl.java:5876)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6253)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6203)
at com.pega.pegarules.data.internal.access.DatabaseImpl.executeRDB(DatabaseImpl.java:6184)
Steps to Reproduce
1. Install hybrid HFix-5601 without following prerequisites.
2. Try installing any HFix.
Root Cause
On installing any HFix, Rollback feature queries list of uncommitted HFixes from a table - PR_DATA_RPODUCTS. This table contains details of all uncommitted fixes in system.
However, in the user's system, the feature queries a different table - PR_DATA_ADMIN.
The reason for this mismatch is due to wrong class mapping of - Data-Admin-Products-Installed-HFix class. Because of this mismatch, the query fails and error is reported in log file.
Resolution
Perform the following two steps to resolve the issue: I. Execute the following sql script in oracle database
CREATE TABLE pr_data_products(
PXCREATEDATETIME DATE,
PXCREATESYSTEMID VARCHAR2 (32),
PXCREATEOPNAME VARCHAR2 (128),
PXCREATEOPERATOR VARCHAR2 (128),
PXINSNAME VARCHAR2 (128),
PXOBJCLASS VARCHAR2 (96),
PXUPDATEDATETIME DATE,
PXUPDATEOPNAME VARCHAR2 (128),
PXUPDATEOPERATOR VARCHAR2 (128),
PXUPDATESYSTEMID VARCHAR2 (32),
PYLABEL VARCHAR2 (128),
PYPRODUCTNAME VARCHAR2 (255),
PYPRODUCTVERSION VARCHAR2 (32),
PYPRODUCTID VARCHAR2 (32),
PYPARENTPRODUCTID VARCHAR2 (255),
PYSTATUS VARCHAR2 (16),
PYUNIQUEID VARCHAR2 (16),
PZINSKEY VARCHAR2 (255) NOT NULL,
PYRULESETNAME VARCHAR2 (64)
, CONSTRAINT pr_data_products_PK PRIMARY KEY (PZINSKEY)
)
/
CREATE TABLE pr_data_restore(
PXCREATEDATETIME DATE,
PXCREATESYSTEMID VARCHAR2 (32),
PXCREATEOPNAME VARCHAR2 (128),
PXCREATEOPERATOR VARCHAR2 (128),
PXINSNAME VARCHAR2 (128),
PXOBJCLASS VARCHAR2 (96),
PXUPDATEDATETIME DATE,
PXUPDATEOPNAME VARCHAR2 (128),
PXUPDATEOPERATOR VARCHAR2 (128),
PXUPDATESYSTEMID VARCHAR2 (32),
PYLABEL VARCHAR2 (64),
PYPARENTREFERENCE VARCHAR2 (255),
PYINSTALLORDER NUMBER (18),
PZINSKEY VARCHAR2 (255) NOT NULL,
PYRULESETNAME VARCHAR2 (64),
PZPVSTREAM BLOB
, CONSTRAINT pr_data_restore_PK PRIMARY KEY (PZINSKEY)
)
/
II. Create Database Table rule for class - Data-Admin-Products-Installed-HFix and map it to DB table - PR_DATA_RPODUCTS. This will link the class to correct Database table and fix the issue.
1. Navigate to Rule Explorer > System Admin > Database tables > New.
2. Provide Class name as - Data-Admin-Products-Installed-HFix
3. Select Pega Rules Data base as DB
4. Do not provide any value for Reports Database and Catalog Name fields.
5. Give appropriate Schema name.
6. Give table name as - PR_DATA_PRODUCTS.
7. Save the rule and try installing any fix.
Published July 16, 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.