Scenario:
Web application running on host seperated from DB server via webserver. After period of inactivity (default is 60mins) on firewall the connection will be torn down, but the connection pool stills indicates a valid connection (i.e netstat reports that connection established on port 1521 - Oracle db)

I'm looking at using the C3P0 connection pool since this has a timeout setting, which i'm assuming will remove idle connections. The example has a setting of 5000 msecs.

The question(s) are:

1. What version of C3P0 is shipped (using Hibernate 1.1.6) ?
2. Can i use the settings like c3p0.idleConnectionTestPeriod, c3p0.

I've tried the DBCP settings, but i'm still seeing the same issue - after a period of 2+hrs of inactivity in the application the system fails to establish a new connection (basically a login to the web-app)

Any suggestions would be most welcome :-)

grant

As far as I understand (many people have had this problem with DBCP), this is a limitation of DBCP. In C3P0, you can set the hibernate.c3p0.timeout property.

Guys, my scalability tests show C3P0 to be a much more robust implementation than DBCP. I know DBCP is Apache and some people think that everything from Apache is necessarily better than a small sf.net project like C3P0, but this is more perception than reality.

[quote="gavin"]As far as I understand (many people have had this problem with DBCP), this is a limitation of DBCP. In C3P0, you can set the hibernate.c3p0.timeout property.

I tried with a timeout settings of:

hibernate.c3p0.timeout 5000
hibernate.c3p0.max_statements 100
hibernate.c3p0.validate false

The result was that Connection timeout exceptions coming from:

com.mchange.v2.c3p0.impl.C3P0PooledConnection.close(C3P0PooledConnect
ion.java:177)

The stack trace (below) looks like a C3P0 async thread has run around and killed off the connection. This results in the app. failing


java.sql.SQLException: Io exception: Connection timed out
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:211)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:324)
at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:648)
at oracle.jdbc.driver.OraclePreparedStatement.privateClose(OraclePrepare
dStatement.java:309)
at oracle.jdbc.driver.OraclePreparedStatement.close(OraclePreparedStatem
ent.java:244)
at oracle.jdbc.driver.OracleConnection.close_statements(OracleConnection
.java:1618)
at oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:916)
at com.mchange.v2.c3p0.impl.C3P0PooledConnection.close(C3P0PooledConnect
ion.java:177)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.destroyResource(C
3P0PooledConnectionPool.java:122)
at com.mchange.v2.resourcepool.BasicResourcePool$5.run(BasicResourcePool
.java:397)
at com.mchange.v2.async.SimpleRunnableQueue$TaskThread.run(SimpleRunnabl
eQueue.java:99)

The timeout is probably in ms, or something .... check the c3p0 doc....