Configuration tool (config.sh) running on text mode doesn't specify a schema for SQL Server

XMLWordPrintable

    • 8.05
    • 1
    • Severity 3 - Minor
    • 1

      Issue Summary

      Running the utility <JIRA_INSTALL>/bin/config.sh -c in text mode will cause it to not ask for a schema name when selecting SQL Server. This will cause the file dbconfig.xml to be set without a schema, which is required in SQL Server.

      Other issues might arise from this situation, for example, failed upgrade tasks in Jira Service Desk.

      Steps to Reproduce

      1. Install Jira.
      2. Run <JIRA_INSTALL>/bin/config.sh -c
      3. Select the following options:
        • [D] Database Selection
        • [S] SQL Server (MS-SQL)
      4. Enter the database details such as hostname, port, etc.

      Expected Results

      The utility will ask for the schema name and write this value accordingly on <JIRA_LOCAL_HOME>/dbconfig.xml.

      Actual Results

      Schema name is not requested, and the value <schema-name> won't be written to <JIRA_LOCAL_HOME>/dbconfig.xml:

      <?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mssql</database-type>
  <jdbc-datasource>
    <url>jdbc:sqlserver://;serverName=localhost;portNumber=3341;databaseName=jiradb</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>atlassian</username>
    <password>atlassian</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

      Here's a full shell session demonstrating the issue:

      ecrmac-101029:atlassian-jira-software-8.5.3-standalone vinicius.fontes$ bin/config.sh -c
No JRE_HOME or JAVA_HOME environment variable is set - attempting to just run java command
Loading application properties from /Users/vinicius.fontes/Documents/deployments/atlassian-jira-software-8.5.3-standalone/atlassian-jira/WEB-INF/classes/jira-application.properties
jira-home not configured - no database settings can be loaded.
----------------------
JIRA Configurator v1.1
----------------------

--- Main Menu ---
  [H] Configure JIRA Home
  [D] Database Selection
  [W] Web Server (incl. HTTP/HTTPs configuration)
  [A] Advanced Settings
  [S] Save and Exit
  [X] Exit without Saving

Main Menu> D

--- Database Selection ---
  Database Type : H2
  Instance      : (unused)
  Connect As    : sa / (no password)

* [H] H2 (not for production use)
  [M] MySQL 5.6
  [N] MySQL 5.7+
  [O] Oracle
  [P] PostgreSQL
  [A] Aurora PostgreSQL 9.6 (DC Only)
  [S] SQL Server (MS-SQL)

  [X] Return to Main Menu

Database Selection [H]> s
SQL Server Database Configuration.
Hostname ()> localhost
Port ()> 3341
Database ()> jiradb
Username ()> atlassian
Password ()>
Test Connection  ([Y]/N)? > n

--- Database Selection ---
  Database Type : SQL Server
  Instance      : localhost:3341/jiradb
  Connect As    : atlassian / *****

  [H] H2 (not for production use)
  [M] MySQL 5.6
  [N] MySQL 5.7+
  [O] Oracle
  [P] PostgreSQL
  [A] Aurora PostgreSQL 9.6 (DC Only)
* [S] SQL Server (MS-SQL)

  [X] Return to Main Menu

Database Selection [S]> x

--- Main Menu ---
  [H] Configure JIRA Home
  [D] Database Selection
  [W] Web Server (incl. HTTP/HTTPs configuration)
  [A] Advanced Settings
  [S] Save and Exit
  [X] Exit without Saving

Main Menu> s
*** Please set a value for jira-home.

--- Main Menu ---
  [H] Configure JIRA Home
  [D] Database Selection
  [W] Web Server (incl. HTTP/HTTPs configuration)
  [A] Advanced Settings
  [S] Save and Exit
  [X] Exit without Saving

Main Menu> H
Current JIRA Home:
New JIRA Home> /Users/vinicius.fontes/Documents/deployments/atlassian-jira-software-8.5.3-standalone/jira-home

--- Main Menu ---
  [H] Configure JIRA Home
  [D] Database Selection
  [W] Web Server (incl. HTTP/HTTPs configuration)
  [A] Advanced Settings
  [S] Save and Exit
  [X] Exit without Saving

Main Menu> s
Storing database configuration in /Users/vinicius.fontes/Documents/deployments/atlassian-jira-software-8.5.3-standalone/jira-home/dbconfig.xml
Settings saved successfully.
ecrmac-101029:atlassian-jira-software-8.5.3-standalone vinicius.fontes$ cat jira-home/dbconfig.xml
<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mssql</database-type>
  <jdbc-datasource>
    <url>jdbc:sqlserver://;serverName=localhost;portNumber=3341;databaseName=jiradb</url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <username>atlassian</username>
    <password>atlassian</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <pool-test-while-idle>true</pool-test-while-idle>
  </jdbc-datasource>
</jira-database-config>

      This in turn can cause some Jira Service Desk upgrade tasks to fail, such as those below:

      2020-03-11 08:37:42,152-0400 localhost-startStop-1 ERROR      [c.a.s.bootstrap.upgrade.SyncUpgradeTaskServiceImpl] Upgrade task: SyncUpgradeTaskMigrateAgentPermission failed (exception message: Caught SQLServerException for select count(*) from "null"."AO_54307E_ASYNCUPGRADERECORD" "AO_54307E_ASYNCUPGRADERECORD" where "AO_54307E_ASYNCUPGRADERECORD"."UPGRADE_TASK_NAME" = ? and "AO_54307E_ASYNCUPGRADERECORD"."ACTION" = ?)
com.querydsl.core.QueryException: Caught SQLServerException for select count(*) from "null"."AO_54307E_ASYNCUPGRADERECORD" "AO_54307E_ASYNCUPGRADERECORD" where "AO_54307E_ASYNCUPGRADERECORD"."UPGRADE_TASK_NAME" = ? and "AO_54307E_ASYNCUPGRADERECORD"."ACTION" = ?
    at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
    at com.querydsl.sql.Configuration.translate(Configuration.java:459)
    at com.querydsl.sql.AbstractSQLQuery.unsafeCount(AbstractSQLQuery.java:627)
    at com.querydsl.sql.AbstractSQLQuery.fetchCount(AbstractSQLQuery.java:132)
    at com.atlassian.servicedesk.internal.upgrade.AsyncUpgradeTaskRecordQStoreImpl.lambda$hasTaskCompleted$1(AsyncUpgradeTaskRecordQStoreImpl.java:70)
    at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69)
    at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87)
    at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43)
    at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:131)
(...)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'null.AO_54307E_ASYNCUPGRADERECORD'.
(...)

      2020-03-11 08:38:33,506-0400 Caesium-1-4 ERROR ServiceRunner     [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [com.atlassian.servicedesk,buildNumber=1] failed
com.querydsl.core.QueryException: Caught SQLServerException for select count(*) from "null"."AO_54307E_ASYNCUPGRADERECORD" "AO_54307E_ASYNCUPGRADERECORD" where "AO_54307E_ASYNCUPGRADERECORD"."UPGRADE_TASK_NAME" = ? and "AO_54307E_ASYNCUPGRADERECORD"."ACTION" = ?
    at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
    at com.querydsl.sql.Configuration.translate(Configuration.java:459)
    at com.querydsl.sql.AbstractSQLQuery.unsafeCount(AbstractSQLQuery.java:627)
    at com.querydsl.sql.AbstractSQLQuery.fetchCount(AbstractSQLQuery.java:132)
    at com.atlassian.servicedesk.internal.upgrade.AsyncUpgradeTaskRecordQStoreImpl.lambda$hasTaskCompleted$1(AsyncUpgradeTaskRecordQStoreImpl.java:70)
    at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:69)
    at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:87)
    at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:43)
    at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:131)
(...)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'null.AO_54307E_ASYNCUPGRADERECORD'.
(...)

      Workaround

      • Use the visual mode. This requires a GUI (X11, macOS, etc), and can be done by running config.sh without the parameter -c.
      • Manually add the parameter <schema-name>, following the example below: 
        <jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>mssql</database-type>
<schema-name>jiraschema</schema-name>
<jdbc-datasource>
  <url>jdbc:sqlserver://dbserver:1433;databaseName=jiradb</url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <username>jiradbuser</username>
  <password>password</password>
  <pool-min-size>20</pool-min-size>
  <pool-max-size>20</pool-max-size>
  <pool-max-wait>30000</pool-max-wait>
  <pool-max-idle>20</pool-max-idle>
  <pool-remove-abandoned>true</pool-remove-abandoned>
  <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
  <validation-query>select 1</validation-query>
  <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
  <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>

  <pool-test-while-idle>true</pool-test-while-idle>
  <pool-test-on-borrow>false</pool-test-on-borrow>
</jdbc-datasource>
</jira-database-config>

        More information about connecting Jira to supported databases is available at the KB article Connecting Jira applications to a database.

            Assignee:
            Mikolaj Rydzewski (Inactive)
            Reporter:
            Vinicius Fontes
            Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: