Surviving Connection Closures

Added by Nick Menere, last edited by James Fleming [Atlassian] on Jan 22, 2008  (view change) show comment
JIRA Documentation

Index

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.

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 'validationQuery' from dual

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.

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:

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

or in the case of Oracle 

<parameter>
<name>validationQuery</name>
<value>select 'validationQuery' 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 'validationQuery' from dual</value>
</parameter>

<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
</ResourceParams>
Tomcat 5.5 and Standalone (post 3.2)

In Tomcat 5.5, the format for the added section would be:

<Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
username="jirauser"
password="jirapassword"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@<database host machine>:<port>:<SID>"
validationQuery="select 'validationQuery' from dual" />
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.

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

Comments (2)

 

  1. Nov 30, 2007

    Abhishek Tripathi says:

    Hi All, I'm useing Jira 3.6.5 Enterprise edition. Can any one help me what will ...

    Hi All,

    I'm useing Jira 3.6.5 Enterprise edition.

    Can any one help me what will be the value of '<value>'  for Oracle database in the below line.

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

    1. Dec 06, 2007

      Ian Daniel [Atlassian] says:

      Hi Abhishek, select 'validationQuery' from dual (as stated in the above ta...

      Hi Abhishek,

      select 'validationQuery' from dual

      (as stated in the above table).