Plans missing in Advanced Roadmaps for Jira after updating SQL Server Collation
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Problem
Plans are missing in Advanced Roadmaps for Jira (formerly Portfolio) for users after updating the database collation following steps in How to fix the collation of a Microsoft SQL Server JIRA database article. The Plan continues to be listed in the database table AO_D9132D_PLAN
The following appears in the atlassian-jira.log
2018-04-20 21:26:25,995 https-jsse-nio-443-exec-9 url:/secure/PortfolioPlanView.jspa username:userabc ERROR [o.a.c.c.C.[.[localhost].[/].[action]] Servlet.service() for servlet [action] in context with path [] threw exception [com.atlassian.rm.common.basics.validation.DataValidationException: number must be positive: Optional.of(0)] with root cause
com.atlassian.rm.common.basics.validation.DataValidationException: number must be positive: Optional.of(0)
at com.atlassian.rm.common.basics.validation.Validation$Numbers.positive(Validation.java:119)
at com.atlassian.rm.jpo.core.plan.data.DefaultPlan.<init>(DefaultPlan.java:94)
at com.atlassian.rm.jpo.core.plan.data.DefaultPlan.<init>(DefaultPlan.java:10)
at com.atlassian.rm.jpo.core.plan.data.DefaultPlan$Builder.build(DefaultPlan.java:420)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence.createPlanFromTuple(QueryDslPlanPersistence.java:713)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence.createDeepPlanFromTuple(QueryDslPlanPersistence.java:615)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence.access$800(QueryDslPlanPersistence.java:56)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence$8.apply(QueryDslPlanPersistence.java:566)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence$8.apply(QueryDslPlanPersistence.java:491)
at com.atlassian.rm.common.persistence.DefaultDatabaseProvider$1.perform(DefaultDatabaseProvider.java:30)
at com.atlassian.rm.common.persistence.env.JiraDatabaseProvider$1.perform(JiraDatabaseProvider.java:41)
at com.atlassian.rm.common.bridges.jira.persistence.JiraDatabaseProviderBridge63.execute(JiraDatabaseProviderBridge63.java:66)
at com.atlassian.rm.common.persistence.env.JiraDatabaseProvider.execute(JiraDatabaseProvider.java:34)
at com.atlassian.rm.common.persistence.DefaultDatabaseProvider.run(DefaultDatabaseProvider.java:26)
at com.atlassian.rm.jpo.core.plan.sql.QueryDslPlanPersistence.get(QueryDslPlanPersistence.java:491)
at com.atlassian.rm.jpo.core.plan.DefaultPlanService.getPlan(DefaultPlanService.java:319)
at com.atlassian.rm.jpo.plugin.views.plan.common.CachedPlanInformationComponent.load(CachedPlanInformationComponent.java:57)
at com.atlassian.rm.jpo.plugin.views.plan.view.standard.PlanView.execute(PlanView.java:120)
...Diagnosis
Environment
- SQL Server as database
Diagnostic Steps
- Validate the plans are listed in AO_D9132D_PLAN table by running the following SQL query:
SELECT * FROM AO_D9132D_PLAN;- Comparing values for the following columns suggests values are updated from NULL to zeros.
Cause
AO_D9132D_PLAN table has columns that have a default value for the column to be either (0) or (0,0). The steps in How to fix the collation of a Microsoft SQL Server JIRA database article take into consideration of default value zero instead of the NULL data from the other table.
Resolution
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
To proceed, please engage with Database Administrator to help compare and execute the fix.
- The idea is to identify the differences of values for BEFORE and AFTER for the AO_D9132D_PLAN table. Update to the AFTER table when there are value differences.
- Please Contact Support if there are any questions.