Details
-
Bug
-
Resolution: Fixed
-
Low
-
8.20.0
-
8.2
-
2
-
Severity 2 - Major
-
1
-
Description
When trying to create an Advanced Roadmaps Program It fails to show the Plans that you want to add to the Program.
Also listing existing plans (Plans > View Plans), will not show any plans, just the Programs list.
Environment:
Jira DC
ARJ 3.29
DB: MSSQL
Enabling roadmaps debugging logs:
com.radiantminds com.atlassian.rm com.atlassian.jpo
It's possible to see the cause of the error is coming from DB:
/secure/PortfolioProgramCreate.jspa [c.a.r.j.core.permissions.BasePermissionService] exception while checking permission for 'PLAN_PERMISSION - [6, 10, 12, 21, 27, 28, 36, 38, 39, 40, 41, 42, 43, 47, 51, 53, 58, 69, 70, 71, 83, 84, 86, 88, 93, 94, 96, 100, 101, 102, 103, 107, 110, 113, 124, 132, 133, 134, 135, 137, 138, 155, 157, 158, 159, 160, 161, 163,... ..... com.querydsl.core.QueryException: Caught SQLServerException for select "pp"."HOLDER_TYPE", "pp"."HOLDER_KEY", "pp"."PERMISSION", "pp"."PLAN_ID" from "dbo"."AO_D9132D_PERMISSIONS" "pp" where ("pp"."PLAN_ID" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ... ..... 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:256) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
Steps to Reproduce
- Add more than 2100 entries to the AO_D9132D_PERMISSIONS table.
- Try to create a Program
- Will fail to show all the plans the user has access to.
Expected Results
Jira Advanced Roadmap should display all the plans the user has access
Workaround.
For instances on versions 8.20.x and later, enabling the dark feature flag below can alleviate some of the problems:
com.atlassian.portfolio.permissions.optimisedPermissionLoading
- Some path may still trigger the issue as the feature flag doesn't effect 100% of the cases but the main ones are covered.
- It does not require a Jira restart to take effect.
An alternative is to delete Plans so they amount to below 2,100.
Refer to this KB article for one way to accomplish this:
You can monitor the progress of Plan deletions in the logs by grepping the atlassian-jira.log for:
grep -E -o ".*PLAN_PERMISSION - \[" atlassian-jira.log
And picking a specific records' timestamp, like 2023-09-28 10:56:11:
grep -E "2023-09-28 10:56:11.*PLAN_PERMISSION - \[" atlassian-jira.log | grep -E -o "PLAN_PERMISSION - \[[0-9, ]+\]" | sed -E 's/, /\n/g' | wc -l
It'll print the number of Plans in the line. They should decrease as you delete Plans. Once below 2,100, the line won't be logged anymore.