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 |
|
Microsoft SQL Server |
|
Oracle |
|
PostgreSQL |
|
Enabling validation query using direct JDBC
To ensure Confluence validates database connections in the database connection pool:
- Shut down Confluence
- Edit the
confluence.cfg.xmlfile at the root of your Confluence Home and other important directories 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> ...- 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
Ensuring validation query using a data source
To ensure Confluence validates database connections in the database connection pool:
Shut down Confluence (or the Tomcat installation running Confluence).
- Edit the
conf/server.xmlfile in your Confluence Install Directory, or in the Tomcat installation's CATALINA_HOME directory. 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" /> ...Save
conf/server.xml- 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.
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.