Bug #61662 The ping option in validationQuery does not work when there is a failover host.
Submitted: 27 Jun 2011 21:53 Modified: 15 Mar 2013 16:36
Reporter: Bin Feng Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.16 OS:Linux (RH4)
Assigned to: Assigned Account CPU Architecture:Any

[27 Jun 2011 21:53] Bin Feng
Description:
There seems to be an issue with the MySQL Connector/J driver.

I am trying to add a failover MySQL server in my Tomcat configuration.  I found out that the "/* ping */" option for validationQuery does not work when there is a failover server configured.  Specifically, the following configuration in Tomcat context.xml will cause a "Cannot create PoolableConnectionFactory" java exception.

validationQuery="/* ping */ SELECT 1"
username="someuser" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://mysqlhost1,mysqlhost2/mysqldb?connectTimeout=15000&socketTimeout=15000"

The "/* ping */" option for validationQuery DOES work when there is only one MySQL host (which is the primary) configured on context.xml.  And also the MySQL Connector/J driver WORKS fine when the "/* ping */" option is NOT used with a failover host configured.  I mean it works with the following configuration:

validationQuery="SELECT 1"
username="someuser" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://mysqlhost1,mysqlhost2/mysqldb?connectTimeout=15000&socketTimeout=15000"

I found the same issue with the MySQL Connector/J driver of version 5.1.13.  

Versions I Use:
MySQL database: 4.1.22
Apache Tomcat: 5.5.27
MySQL Connector/J: 5.1.16 (the latest)

How to repeat:
Place the following config in Tomcat context.xml:

validationQuery="/* ping */ SELECT 1"
username="someuser" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://mysqlhost1,mysqlhost2/mysqldb?connectTimeout=15000&socketTimeout=15000"

Replace mysqlhost1 and mysqlhost2 with two available servers running MySQL database via Port 3306.

Restart Tomcat.
[9 Aug 2011 4:37] Todd Farmer
Hello Bin,

Can you please provide the full stack trace of the Exception you report?

Thanks,

Todd
[16 Aug 2011 22:01] Bin Feng
Below is the full stack trace of the exception.

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (No operations allowed after connection closed.)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
        at com.company.myclass.MyOwnClass.init(MyOwnClass.java:206)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1139)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:966)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:3956)
        at org.apache.catalina.core.StandardContext.start(StandardContext.java:4230)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
        at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:927)
        at org.apache.catalina.startup.HostConfig.deployDirectories(HostConfig.java:890)
        at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:492)
        at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1150)
        at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
        at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
        at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
        at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
        at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
        at org.apache.catalina.core.StandardService.start(StandardService.java:448)
        at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
Caused by: com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1012)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
        at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1193)
        at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1180)
        at com.mysql.jdbc.ConnectionImpl.setReadOnly(ConnectionImpl.java:5102)
        at com.mysql.jdbc.FailoverConnectionProxy.failOver(FailoverConnectionProxy.java:182)
        at com.mysql.jdbc.FailoverConnectionProxy.dealWithInvocationException(FailoverConnectionProxy.java:98)
        at com.mysql.jdbc.LoadBalancingConnectionProxy$ConnectionErrorFiringInvocationHandler.invoke(LoadBalancingConnectionProxy.java:109)
        at com.mysql.jdbc.FailoverConnectionProxy$FailoverInvocationHandler.invoke(FailoverConnectionProxy.java:53)
        at $Proxy1.executeQuery(Unknown Source)
        at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
        at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.validateConnection(PoolableConnectionFactory.java:332)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1249)
        at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
        ... 28 more
[20 Mar 2013 22:47] Todd Farmer
This is slightly related to behavior in ReplicationConnection which does not ping all slaves, reported in Bug#68733.