Issue Summary

The current logic implemented for the "UpgradeTask_Build960000" upgrade task doesn't consider different database engine limitations. It uses the default 50000 batch size for SQL queries executed within the scope of this task which exceeds the MSSQL limitation of 2100 parameters per request. As a result for environments running Jira on MSSQL execution of this upgrade task will fail.

Steps to Reproduce

1. Create a Jira instance with more than 2100 archived issues and MSSQL as a database.
2. Try to upgrade Jira 9.6 (or lower) to the latest 10.3.
   • Upgrading from Jira versions higher than 9.6 (i.e. 9.7+) will not trigger this trouble as the appropriate upgrade task will not be invoked (it should be already executed during the preliminary upgrade)

Expected Results

Upgrade completes successfully

Actual Results

Upgrade fails for "UpgradeTask_Build960000" task with the following exception on MSSQL-based environments:

2025-01-06 14:55:58,007+0100 upgrade-960000-watchdog INFO      [c.a.j.upgrade.tasks.UpgradeTask_Build960000] Stopping monitoring of upgrade task by upgrade-960000-watchdog
2025-01-06 14:55:58,007+0100 Caesium-1-1 ERROR Anonymous user     [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=960000] failed
java.lang.RuntimeException: Error running original upgrade task
...
Caused by: com.querydsl.core.QueryException: Caught SQLServerException for update dbo.jiraissue
set archived = ?
where jiraissue.id in (...trimmed...includes 2100+ parameters...)
...
	at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
	at com.querydsl.sql.Configuration.translate(Configuration.java:507)
	at com.querydsl.sql.dml.AbstractSQLUpdateClause.execute(AbstractSQLUpdateClause.java:213)
	at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build960000.lambda$doUpgrade$2(UpgradeTask_Build960000.java:123)
...
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)

Workaround

1. Intermediate upgrade. The recent change that introduced a flaw in upgrade task logic was introduced only recently and affected only the latest Jira release. You can upgrade to some intermediate version first that is higher than 9.6.0 and lower than affected versions. This should skip the upgrade task for further upgrade and the customer should be able to proceed with the 10.3.1 upgrade next. Verified upgrade paths:
   • 9.4.2 > 9.7.0 > 10.3.1
   • 9.4.2 > 9.12.15 > 10.3.1
   • 9.4.2 > 10.1.2 > 10.3.1
2. Manual data update in DB. As an alternative solution, you can try to manually execute the equivalent of the SQL query generated by this task before the current logic is implemented.

   UPDATE jiraissue
   SET archived = 'Y'
   WHERE project IN (
       SELECT entity_id
       FROM propertyentry
       LEFT JOIN propertynumber ON propertyentry.id = propertynumber.id
       WHERE propertyentry.property_key = 'jira.archiving.projects'
       AND propertynumber.propertyvalue = 1
   )
   AND (archived = 'N' OR archived IS NULL);
   

The safest approach in such a situation would be performing an intermediate upgrade as it is a supported way that is not expected to introduce any regressions. In case you decide to go with a manual SQL data update approach, we would highly recommend making sure you have a fresh and valid backup of all data before running this step and that workaround is tested/verified in some lower environment first to make sure it runs smoothly and doesn't introduce any regression.