コネクション切断の問題を乗り越える方法

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:

データベース	バリデーションクエリ
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>

例:

<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.

結果

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

Child pages