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.
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
- Shut down Jira (or the Tomcat installation running Jira).
- Edit the
dbconfig.xmlfile at the root of your Jira home directory or use the Advanced tab of the Jira configuration tool to configure the relevant settings. - Configure the validation query for your type of database:
If editing the
dbconfig.xmlfile, 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> <jdbc-datasource> <url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&characterEncoding=UTF8&sessionVariables=storage_engine=InnoDB</url> <driver-class>com.mysql.jdbc.Driver</driver-class> <username>jiradbuser</username> <password>password</password> <pool-min-size>20</pool-min-size> <pool-max-size>20</pool-max-size> <pool-max-wait>30000</pool-max-wait> <validation-query>select 1</validation-query> <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> <pool-test-on-borrow>false</pool-test-on-borrow> <validation-query-timeout>3</validation-query-timeout> </jdbc-datasource> </jira-database-config>- If using the Jira configuration tool, on the Advanced tab, select the Validation Query checkbox and enter the appropriate validation query for your type of database. (See Determining the validation query below for details.)
- 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.xmlfile, add the<validation-query-timeout/>element with the appropriate length of time (in seconds).
This should only be done for MySQL. - If using the Jira configuration tool, on the Advanced tab, select the Validation Query Timeout checkbox and enter the appropriate length of time (in seconds).
- If editing the
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.xmlTest While Idle pool-test-while-idleTime Between Eviction Runs time-between-eviction-runs-millisMinimum Evictable Idle Time min-evictable-idle-time-millisSave your edited
dbconfig.xmlfile (or click the Save button if using the Jira configuration tool).- Restart JIRAJira(or the Tomcat installation running Jira).
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 |
| 3 |
Microsoft SQL Server |
| N/A |
Oracle |
| N/A |
PostgreSQL |
| N/A |
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.
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.