MySQL Bugs: #61662: The ping option in validationQuery does not work when there is a failover host.

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

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&amp;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&amp;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&amp;socketTimeout=15000"

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

Restart Tomcat.

Hello Bin,

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

Thanks,

Todd

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

This is slightly related to behavior in ReplicationConnection which does not ping all slaves, reported in Bug#68733.