This page last changed on Apr 08, 2010 by rosie@atlassian.com.

When a database server reboots, or there is a network failure, all the connections in the connection pool are broken and this normally requires a Application Server reboot.

However, the Commons DBCP (Database Connection Pool) which is used by the Tomcat application server (and hence JIRA Standalone) can validate connections before issuing them by running a simple SQL query, and if a broken connection is detected, a new one is created to replace it. To do this, you will need to set the "validationQuery" option on the database connection pool.

Performance Considerations
Please note, that setting the validationQuery option on the database connection pool will have 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 you keep the connection for the duration of a request, the query will only occur once per request.
You may wish to assess the performance impact of this change before implementing it, if you are running a large JIRA instance.

Determining the Validation Query

Each database has slightly different SQL syntax. The Validation Query should be as simple as possible, as this is run every time a connection is retrieved from the pool.
Some examples are:

Database Validation Query
MySQL select 1
MS SQL Server select 1
Oracle select 1 from dual
Postgres select version();

Setting the validationQuery parameter

In your application server, where the JDBC DataSource is configured, a parameter needs to be added to tell the Connection Pool to use a validation query (determined above) to validate connections.

JIRA Standalone (after version 3.2) or JIRA EAR/WAR on Tomcat 5.5 and Tomcat 6.0

If you are using JIRA Standalone, edit conf/server.xml
If you are using JIRA EAR/WAR on Tomcat 5.5 or Tomcat 6.0, edit conf/Catalina/localhost/jira.xml

Locate the section where the 'jdbc/JiraDS' DataSource is set up, and add the following:

  • If you are using MySQL or MS SQL Server, add validationQuery="select 1"
  • If you are using Oracle, add validationQuery="select 1 from dual"
  • If you are using Postgres, add validationQuery="select version();"

For example (for MySQL):

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/jiradb?useUnicode=true&amp;characterEncoding=UTF8"
    username="jirauser"
    password="jirapassword"
    maxActive="20"
    validationQuery="select 1" />
Tomcat 4 and 5.0:

Edit conf/server.xml (Tomcat 4) or conf/Catalina/localhost/jira.xml (Tomcat 5.0), locate the section where the 'jdbc/JiraDS' DataSource is set up, and add the following:

  • If you are using MySQL or MS SQL Server, add
    <parameter>
    <name>validationQuery</name>
    <value>select 1</value>
    </parameter>
  • If you are using Oracle, add
    <parameter>
    <name>validationQuery</name>
    <value>select 1 from dual</value>
    </parameter>

For example:

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/JiraDS">
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@<database host machine>:<port>:<SID></value>
</parameter>
<parameter>
<name>username</name>
<value>...</value>
</parameter>
<parameter>
<name>password</name>
<value>...</value>
</parameter>

<parameter>
<name>validationQuery</name>
<value>select 1</value>
</parameter>

<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
</ResourceParams>
Orion / OC4J

For Orion/OC4J, edit config/data-sources.xml, and add the property as a nested tag:

<data-source
class="<datasource driver class>"
name="<name>"
location="<location>"
xa-location="<xa-location>"
ejb-location="<ejb-location>"
url="<url>"
connection-driver="<driver>"
username="<login>"
password="<password>"
inactivity-timeout="30"
>
<property name="validationQuery" value="Select 1" />
</data-source>
Other app servers

Consult the relevant JIRA app server guide and the app server documentation to find how to add the property.

Results

You should now be able to survive a complete loss of all connections and be able to recover without rebooting the your App Server.

Document generated by Confluence on Mar 27, 2011 18:52