How to detect incorrect endpoint for my database using C3P0 pool and JDBC

0
votes

A silly bug while copying the connection host made me point to an incorrect endpoint... this blocked the initialization process for 30 minutes...

and finally the exception:

  • Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30).

Trying to reproduce the error I simply point to google.es with the following connection string

jdbc:mysql://google.es/myDB

Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> 1hgeksr8t1vk3sn21ui8jk0|53689fd0, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1hgeksr8t1vk3sn21ui8jk0|53689fd0, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://google.es/myDB, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 3600, maxIdleTimeExcessConnections -> 300, maxPoolSize -> 5, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 1, numHelperThreads -> 3, preferredTestQuery -> null, properties -> {user=*, password=*}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

and the initialization gets stuck for those 30 longs minutes...

I'd like it to throw an exception faster, but I'm unsure as to which configuration values should I touch: c3p0 acquireRetryAttempts? or jdbc socketTimeout? and most important what it may break if I change this...

2
mysqljdbcc3p0

2 Answers

0
votes

the default set-up will take about 30 secs (not 30 mins!) to detect a bad database: it makes acquireRetryAttempts=30 with a delay of acquireRetryDelay=1000ms before concluding that a Connection cannot be acquired. if you wish faster detection of a bad endpoint, recide either or both of those variables. you can set acquireRetryAttempts to one, if you'd like, in which case any Exception on Connection acquisition will be interpreted as a problem with the endpoint.

See http://www.mchange.com/projects/c3p0/#configuring_recovery

0
votes

The problem lies with the JDBC timeout configuration.

As specified on this blog: Understanding JDBC Internals & Timeout Configuration

JDBC defaults to 0ms for conection and socket timeout, that is, no timeout.

If the target endpoint exists but does not answer (packets are probably swallowed by the firewall) connections stay trapped and only after a whole minute (why one minute? still a mistery) does c3p0 attempt a connection retry... therefore exception appeared after way too long...

The solution lies in adding a connectTimeout=XXXms to JDBC (can be passed as a parameter: mysql://google.es/myDB?connectTimeout=1000) and after a minute (30 tries at 1 sec for timeout 1 sec for retry delay) exception occurs...

Still all parameters need to be tuned to your needs, as they have other implications and may disrupt functioning. It is also recommended to check c3p0 forum thread about possible configurations such as activating breakAfterAcquireFailure.