Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Atanu Sen (Atanu Sen)
Virtusa Consulting and Services Pty Ltd
Principal Architect
Virtusa Consulting and Services Pty Ltd
AU
Atanu Sen Member since 2013 16 posts
Virtusa Consulting and Services Pty Ltd
Posted: Feb 7, 2023
Last activity: Jun 18, 2024
Posted: 7 Feb 2023 8:03 EST
Last activity: 18 Jun 2024 13:58 EDT
Closed
Solved

Pega 8.5.1 to 8.8.1

getting the below error in update logs and update failing:

Show More

getting the below error in update logs and update failing:

[java] 2023-02-07 14:08:14,833 (                  SQLLoaderInternal)    INFO - File: oracledate032.sql
     [java] 2023-02-07 14:08:14,833 (                  SQLLoaderInternal)    INFO - ALTER TABLE PRPCDATA.PR_DATA
     [java] 2023-02-07 14:08:15,493 (                  SQLLoaderInternal)    INFO - Failed to execute SQL in file oracledate032.sql
     [java] 
     [java] java.sql.SQLException: ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated
     [java] 
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
     [java]     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
     [java]     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
     [java]     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
     [java]     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
     [java]     at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
     [java]     at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
     [java]     at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
     [java]     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
     [java]     at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
     [java]     at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
     [java]     at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119)
     [java]     at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99)
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66)
     [java] Caused by: Error : 2437, Position : 44, Sql = ALTER TABLE PRPCDATA.pr_data
     [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY
     [java] (
     [java]     "PZINSKEY"
     [java] ), OriginalSql = ALTER TABLE PRPCDATA.pr_data
     [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY
     [java] (
     [java]     "PZINSKEY"
     [java] ), Error Msg = ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated
     [java] 
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
     [java]     ... 17 more
     [java] 2023-02-07 14:08:15,493 (                  SQLLoaderInternal) WARNING - Statement: ALTER TABLE PRPCDATA.pr_data
     [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY
     [java] (
     [java]     "PZINSKEY"
     [java] )
     [java] 2023-02-07 14:08:15,493 (                  SQLLoaderInternal) WARNING - 
     [java] ********************************************************************************
     [java] 
     [java] Encountered a SQL Exception, clean schema before running again.
     [java] 
     [java] ********************************************************************************
     [java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:81)
     [java]     at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66)
     [java] Caused by: java.sql.SQLException: ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated
     [java] 
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
     [java]     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
     [java]     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
     [java]     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
     [java]     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
     [java]     at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
     [java]     at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
     [java]     at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
     [java]     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
     [java]     at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
     [java]     at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
     [java]     at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160)
     [java]     at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119)
     [java]     at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99)
     [java]     at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78)
     [java]     ... 1 more
     [java] Caused by: Error : 2437, Position : 44, Sql = ALTER TABLE PRPCDATA.pr_data
     [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY
     [java] (
     [java]     "PZINSKEY"
     [java] ), OriginalSql = ALTER TABLE PRPCDATA.pr_data
     [java] ADD CONSTRAINT "PR_DATA_PK" PRIMARY KEY
     [java] (
     [java]     "PZINSKEY"
     [java] ), Error Msg = ORA-02437: cannot validate (PRPCDATA.PR_DATA_PK) - primary key violated
     [java] 
     [java]     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
     [java]     ... 17 more
 

In-place upgrade on existing schema. 

Show Less
***Edited by Moderator Marissa to add Capability tags***
To see attachments, please log in.
Pega Platform 8.8.1
Updates

Accepted Solution

Posted: 3 years ago
Updated: 2 years ago
Posted: 7 Feb 2023 8:28 EST
Updated: 2 Jun 2023 8:16 EDT
Marije Schillern (MarijeSchillern)
MOD
Senior Knowledge Management Specialist
Pegasystems Inc.
GB

@Atanu Sen  this sounds like on a DataOnly update there are Duplicate pzinskey values present in table pr_data .

 when running:

select pzinskey from pr_data order by pzinskey;

You should fix the source system by removing the duplicate entries, reapply the PK consttraint and redo your upgrade.

Other scenarios:  if a PK index on a shipped table may have been dropped in the source system, and that this allowed for the creation of duplicate entries in the pr4_rule_sysgen table. When the upgrade is attempted, the upgrade rightly applies the shipped index which fails due to the table inconsistent data.

Perform the below changes:-

1. Run the below query to get the duplicate pzinskey.

select pzinskey from pr_data group GROUP BY pzinskey 
HAVING COUNT(*) > 1; 

2.Then remove the duplicate entries from the table and resume the upgrade process.

To see attachments, please log in.
Posted: 1 year ago
Updated: 1 year ago
Posted: 18 Jun 2024 13:57 EDT
Updated: 18 Jun 2024 13:58 EDT
Vivek Bommayappan (VIVEKATCTS)
Cognizant

Cognizant
CA

@MarijeSchillern 

Pega patch update issues:

Thanks. This helped to resolve my issue. I used the below queries to fix this issue with pr_data & PR_LOG tables.

Find duplicates:

select pzinskey from data.PR_LOG GROUP BY pzinskey  HAVING COUNT(*) > 1;  

Delete Duplicates:

delete from  data.PR_LOG a WHERE ROWID > (SELECT MIN(ROWID) FROM data.PR_LOG b WHERE b.pzInsKey=a.pzInsKey);  ()
 

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice