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 |
|
Microsoft SQL Server |
|
Oracle |
|
PostgreSQL |
|
Enable validation query with a direct JDBC connection
To ensure Confluence validates database connections in the database connection pool:
- Stop Confluence.
- Edit the
<home-directory>confluence.cfg.xmlfile. 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>- Save
confluence.cfg.xml 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"- 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:
Stop Confluence.
- Edit the
<installation-directory>/conf/server.xmlfile (or wherever you have configured your datasource). 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" /> ...Save
conf/server.xml- 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.