Clean schema before
When I ran Pega 7.2 update from 7.1.7, I got a sql error 'clean schema before running again'. I am not sure how many such errors I might encounter. The refers to an empty table. What is the process/step to clean the schema. Is there a way to proactively identify the potential sql errors and rectify them before running the update?
Accepted Solution
Updated: 26 Jan 2017 11:48 EST
Upon reviewing the corresponding SR, we see that it has been resolved. SA-22713 was created as a result of the investigation. Please reference that if you have the same question.
Pegasystems Inc.
IN
Hi
Have you checked this SA item : https://community.pega.com/support/support-articles/migrate-upgraded-71-rules-schema-new-database-failing
Pegasystems Inc.
US
Can you post the full upgrade log file so we can see why the update failed? What you should do depends on why the update failed, what step you are on, etc..
Hawaii Medical Service Association
US
Hi Celeste,
Please find below:
Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: File: mssql00490.sql
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: ALTER TABLE DBO.PR_PAGE_STORE
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: File: mssql00500.sql
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: ALTER TABLE DBO.PR_PAGE_STORE
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeException
[java] WARNING: Failed to execute SQL in file mssql00500.sql
[java] Throwable occurred: com.microsoft.sqlserver.jdbc.SQLServerException: 'pr_page_store_PK' is not a constraint.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
Hi Celeste,
Please find below:
Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: File: mssql00490.sql
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: ALTER TABLE DBO.PR_PAGE_STORE
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: File: mssql00500.sql
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] INFO: ALTER TABLE DBO.PR_PAGE_STORE
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeException
[java] WARNING: Failed to execute SQL in file mssql00500.sql
[java] Throwable occurred: com.microsoft.sqlserver.jdbc.SQLServerException: 'pr_page_store_PK' is not a constraint.
[java] at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
[java] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
[java] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
[java] at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:662)
[java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:266)
[java] Mar 25, 2016 9:43:36 AM com.pega.pegarules.install.SQLLoader writeMessage
[java] WARNING: Statement: ALTER TABLE dbo.pr_page_store
[java] DROP CONSTRAINT pr_page_store_PK
[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)
Pegasystems Inc.
US
I need to see more than this. Earlier in the log file we will see the current structure of the DBO.PR_PAGE_STORE table. We need to see if the tool correctly identified pr_age_store_pk as the primary key and if so why the database is failing when we try to drop it. It could be that it is dropping things in the wrong order or something else. In the media/schema/generated/mssql/update directory there will be a mssql.sql file. Please post that as well. We can probably alter the DDL, have you apply it manually, and then run the update again bypassing ddl generation to get past this but I also want to know the details to see if this is because of something wrong in how you ran the tool or if this a bug in the update tool we would want to make sure that we open a bug item to get it fixed. If you want to do this via SR instead of posting your logs/generated DDL here we can do that too.
Pegasystems Inc.
US
Please attach E:\Pega7.2UPDATE\115042_Pega72UP\scripts/../schema/generated/mssql/mssql.sql file to the SR.
Accepted Solution
Updated: 26 Jan 2017 11:48 EST
Upon reviewing the corresponding SR, we see that it has been resolved. SA-22713 was created as a result of the investigation. Please reference that if you have the same question.
Updated: 26 Jan 2017 11:49 EST
Pegasystems Inc.
US
I will update the SA link from Lochana since it has since changed. I have pasted the Resolution from the SA below:
Perform the following local-change:
1. Obtain modified SQL file from GCS which removes the statements trying to add the existing constraints and index and rearrange the order.
2. Ensure that the DBA applies the modified SQL.
3. Continue with the update by setting bypass.pega.schema=true in setupDatabase.properties file and restarte the update script.