Scheme generation failure on privilege grant
SummaryWhen upgrading from PRPC 6.2 to Pega 7.2 and performing the step "Generate rules schema objects in New Schema" the build is failing with below message.
Error MessagesWARNING - Failed to execute SQL in file oracledate02080.sql
[java] java.sql.SQLSyntaxErrorException: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself
[java] 2017-01-31 03:43:52,957 ( SQLLoader) WARNING - Statement: GRANT insert ON PEGADBO.pr_sys_updatescache TO pegadbo7
[java] Exception in thread "main" com.pega.pegarules.install.SQLLoader$SQLLoaderException:
[java] Encountered a SQL Exception, clean schema before running again.
[java] at com.pega.pegarules.install.SQLLoader.fail(SQLLoader.java:359)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:316)
/app0/uat/pega/pega72/scripts/migrateSystem.xml:697: Java returned: 1
Steps to ReproducePerform upgrade from 6.2 SP2 to 7.2.
A separate deployment user which is not an owner of either schema was not being used to perform the upgrade as recommended in the upgrade guide. Instead, the schema owner was given deployment user privileges. However, the install scripts assumes that the schema owners are NOT being used for the installation process, and therefore when GRANTing permissions to the schema owners, it's attempting to GRANT to itself, which produces a database exception in Oracle.
There are two possible solutions:
1. Use a deployment user which is not an owner of either schema and has the appropriate privileges according to the upgrade document.
2. Manually perform the creation of schema objects manually instead of having the script apply them. This means changing the following in migratesystem.properties for this step:
This will create SQL files to manually apply to your database under [InstallationMedia]/schema/generated/[DatabaseType] folder.
Published February 3, 2017