hibernate.c3p0.preferredTestQuery is ignored in confluence.cfg.xml

Our documentation says we can modify the preferred test query by setting that parameter. However, with Confluence 5.7 and possibly older versions, it does not appear to work.

Steps to reproduce:

• Start Confluence 5.7 with a debugger attached
• Add to confluence.cfg.xml

  <property name="hibernate.c3p0.preferredTestQuery">select 1</property>
  <property name="hibernate.c3p0.validate">true</property>

• Set a breakpoint in testPooledConnection in C3P0PooledConnectionPool and you will notice that the test query is null, which eventually ends up calling com.mchange.v2.c3p0.impl.DefaultConnectionTester#activeCheckConnectionNoQuery which does a getTables() call on the connection metadata instead of issuing the test query specified.

Workaround:

Add the system property - c3p0.preferredTestQuery - with the validation query that is appropriate for your database. The added system property will be used as default value and picked up by c3p0 connection pool when getting a new connection.

For example:

-Dc3p0.preferredTestQuery="select 1"

Refer to https://confluence.atlassian.com/display/DOC/Configuring+System+Properties for more information on how to configure system properties.

Workaround 2:

Run the steps in this guide to switch from jdbc to datasource. Be sure to back up the files indicated in Step 1.

Please be sure not to skip the section starting with "If you're changing an existing Confluence installation over to using a datasource" in Step 4.