Upgrade pega 8.6 to 8.8.4 Issue:cursor with the name 'TableCursor' does not exist
I'm currently dealing with the following challenge. Could you please share any insights or experiences you might have on this matter?
We are trying upgrade pega 8.6.3 to page 8.8.4 and facing issues.
Calculate Table Stats Inner:
[echo] Calculating Database Table Statistics
[echo] Executing: EXECUTE PegaRules.sppr_rebuild_indexes 'stats'
[pega:sql] Unable to execute sql query:
[pega:sql] EXECUTE PegaRules.sppr_rebuild_indexes 'stats'
[pega:sql] com.microsoft.sqlserver.jdbc.SQLServerException: A cursor with the name 'TableCursor' does not exist
----------------------------------------------------------------------------------
BUILD FAILED
/tmp/pega88/scripts/setupDatabase.xml:354: The following error occurred while executing this line:
/tmp/pega88/scripts/setupDatabase.xml:3028: Unable to execute sql query:
EXECUTE PegaRules.sppr_rebuild_indexes 'stats'
I have checked following but issue seems bit different also not sure if script is correct.
***Edited by Moderator Marije to add CASE tags***
Accepted Solution
Updated: 23 May 2024 4:38 EDT
Pegasystems Inc.
GB
@Sutanu it looks like your logged a support ticket on 7th May - INC-B18475 (issue during upgrade from 8.6 to 8.8.4 sppr_rebuild_indexes)
==================
Closure details:
Client doing in-place upgrade from 8.6 to 8.8.4 on sql server database.
Hit known issue with a bug in the sppr_rebuild_indexes stored procedure that does not handle special characters within the database, schema name or table name correctly.
The article to tell client how to work around this issue for the upgrade needs to be modified to provide the correct properties needed for the work around as well as how client can manually edit the stored procedure to execute correctly during runtime. Article is being updated for that.
Issue primary reason description:
The sppr_rebuild_indexes stored procedure has a bug which does not escape object names which could include special characters. There is an article that was written for how to work around this issue but the article had several typos in it that need to be updated. The bug is in the stored procedure itself and this article is talking about how to correct the stored procedure and also how to work around the bug for the upgrade.
@Sutanu it looks like your logged a support ticket on 7th May - INC-B18475 (issue during upgrade from 8.6 to 8.8.4 sppr_rebuild_indexes)
==================
Closure details:
Client doing in-place upgrade from 8.6 to 8.8.4 on sql server database.
Hit known issue with a bug in the sppr_rebuild_indexes stored procedure that does not handle special characters within the database, schema name or table name correctly.
The article to tell client how to work around this issue for the upgrade needs to be modified to provide the correct properties needed for the work around as well as how client can manually edit the stored procedure to execute correctly during runtime. Article is being updated for that.
Issue primary reason description:
The sppr_rebuild_indexes stored procedure has a bug which does not escape object names which could include special characters. There is an article that was written for how to work around this issue but the article had several typos in it that need to be updated. The bug is in the stored procedure itself and this article is talking about how to correct the stored procedure and also how to work around the bug for the upgrade.
Explanation description:
Ensure that the stored procedure sppr_rebuild_indexes is not called from an update and manually correct and run the stored procedure before and after the update:
Ensure that the stored procedure is not called from the update. See Preventing the stored procedure from being called during the update process.
- completed.steps.CalculateTableStats=true
- calculate.table.stats=false
- rebuild.indexes=false
For the correction to the stored procedure it is just this one line that needs to be updated to add the bracket escaping the names. FROM: SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName FROM INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'' AND table_name IN
TO: SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name as tableName + '']'' FROM INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'' AND table_name IN
==============================
This followed on with an unrelated new issue which has been logged in INC-B19406 (Startup on 8.8.4 failing with NoSuchMethodError). (This has since been resolved when it was identified that you were using the "cluster/hazelcast/v4/enabled" setting and this jar was missing in your env. After you changed the setting to v5 and did a full restart of the environment (shut down all the nodes and be sure that nothing is working for 10 sec) it flushed the memory and a restart showed this had fixed the problem).
For the original issue I will mark this as the Accepted Solution.