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.

 

Suggest Edit

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.