how to use c3p0 with node failover test

Question

We have setup MySQL NDB Cluster with three nodes and here is the details of Setup.
Node1 - MultithreadedDataNode1, SQLNode1
Node2 - MultithreadedDataNode2, SQLNode2
Node3 - Management Node

we are testing a few failover scenarios to ensure that the setup works. On the client side we are using MySQLConnector v5.1.30 along with c3p0 connection pooling. Client is configured with Multimaster parameters. So if one of the node or application goes down, JDBC queries should be executed on another node and continue to respond.

But we are observing abnormal behavior when SQLNode(Port: 3306) goes down. The connections are not failed over to another node.

The configurations are as given below:

<bean id="myDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
                destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.ReplicationDriver" />                            <!-- useConfigs=clusterBase -->
        <property name="jdbcUrl" value="jdbc:mysql:replication://192.168.102.22,192.168.102.23/hhmefep?autoReconnect=true&amp;failOverReadOnly=false&amp;roundRobinLoadBalance=true" /> 
        <property name="user" value="root"/>
        <property name="password" value="changeit"/>
        <property name="acquireIncrement" value="2" />
        <property name="minPoolSize" value="50" />
        <property name="maxPoolSize" value="100" />
        <property name="maxStatements" value="2000" />
        <property name="maxIdleTime" value="3600" />
        <property name="connectionTesterClassName" value="com.mysql.jdbc.integration.c3p0.MysqlConnectionTester" />
        <property name="testConnectionOnCheckin" value="true" />
        <property name="testConnectionOnCheckout" value="false" />
        <property name="idleConnectionTestPeriod" value="30" />
    </bean>

When I start the client, these two nodes are up and running. After that I kill the mysqld process from first node (192.168.102.22), I get following stackTrace,

org.hibernate.exception.JDBCConnectionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2536)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
at org.hibernate.loader.Loader.list(Loader.java:2271)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:119)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1716)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347)
at com.mi.persistence.dao.cdr.BganActiveCallsDaoImpl.findBganActiveCallsBy_Imsi_Nsapi_Apn(BganActiveCallsDaoImpl.java:53)
at sun.reflect.GeneratedMethodAccessor283.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy23.findBganActiveCallsBy_Imsi_Nsapi_Apn(Unknown Source)
at com.mi.persistence.service.cdr.BganActiveCallsServiceImpl.findBganActiveCallsBy_Imsi_Nsapi_Apn(BganActiveCallsServiceImpl.java:22)
at sun.reflect.GeneratedMethodAccessor282.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:91)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy24.findBganActiveCallsBy_Imsi_Nsapi_Apn(Unknown Source)
at com.mi.persistence.service.callhandler.BganAccessRequestServiceImpl.getPreeptedCalls(BganAccessRequestServiceImpl.java:384)
at com.mi.taskobjects.externalsigobj.radiusobj.BGANAccessRequest.process(BGANAccessRequest.java:175)
at com.mi.entities.scheduler.SchWorkerThread.run(SchWorkerThread.java:165)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 19,578 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
at sun.reflect.GeneratedConstructorAccessor66.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3715)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3604)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4155)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2838)
at com.mysql.jdbc.LoadBalancedMySQLConnection.execSQL(LoadBalancedMySQLConnection.java:159)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2212)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
at org.hibernate.loader.Loader.doQuery(Loader.java:802)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2533)
... 42 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3161)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3615)
... 56 more
Apr 24, 2014 2:13:13 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@18138d9 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
Apr 24, 2014 2:13:13 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@18138d9 -- APPARENT DEADLOCK!!! Complete Status: 
Managed Threads: 3
Active Threads: 3
Active Tasks: 
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@2dbdcf (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1088a28 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1a67efd (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
Pending Tasks: 
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1db547d
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@87fa45
    com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@13fcd69
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1c6dc6d
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@b198d1
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1a7ce01
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@1c5be25
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1469706
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@1654503
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@195f1b4
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@12f893d
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1db4400
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@4d7ab3
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@b29aa5
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@1c6fb4f
    com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@9d2c8e
    com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@d843e0
    com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@504b76
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@18a408c
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@193e4a4
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@87d60d
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@1f6002a
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@184e113
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@d4bbb0
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@6e4a4c
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@cfc9f4
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@1d3c0f3
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@120989c
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@1034f45
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@497a1b
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@1ef8e0b
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@568dc9
    com.mchange.v2.resourcepool.BasicResourcePool$AsyncTestIdleResourceTask@76bb9c
Pool thread stack traces:
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
    java.lang.Thread.sleep(Native Method)
    com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2396)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2326)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.GeneratedConstructorAccessor27.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    com.mysql.jdbc.LoadBalancingConnectionProxy.createConnectionForHost(LoadBalancingConnectionProxy.java:383)
    com.mysql.jdbc.RandomBalanceStrategy.pickConnection(RandomBalanceStrategy.java:75)
    com.mysql.jdbc.LoadBalancingConnectionProxy.pickNewConnection(LoadBalancingConnectionProxy.java:711)
    com.mysql.jdbc.LoadBalancingConnectionProxy.dealWithInvocationException(LoadBalancingConnectionProxy.java:415)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:654)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:549)
    $Proxy22.ping(Unknown Source)
    com.mysql.jdbc.ReplicationConnection.ping(ReplicationConnection.java:986)
    sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:597)
    com.mchange.v2.c3p0.impl.NewProxyConnection.rawConnectionOperation(NewProxyConnection.java:1315)
    com.mysql.jdbc.integration.c3p0.MysqlConnectionTester.activeCheckConnection(MysqlConnectionTester.java:78)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:368)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishResourceOnCheckin(C3P0PooledConnectionPool.java:301)
    com.mchange.v2.resourcepool.BasicResourcePool.attemptRefurbishResourceOnCheckin(BasicResourcePool.java:1606)
    com.mchange.v2.resourcepool.BasicResourcePool.access$200(BasicResourcePool.java:32)
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask.run(BasicResourcePool.java:1228)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
    java.lang.Thread.sleep(Native Method)
    com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2396)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2326)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.GeneratedConstructorAccessor27.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    com.mysql.jdbc.LoadBalancingConnectionProxy.createConnectionForHost(LoadBalancingConnectionProxy.java:383)
    com.mysql.jdbc.RandomBalanceStrategy.pickConnection(RandomBalanceStrategy.java:75)
    com.mysql.jdbc.LoadBalancingConnectionProxy.pickNewConnection(LoadBalancingConnectionProxy.java:711)
    com.mysql.jdbc.LoadBalancingConnectionProxy.dealWithInvocationException(LoadBalancingConnectionProxy.java:415)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:654)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:549)
    $Proxy22.ping(Unknown Source)
    com.mysql.jdbc.ReplicationConnection.ping(ReplicationConnection.java:986)
    sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:597)
    com.mchange.v2.c3p0.impl.NewProxyConnection.rawConnectionOperation(NewProxyConnection.java:1315)
    com.mysql.jdbc.integration.c3p0.MysqlConnectionTester.activeCheckConnection(MysqlConnectionTester.java:78)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:368)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishResourceOnCheckin(C3P0PooledConnectionPool.java:301)
    com.mchange.v2.resourcepool.BasicResourcePool.attemptRefurbishResourceOnCheckin(BasicResourcePool.java:1606)
    com.mchange.v2.resourcepool.BasicResourcePool.access$200(BasicResourcePool.java:32)
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask.run(BasicResourcePool.java:1228)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
    java.lang.Thread.sleep(Native Method)
    com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2396)
    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2326)
    com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832)
    com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
    sun.reflect.GeneratedConstructorAccessor27.newInstance(Unknown Source)
    sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417)
    com.mysql.jdbc.LoadBalancingConnectionProxy.createConnectionForHost(LoadBalancingConnectionProxy.java:383)
    com.mysql.jdbc.RandomBalanceStrategy.pickConnection(RandomBalanceStrategy.java:75)
    com.mysql.jdbc.LoadBalancingConnectionProxy.pickNewConnection(LoadBalancingConnectionProxy.java:711)
    com.mysql.jdbc.LoadBalancingConnectionProxy.dealWithInvocationException(LoadBalancingConnectionProxy.java:415)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:654)
    com.mysql.jdbc.LoadBalancingConnectionProxy.invoke(LoadBalancingConnectionProxy.java:549)
    $Proxy22.ping(Unknown Source)
    com.mysql.jdbc.ReplicationConnection.ping(ReplicationConnection.java:986)
    sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    java.lang.reflect.Method.invoke(Method.java:597)
    com.mchange.v2.c3p0.impl.NewProxyConnection.rawConnectionOperation(NewProxyConnection.java:1315)
    com.mysql.jdbc.integration.c3p0.MysqlConnectionTester.activeCheckConnection(MysqlConnectionTester.java:78)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:368)
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishResourceOnCheckin(C3P0PooledConnectionPool.java:301)
    com.mchange.v2.resourcepool.BasicResourcePool.attemptRefurbishResourceOnCheckin(BasicResourcePool.java:1606)
    com.mchange.v2.resourcepool.BasicResourcePool.access$200(BasicResourcePool.java:32)
    com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask.run(BasicResourcePool.java:1228)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

Please correct me if I am missing anything ..
Thanks,
Parimal

score 2 · Answer 1 · answered Apr 24 '14 at 14:36

So, what is happening is clear: c3p0 is testing Connections on check-in. those tests are hanging, neither succeeding nor failing with an Exception, eventually sataurating and deadlocking the Thread pool.

The question I can't answer for sure is why the Connection tests are hanging. It's clear from the stack traces that what they are trying to do is reconnect, which is what you want to happen, but to your replicated (slave) server. Instead, reconnections are failing, and the Threads are falling asleep. Looking at the source to mysql's ConnectionImpl class, connectWithRetry sleeps initialTimout seconds (default 2) and retries up to maxReconnects time (default 3). by default, this means that things should hang for only up to 6 seconds. It looks like you may be experiencing longer hangs than this, though.

One workaround would be to just set autoReconnect=false. Then Connection tests will probably just fail, c3p0 will try to acquire a new Connection, which will hopefully succeed from the failover server.

Other things to try would be to reduce initialTimeout and maxConnects to make failures faster. Again, if the internal reconnect fails, c3p0 will reacquire dead Connections, which should now point to the failover server.

Ultimately, there are a couple of unknowns here: (1) Is your replication server, the one that your Connections should reconnect with, live and up and running? Because the clear issue is that the mysql driver is failing to establish a new Connection upon failure, as real question is why com.mysql.jdbc.ReplicationDriver is not behaving as advertised, and connecting to the alternative server. One possibility is that there's just a problem with the alternative server. Please verify that you can connect directly to that [ jdbc:mysql://192.168.102.23/hhmefep ]. If not, that's your problem! (2) If you can connect to the alt server, we still don't know whether the failover logic prior to connectWithRetries(...) is actually working, that is, is it trying to connect to the replicated server? (One thing that might be worth doing is to intentionally shut down the replicated server, so that there is nothing to failover too. Does the application fail more cleanly?)

I guess, as a practical matter, I'd verify that the replication server is functioning, reduce initialTimout and maxReconnects (maybe to 1 and 1), and try again. If that doesn't work, I'd set autoReconnect to false. If you do set autoReconnect to false, you can use the c3p0 parameter maxConnectionAge to ensure that, when the main server comes back, Connections are (fairly) promptly replaced by Connections back to the master. Set maxCoonnectionAge to something like a few minutes (not a few seconds, or you'll churn through Connections and reduce performance).

good luck!

I have tried both approaches (1) reducing initialTimeout and maxReconnects to 1 and (2) set autoReconnect=false and maxConnectionAge=600 sec. But still the driver performance is not getting improved. I am also getting WARN messages from hazelcast cache `Apr 25, 2014 3:36:34 PM com.mi.persistence.model.network.Ggsn WARNING: [192.168.6.192]:5701 [NY-HHMEA] [3.2-RC2] Cache lock could not be acquired!` — Patel Parimal, Apr 25 '14 at 10:18
This [Blog post](https://blogs.oracle.com/carriergrademysql/entry/how_to_use_jdbc_connector) was helpful to achieve better results. — Patel Parimal, Apr 25 '14 at 10:35

how to use c3p0 with node failover test

1 Answers1