Jira queries the wrong MSSQL schema while retrieving details for AO_60DB71_SPRINT table

Issue Summary

After upgrading to recent versions, in case your MS SQL Server Jira database has different schemas with the same set of tables as Jira available, under some circumstances, a table from the wrong schema will be required.

For example, assume that you have a jiraDB database with the following configuration:

1. dbo schema (default schema specified at dbconfig.xml where all data from the current Jira instance is located).
2. jiraDB database has dbo as the default schema and your database use has dbo as the default schema as well.
3. jiraschema (some leftover schema that is left from old installations/migrations/etc. It has outdated data and its tables partially or completely duplicate tables from dbo).

In case of such configuration, Jira will sometime query tables from jiraschema instead of dbo and fail with SQLServerException if outdated schema lacks some columns/fields introduced in the latest versions.

The current problem had been reproduced in Jira 9.9.0 and potentially could manifest itself after an upgrade to Jira 9.1.0 and higher (Jira 9.0.0 is not affected). It was currently observed only for Windows installation with MS SQL Server (the Linux version of MS SQL and other supported RDBMS doesn't seem to be affected).

So far reported cases were covering the Agile board (Scrum) cases where the board's backlog fails to be loaded and represented in UI.

This is reproducible on Data Center: (yes)

Steps to Reproduce

In order to trigger the error, you will need to have a configuration similar to that described above. Otherwise, you could emulate the behaviour by following the steps below:

1. Create new or use existing Jira installation. Make sure that you could load and view the backlog for your Agile board (Scrum).
2. Created a new schema under your Jira DB.
3. Copy the structure of your default schema AO_60DB71_SPRINT table to the newly created schema
4. Drop one of the columns to trigger the issue.

   CREATE SCHEMA jiraschema;  
   SELECT * INTO jiraschema.AO_60DB71_SPRINT from dbo.AO_60DB71_SPRINT
   ALTER TABLE jiraschema.AO_60DB71_SPRINT DROP COLUMN SYNCED
   

5. Dropping the column will allow us to visualise the fact that Jira is querying the wrong schema since the attempt to load the backlog will now fail with SQLException.

   2023-07-06 13:39:14,810+0000 http-nio-8080-exec-4 url: /rest/greenhopper/1.0/xboard/plan/backlog/data.json; user: jira_admin ERROR jira_admin 819x65x4 1ouzt8t 0:0:0:0:0:0:0:1 /rest/greenhopper/1.0/xboard/plan/backlog/data.json [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: null
   java.lang.reflect.UndeclaredThrowableException
   	at com.sun.proxy.$Proxy3650.getSprintsForView(Unknown Source)
   	at com.atlassian.greenhopper.web.rapid.plan.PlanningModeHelper.buildSprints(PlanningModeHelper.java:240)
   ...
   Caused by: com.mysema.query.QueryException: Caught SQLServerException for select "SPRINT"."COMPLETE_DATE", "SPRINT"."END_DATE", "SPRINT"."ID", "SPRINT"."NAME", "SPRINT"."RAPID_VIEW_ID", "SPRINT"."SEQUENCE", "SPRINT"."START_DATE", "SPRINT"."CLOSED", "SPRINT"."STARTED", "SPRINT"."ACTIVATED_DATE", "SPRINT"."GOAL", "SPRINT"."SYNCED", "SPRINT"."AUTO_START_STOP", "SPRINT"."INCOMPLETE_ISSUES_DESTINATION" from "jiraschema"."AO_60DB71_SPRINT" "SPRINT" where "SPRINT"."RAPID_VIEW_ID" = ?
   	at com.mysema.query.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:38)
   	at com.mysema.query.sql.Configuration.translate(Configuration.java:514)
   	at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:364)
   	at com.mysema.query.sql.ProjectableSQLQuery.list(ProjectableSQLQuery.java:313)
   ...
   Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'SYNCED'.
   	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
   	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
   	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602)
   	...
   	at com.mysema.query.sql.AbstractSQLQuery.list(AbstractSQLQuery.java:308)
   	... 318 more
   

Expected Results

Jira will query tables only under the default schema defined at dbconfig.xml and set as default for database users.

Actual Results

Jira will query the table under the wrong schema.

Workaround

Migrating data to dedicated schema

The root schema "dbo" is considered to be the default in most cases and occasionally other applications will store tables under this schema. As the best practice, it is recommended to have a dedicated schema/database user for the Jira database since it allows to prevent of unexpected change data by other applications that could access "dbo" schema. Additionally, this also prevents some failures with Active Objects tables (used by different Jira plugins — both bundle and 3rd-party). In order to mitigate this problem you could migrate data under a dedicated schema:

1. Make a backup using the XML export functionality (Backing up data).
2. Stop Jira.
3. Create a new dedicated schema/database user following steps from documentation (Connecting Jira applications to SQL Server 2017). Ensure that the new user has proper access to the created schema.
4. Point Jira to the newly created schema/database user (you could use Using the Jira application configuration tool for this activity).
5. Start Jira and restore the backup created on the first step (Restoring data from an xml backup).