0

My application has a timer that execute every 60 seconds. Each execution will access the oracle database with C3p0 connection pool. The existing configuration for C3p0 of my application is as following(there are other tasks to access db):

*<property name="acquireIncrement" value="1"/>
<property name="initialPoolSize" value="10"/>
<property name="maxPoolSize" value="25"/>
<property name="minPoolSize" value="10"/>
<property name="maxStatements" value="250"/>
<property name="maxStatementsPerConnection" value="10"/>
<property name="numHelperThreads" value="6"/>
<property name="checkoutTimeout" value="30000"/>     
<property name="maxIdleTimeExcessConnections" value="1800"/>    
<property name="idleConnectionTestPeriod" value="900"/>
<property name="preferredTestQuery" value="SELECT 'x' FROM DUAL"/>*

Now I got an issue that when database shutdown, the timer will throw SQLexcetion for bad connection for each execution, but when the database recover, it keep throwing SQLExcetion for bad connection.

I think that the reason should be when application startup, 10 connection will be established, and timer occupy one of them, when database shutdown, the connection broken, and then database recover, the timer still uses the broken connection.

But I am not sure whether my assumption is correct, the timer will use single connection for its each execution even it has already broken?

If my thinking is correct, I want to reduce idleConnectionTestPeriod to less than 60 seconds that is the time intenal of timer to fix this issue.

Could you guide me on this problem? Thank you.

Evan
  • 21
  • 2
  • 2
    this sounds like stale connections and has nothing to do with idle connections. set the "testConnectionOnCheckout" property to true. this should run your preferredTestQuery before it returns you a connection. – happymeal May 12 '14 at 09:18
  • Thank you for your response. Could you please tell me based on my configuration, why the application still exception after database restarted. – Evan May 14 '14 at 03:31
  • A bit late, but if you are still interested, you might want to read [this](https://www.mchange.com/projects/c3p0/#configuring_connection_testing), especially the part "Simple advice on Connection testing". It's from the author of c3p0. – jumping_monkey Dec 28 '20 at 17:53

0 Answers0