Surviving Connection Closures

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, the Apache Commons DBCP (DataBase Connection Pool), which is used by JIRA, can validate connections in the connection pool by running a simple SQL query. If a broken connection is detected, 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 connection pool. See the following procedure for details.

Setting the Validation Query for Your JIRA Database

To ensure JIRA validates connections in the 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.
3. 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.)

   <?xml version="1.0" encoding="UTF-8"?>
   
   <jira-database-config>
     <name>defaultDS</name>
     <delegator-name>default</delegator-name>
     <database-type>mysql</database-type>
     <schema-name></schema-name>
     <jdbc-datasource>
       <url>jdbc:mysql://dbserver:3306/jiradb?characterEncoding=utf8&amp;useUnicode=true</url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <username>jiradbuser</username>
       <password>mydbpassword</password>
       <pool-size>20</pool-size>
   
       <validation-query>select 1</validation-query>
   
     </jdbc-datasource>
   </jira-database-config>
   

4. Save your edited dbconfig.xml file.
5. Restart JIRA (or the Tomcat installation running JIRA).

Determining the Validation Query

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 following validation queries are recommended for the following types of databases:

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.

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.