Discussion

1
Replies
341
Views
Shankhabrata Member since 2013 20 posts
Accenture
Posted: January 5, 2016
Last activity: April 6, 2018
Closed

PR4_LOG_RULE_USAGE - Duplicate row issue

Problem:

During running of the 2nd Upgrade (DataOnly upgrade) which is basically the 4th steps of the Upgrade Script execution process, below error is coming and the process get stopped.

:17,504 (SQLLoader) INFO - ALTER TABLE *****.PR4_LOG_RULE_USAGE

[java] 2016-01-04 08:01:17,859 (SQLLoader) WARNING - Failed to execute SQL in file oracledate02410.sql

[java]

[java] java.sql.SQLException: ORA-02437: cannot validate (******.PR4_LOG_RULE_USAGE_PK) - primary key violated

[java]

[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)

[java] at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

[java] at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)

[java] at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)

[java] at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)

[java] at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)

[java] at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:195)

[java] at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1036)

[java] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)

[java] at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1916)

[java] at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1878)

[java] at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:318)

[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266)

[java] 2016-01-04 08:01:17,863 ( SQLLoader) WARNING - Statement: ALTER TABLE ******.pr4_log_rule_usage

[java] ADD CONSTRAINT pr4_log_rule_usage_PK PRIMARY KEY

[java] (

[java] pzInsKey

[java] )

[java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException:

[java] ********************************************************************************

[java]

[java] Encountered a SQL Exception, clean schema before running again.

[java]

[java] ********************************************************************************

[java] at com.pega.pegarules.install.SQLLoader.fail(SQLLoader.java:359)

[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:316)

Root Cause:

During the DataOnly upgrade, the Upgrade Script generate and apply many DDL SQL statements for data schema, among those there is one statement which create Primary key constrain in the pr4_log_rule_usage table in the data schema.

The statement is like below

ALTER TABLE <data schema>.pr4_log_rule_usage ADD CONSTRAINT pr4_log_rule_usage_PK PRIMARY KEY (pzInsKey);

we were getting “cannot validate (******.PR4_LOG_RULE_USAGE_PK) - primary key violated” error while running below statement and data only upgrade was failing, because in the Data schema which was copied from the existing 6.x environment, the table PR4_LOG_RULE_USAGE already contain some duplicate records which was not allowing to create primary key.

Solution:

We deleted duplicate records from the table PR4_LOG_RULE_USAGE. Once done we executed dataOnly upgrade again and it got executed successfully.

So it is recommended that in Lift and Shift approach, as pre upgrade steps truncate the PR4_LOG_RULE_USAGE table.

Truncating this table before the running of upgrade script will not be an issue, because this table does not holds any business transaction, this table holds statistics collected during system shutdown about cumulative rule execution statistics associated with theLog-RuleUsage class

Regards

Shankha

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Low-Code App Development Upgrades
Moderation Team has archived post
Share this page LinkedIn