-
Bug
-
Resolution: Fixed
-
High
-
5.7, 5.10.1
-
89
-
Severity 1 - Critical
-
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.
| Database Type | Validation Query |
|---|---|
| MySQL | select 1 |
| Microsoft SQL Server | select 1 |
| Oracle | select 1 from dual |
| PostgreSQL | select 1 |
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.
- is related to
-
-
- Closed
-
- relates to
-
-
- Closed
-
- mentioned in
-
![[Extranet] Page](/images/icons/generic_link_16.png "[Extranet] Page")
Page
Failed to load
-
Page
Failed to load
-
Page Loading...
-
-
-
-
-
-
![[Atlassian Documentation] Page](https://confluence.atlassian.com/images/icons/favicon.png "[Atlassian Documentation] Page"){kind=icon}
[CONFSERVER-36484] hibernate.c3p0.preferredTestQuery is ignored in confluence.cfg.xml
Hi team,
we have confluence 5.9.9 and MSSQL 2014 and wonder the validation is working for us. I like your workaround with system property, but how on Earth should I propagate the value with space into java property ?
setenv.sh
JAVA_OPTS=" -Dc3p0.preferredTestQuery=\"select 1\" "
Is it correct? at the linux process output I see:
516 11952 1 87 09:06 pts/0 00:07:48 /srv/confluence/jre//bin/java -Djava.util.logging.config.file=/srv/confluence/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Xms4096m -Xmx4096m -XX:+UseG1GC -Dc3p0.preferredTestQuery=select 1 -XX:+PrintGCDetails -XX:+PrintGCDateStamps .....
However confluence started up and in system information I see...
JAVA_OPTS = -Xms4096m -Xmx4096m -XX:+UseG1GC -Dc3p0.preferredTestQuery="select 1" -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+PrintGCTimeStamps -Xloggc:/srv/confluence-home/logs/gc_2016-08-11_0906.log -Djava.awt.headless=true -Djava.net.preferIPv4Stack=true -Dhttps.socket.timeout=60000 -Djsse.enableSNIExtension=false
I hope this is correctly setup. Thanks Roman
We have observed one instance of this bug affecting Oracle DB on Confluence 5.8.13, so it is not limited to MS SQL Server.
This was verified by observing SQL logs. The custom validationQuery of select 1 from dual for Oracle was not being used. When we hooked up JMonitor to the Confluence application, we further verified that hibernate.c3p0.preferredTestQuery was null, meaning it was not picked up from <confluence_home>/confluence.cfg.xml. Instead, Confluence was requesting to run a much more expensive query to get all tables (which is the c3p0 default):
SELECT NULL AS table_cat, o.owner AS table_schem, o.object_name AS table_name, o.object_type AS table_type, NULL AS remarks FROM all_objects o WHERE o.owner LIKE :1 ESCAPE '/' AND o.object_name LIKE :2 ESCAPE '/' AND o.object_type IN ( 'xxx', 'TABLE' ) ORDER BY table_type, table_schem, table_name
Like in the MS SQL Server cases, it appears that the preferred test query is being ignored. This caused severe general performance problems in the observed case. Switching from a direct JDBC connection to a Tomcat Datasource resolved the issue.
This issue has been resolved in Confluence 5.10.4 which is now available for download.