Surviving Database 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, Confluence would normally need to be restarted.

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, Confluence can use a validation query for your database connection. This is enabled by default on new installations (Confluence 6.5 and later), but if you've upgraded from an older Confluence version you can choose to enable this manually by following the steps below. 

Determine the validation query SQL for your database

Different databases 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:

Database Type

Validation Query

MySQL

select 1

Microsoft SQL Server

select 1

Oracle

select 1 from dual

PostgreSQL

select 1

Enable validation query with a direct JDBC connection

To ensure Confluence validates database connections in the database connection pool:

  1. Stop Confluence. 
  2. Edit the <home-directory>confluence.cfg.xml file.
  3. Insert the following property for your particular database. 

    For PostgreSQL, SQL Server, and MySQL
    <property name="hibernate.c3p0.preferredTestQuery">select 1</property>
    For Oracle
    <property name="hibernate.c3p0.preferredTestQuery">select 1 from dual</property>
  4. Save confluence.cfg.xml
  5. If you're using Confluence 5.10.3 or earlier you'll need to add the following system property with the validation query for your database.
    For example: 

    -Dc3p0.preferredTestQuery="select 1"
  6. Restart Confluence. 

You should now be able to recover from a complete loss of all connections in the database connection pool without the need to restart Confluence.

Enable validation query with a datasource connection

To ensure Confluence validates database connections in the database connection pool:

  1. Stop Confluence.

  2. Edit the <installation-directory>/conf/server.xml file (or wherever you have configured your datasource).
  3. Find the Resource element for your data source, and add the "validationQuery" parameter as in the example for PostgreSQL below.  Remember to give it the appropriate value for your database type. 

    server.xml (excerpt)
    ... 
    <Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
              username="postgres"
              password="postgres"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost:5432/yourDatabaseName"
              maxTotal="60"
              maxIdle="20"
              validationQuery="select 1" />
    ...
  4. Save conf/server.xml

  5. Restart Confluence.

You should now be able to recover from a complete loss of all connections in the database connection pool without the need to restart Confluence.

Last modified on Dec 14, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.