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, you can specify an optional validation query for your database connection. Depending on whether you are using a direct JDBC URL, or a data source, this is configured differently.

Determining the validation query SQL for your database type

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

Enabling validation query using direct JDBC

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

  1. Shut down Confluence
  2. Edit the confluence.cfg.xml file at the root of your Confluence Home and other important directories
  3. Add the property "hibernate.c3p0.validate" and set it to "true", and add the property "hibernate.c3p0.preferredTestQuery" and set it to the value of the query you determined above for your database type. See this excerpt of the file with the two added properties for details: 

    confluence.cfg.xml (excerpt)
        ...
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">100</property>
        <property name="hibernate.c3p0.max_size">60</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.min_size">20</property>
        <property name="hibernate.c3p0.timeout">30</property>
        <property name="hibernate.c3p0.validate">true</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1</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

Ensuring validation query using a data source

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

  1. Shut down Confluence (or the Tomcat installation running Confluence).

  2. Edit the conf/server.xml file in your Confluence Install Directory, or in the Tomcat installation's CATALINA_HOME directory.
  3. Find the Resource element for your data source, and add the "validationQuery" field, with the value of the query you determined above for your database type. See this excerpt of the file with this added for details:

    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 (or the Tomcat installation running Confluence).

Results and Considerations

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 or the application server running Confluence.

(warning) 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 Confluence installation, you may wish to assess the performance impact of this change before implementing it.

Last modified on Jul 14, 2017

Was this helpful?

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