Documentation for JIRA 6.3 EAP developer (EAP) releases only. Not using this? See below:
(JIRA 6.2.x documentation | JIRA OnDemand documentation | earlier versions of JIRA)

Skip to end of metadata
Go to start of metadata

When a database server reboots or a network failure has occurred, all connections in the database connection pool are broken. To overcome this issue, JIRA would normally need restarting (or for JIRA WAR distributions, the application server running JIRA would need restarting).

However, database connections in the database connection pool can be validated by running a simple SQL query. If a broken database connection is detected in the pool, a new one is created to replace it.

To do this, you need to specify an optional <validation-query/> element (in the dbconfig.xml file of your JIRA Home Directory), whose content is the query which validates connections in the database connection pool. See the following procedure for details.

Ensuring JIRA validates connections to its database

To ensure JIRA validates database connections in the database connection pool:

  1. Shut down JIRA (or the Tomcat installation running JIRA).
  2. Edit the dbconfig.xml file at the root of your JIRA Home Directory or use the Advanced tab of the JIRA Configuration Tool to configure the relevant settings.
  3. Configure the validation query for your type of database:
    • If editing the dbconfig.xml file, add the <validation-query/> element with the appropriate validation query for your type of database, as shown in the example below for MySQL. (See Determining the Validation Query below for details.)

    • If using the JIRA Configuration Tool, on the Advanced tab, select the Validation Query check box and enter the appropriate validation query for your type of database. (See Determining the Validation Query below for details.)
  4. Specify a validation query timeout for your validation query, whose value is the appropriate length of time (in seconds) that the system should wait for a validation query to succeed before the system considers the database connection broken:
    • If editing the dbconfig.xml file, add the <validation-query-timeout/> element with the appropriate length of time (in seconds). (warning) This should only be done for MySQL.
    • If using the JIRA Configuration Tool, on the Advanced tab, select the Validation Query Timeout check box and enter the appropriate length of time (in seconds).
  5. You may wish to specify the following options, which relate to the above validation query options (see Tuning Database Connections - Connection pool settings section for details):

    JIRA Configuration Tool 'Advanced' tab option

    Element in dbconfig.xml

    Test While Idlepool-test-while-idle
    Time Between Eviction Runstime-between-eviction-runs-millis
    Minimum Evictable Idle Timemin-evictable-idle-time-millis
  6. Save your edited dbconfig.xml file (or click the Save button if using the JIRA Configuration Tool).

  7. Restart JIRA (or the Tomcat installation running JIRA).

(info) Please Note: If you continue to have problems with connections closing, you may need to set the time-between-eviction-runs-millis parameter to a lower value or as a last resort, set test-on-borrow to true. For more information about test-on-borrow, see Tuning Database Connections - Connection pool settings section.

Determining the Validation Query and Timeout

Different database types have slightly different SQL syntax requirements for their validation query. The validation query should be as simple as possible, as this is run every time a connection is retrieved from the pool. The validation query timeout should only be set for MySQL.

The following validation queries are recommended for the following types of databases:

Database Type

Validation Query

Validation Query Timeout

MySQL

select 1

3

Microsoft SQL Server

select 1

N/A

Oracle

select 1 from dual

N/A 

PostgreSQL

select version();

N/A

(warning) If the Validation Query Timeout is used on any database other than MySQL it will cause significant problems with the JIRA instance.

Result

You should now be able to recover from a complete loss of all connections in the database connection pool without the need to restart JIRA or the application server running JIRA.

(warning) Performance Considerations:

  • Setting this option has a performance impact. The overall decrease in performance should be minimal, as the query itself is quick to run. In addition, the query will only execute when you make a connection. Thus, if the connection is kept for the duration of a request, the query will only occur once per request.
  • If you are running a large JIRA installation, you may wish to assess the performance impact of this change before implementing it.
  • No labels

9 Comments

  1. Anonymous

    Hi All,

    I have Jira 3.13 Enterprise Edition running on Weblogic 9.2 / DB2.

    Any ideas how i can set the 'validationQuery' parameter on my datasource created using weblogic console.

    Thanks

    Imtiyaz

    1. Hi,

      Admittedly I am not an expert but the BEA documentation suggests you can use a combination of the following properties - TESTTABLE, REFRESH and TESTONRESERVE. This should behave the same way as the validation query.

      Cheers,
      Nick Menere
      JIRA Developer

  2. Anonymous

    I've followed every letter of the instructions to the letter, and yet in a brand new install of the v4.2.1 release (because atlassian still hasn't got a clue about upgrading), I *still* get the stupid warning message shown below.

    "WARNING: You are using MySQL but do not have a validation query configured. Because of MySQL connection timeouts, this situation can cause data corruption in JIRA. For more information please see this bug report."

    All I can say is that ya'll are lucky that it's the marketing people in my org who are buying into this software - as the person who has to attempt support it, I find the installation and "maintenance" to be cumbersome and unwieldy in the extreme.

  3. The number of validation queries is actually extremely high.

    Tests done with JIRA 4.4.4, Oracle, and config:

    <pool-size>100</pool-size>
    <connection-properties>SetBigStringTryClob=true</connection-properties>
    <validation-query>select 'validationQuery' from dual</validation-query>
    actionvalidation calls
    viewing a single issue (/browse/ISSUEKEY-123)29
    viewing a moderately fancy dashboard 100
    commenting on an issue53

    Each validation is a full round trip to the DB and eats up time.

    before config was moved to dbconfig.xml one could play with settings as detailed below in an attempt to reduce the number of validations but still validate occasionally in order to be able to survive a DB disconnect.

    http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

    After the move we appear to have no ability to do this.

    Is it the case now that one can either have a JIRA instance that becomes unusable if there is a network hiccup/DB problem or one that validates so aggressively that the solution is not feasible for large installs?

    thanks

    1. Anonymous

      Tomcat's Connection Pool had a validationInterval parameter. Unfortunately, DBCP-Commons does not have such a thing.

  4. Anonymous

    This query execution takes approximately 100 microseconds with SQL Server (based on averaging 112 executions).  Thus, on average, approximately 10 milliseconds are added to the overall page retrieval for the moderately fancy dashboard

    Greenhopper queries are so slow, the additional 10 milliseconds are not noticed.  

  5. Anonymous

    The following does not work with PostgreSQL. What are the similar settings for PostgreSQL?

        <pool-min-size>20</pool-min-size>
        <pool-max-size>20</pool-max-size>
        <pool-max-wait>30000</pool-max-wait>
     
        <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-while-idle>true</pool-test-while-idle>
        <validation-query-timeout>3</validation-query-timeout>
    1. What are the effects/results you've gotten from applying the above? It works from my end here with the following setup:

      • JIRA 5.0
      • PostgreSQL 9.1