Plans missing in Advanced Roadmaps for Jira after updating SQL Server Collation

Still need help?

The Atlassian Community is here for you.

Ask the community

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. 


Last modified on May 13, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.