0

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...

Daren
  • 3,337
  • 4
  • 21
  • 35

2 Answers2

0

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

Steve Waldman
  • 13,689
  • 1
  • 35
  • 45
  • Yes, that is what I expected, but not what I observe... I have timed it exactly and it takes 30mins and 30secs to fail, which leads me to conclude that each connection is stuck for 1 full minute before considered for retry. – Daren Apr 10 '13 at 12:11
  • hmmm... that must be some kind of issue with your network setup and/or mysql. have you seen e.g. http://stackoverflow.com/questions/1292856/why-connect-to-mysql-is-so-slow http://forums.mysql.com/read.php?24,23390,23390 – Steve Waldman Apr 10 '13 at 17:33
  • Yeah, I though of that first, but my problem is with configuration... My guess still stands with JDBC configuration parameters, not on c3p0 which I control a bit more... The default JDBC has no time out for its conections... so when "google" doesn't answer it stays there waiting... and perhaps a minute (and a second form c3p0) later a new connection is started and things slowly go to hell... however changing socketTimeout doesn't seem to affect it... gota debug it carefully. – Daren Apr 10 '13 at 17:39
0

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.

Daren
  • 3,337
  • 4
  • 21
  • 35