Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-69850

Database Latency Check fails on SQL Server when using a schema other than dbo

    XMLWordPrintable

Details

    Description

      Issue Summary

      The Database Latency Check feature implemented on JRASERVER-66980 fails on SQL Server when Jira's database uses a schema other than 'dbo'.

      Environment

      Jira 7.13.3 or later
      SQL Server: 2012, 2014, 2016

      Steps to Reproduce

      1. Create a database for Jira
      2. Create a database user which Jira will connect as
      3. Create an empty 'schema' in the database for the Jira tables
      4. Install Jira and connect to the newly created database

      Expected Results

      The query to normally execute and show results under Jira admin > System > System Info > Database latency measurement milliseconds.

      Actual Results

      The below exception is thrown in the atlassian-jira.log file:

      2019-08-21 17:54:58,021 http-nio-8080-exec-13 ERROR rparmigiani 1074x184x1 vyxt7d 0:0:0:0:0:0:0:1 /secure/admin/ViewSystemInfo.jspa [c.a.j.util.system.ExtendedSystemInfoUtilsImpl] Exception while measuring database query select count(*) from productlicense
      java.lang.RuntimeException: Exception while measuring database query select count(*) from productlicense
      	at com.atlassian.jira.database.measure.ClockBasedDatabaseQueryMeter.measure(ClockBasedDatabaseQueryMeter.java:34)
      	at com.atlassian.jira.database.measure.CheapSelectDatabaseLatencyMeter.lambda$measure$0(CheapSelectDatabaseLatencyMeter.java:25)
      	at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)
      	at com.atlassian.jira.database.measure.CheapSelectDatabaseLatencyMeter.measure(CheapSelectDatabaseLatencyMeter.java:24)
      	at com.atlassian.jira.util.system.ExtendedSystemInfoUtilsImpl.getProps(ExtendedSystemInfoUtilsImpl.java:249)
      	... 3 filtered
      	at java.lang.reflect.Method.invoke(Method.java:498)
      	at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)
      	at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)
      	at webwork.util.ValueStack.findValue(ValueStack.java:517)
      	at webwork.util.ValueStack.findValue(ValueStack.java:216)
      	at webwork.view.taglib.WebWorkBodyTagSupport.findValue(WebWorkBodyTagSupport.java:62)
      	at webwork.view.taglib.IteratorTag.doStartTag(IteratorTag.java:71)
      	at jsp.secure.admin.jira.views.systeminfo_jsp._jspx_meth_ww_005fiterator_005f1(systeminfo_jsp.java:1504)
      	at jsp.secure.admin.jira.views.systeminfo_jsp._jspx_meth_aui_005fparam_005f8(systeminfo_jsp.java:1435)
      	at jsp.secure.admin.jira.views.systeminfo_jsp._jspx_meth_aui_005fcomponent_005f3(systeminfo_jsp.java:1266)
      	at jsp.secure.admin.jira.views.systeminfo_jsp._jspService(systeminfo_jsp.java:130)
      	at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
      	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
      	... 48 filtered
      	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
      	... 45 filtered
      	at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
      	... 1 filtered
      	at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
      	... 20 filtered
      	at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
      	... 12 filtered
      	at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
      	... 11 filtered
      	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
      	... 48 filtered
      	at com.atlassian.greenhopper.jira.filters.ClassicBoardRouter.doFilter(ClassicBoardRouter.java:62)
      	... 12 filtered
      	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
      	... 66 filtered
      	at com.atlassian.jira.security.JiraSecurityFilter.lambda$doFilter$0(JiraSecurityFilter.java:66)
      	... 1 filtered
      	at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:64)
      	... 39 filtered
      	at com.atlassian.jira.servermetrics.CorrelationIdPopulatorFilter.doFilter(CorrelationIdPopulatorFilter.java:30)
      	... 10 filtered
      	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingFilter.doFilter(RedirectInterceptingFilter.java:21)
      	... 4 filtered
      	at com.atlassian.web.servlet.plugin.LocationCleanerFilter.doFilter(LocationCleanerFilter.java:36)
      	... 26 filtered
      	at com.atlassian.jira.servermetrics.MetricsCollectorFilter.doFilter(MetricsCollectorFilter.java:25)
      	... 25 filtered
      	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
      	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
      	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
      	at java.lang.Thread.run(Thread.java:748)
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'productlicense'.
      	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1621)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:868)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:768)
      	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
      	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:693)
      	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:207)
      	at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:207)
      	at com.atlassian.jira.database.measure.ClockBasedDatabaseQueryMeter.measure(ClockBasedDatabaseQueryMeter.java:29)
      	... 406 more
      

      Also, Database latency measurement milliseconds field gets replaced in Jira's UI and shows: Error accessing database (more in logs) java.lang.RuntimeException: Exception while measuring database query select count( * ) from productlicense.

      If the Jira Admin generates a Support.zip file, the line above can be spotted on the application.xml file almost as if it was a system property right after the database collation settings:

      <Database-collation>SQL_Latin1_General_CP437_CI_AI</Database-collation>
      <Error-accessing-database-more-in-logs>java.lang.RuntimeException: Exception while measuring database query select count(*) from productlicense</Error-accessing-database-more-in-logs>
      

      Notes

      The query that checks latency doesn't take into account the database schema. Instances running under the default 'dbo' schema will likely never see this problem.

      This also affects Integrity checker and support zip generation.

      Workaround

      If SQL Server hosts only the Jira database, then it's possible to change the schema for all tables to use dbo by following the steps at Changing the schema for all JIRA tables under a specific schema.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              rparmigiani Rafael Parmigiani
              Votes:
              41 Vote for this issue
              Watchers:
              60 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: