[dbcp][pool] SQLException Connection reset even with validation query

[Jason Britton <jb...@gmail.com>]

Hi -
Using older version of dbcp (1.2.2) and pool (1.3) we've been getting
java.sql.SQLException: Io exception: Connection reset after retrieving a
connection from pool that has been timed out or gone away for some reason
even though we've configured dbcp with testOnBorrow and validationQuery.  I
have even tried adding in the idle evictor thread settings as well with no
help from that either.  I've just updated our libs to use latest dbcp and
pool but I'm very pessimistic that updating the libs would fix such a very
fundamental problem or am I completely missing what testOnBorrow and
validationQuery should be accomplishing?  I'd really love to hear that this
bug/lack of robustness has been addressed in latest version of dbcp or that
my configuration parameters below are at fault.  Thanks for any suggestions
or information,

            DriverAdapterCPDS da = new DriverAdapterCPDS();
            da.setDriver("oracle.jdbc.OracleDriver");
            da.setPoolPreparedStatements(false);
            da.setUrl(DB_CONNECT_STR);
            SharedPoolDataSource tds = new SharedPoolDataSource();
            tds.setConnectionPoolDataSource(da);
            tds.setValidationQuery("select 'validationQuery' from dual");
            tds.setTestOnBorrow(true);

            tds.setMaxActive(450);
            tds.setMaxWait(1000);
            tds.setMaxIdle(20);

            tds.setTestWhileIdle(true);
            tds.setTimeBetweenEvictionRunsMillis(100000);
            tds.setNumTestsPerEvictionRun(100);
            tds.setMinEvictableIdleTimeMillis(450000);

Re: [dbcp][pool] SQLException Connection reset even with validation query

[Phil Steitz <ph...@gmail.com>]

On 11/7/10 10:07 AM, Jason Britton wrote:
> Hi -
> Using older version of dbcp (1.2.2) and pool (1.3) we've been getting
> java.sql.SQLException: Io exception: Connection reset after retrieving a
> connection from pool that has been timed out or gone away for some reason
> even though we've configured dbcp with testOnBorrow and validationQuery.  I
> have even tried adding in the idle evictor thread settings as well with no
> help from that either.  I've just updated our libs to use latest dbcp and
> pool but I'm very pessimistic that updating the libs would fix such a very
> fundamental problem or am I completely missing what testOnBorrow and
> validationQuery should be accomplishing?  I'd really love to hear that this
> bug/lack of robustness has been addressed in latest version of dbcp or that
> my configuration parameters below are at fault.  Thanks for any suggestions
> or information,
>
>              DriverAdapterCPDS da = new DriverAdapterCPDS();
>              da.setDriver("oracle.jdbc.OracleDriver");
>              da.setPoolPreparedStatements(false);
>              da.setUrl(DB_CONNECT_STR);
>              SharedPoolDataSource tds = new SharedPoolDataSource();
>              tds.setConnectionPoolDataSource(da);
>              tds.setValidationQuery("select 'validationQuery' from dual");
>              tds.setTestOnBorrow(true);
>
>              tds.setMaxActive(450);
>              tds.setMaxWait(1000);
>              tds.setMaxIdle(20);
>
>              tds.setTestWhileIdle(true);
>              tds.setTimeBetweenEvictionRunsMillis(100000);
>              tds.setNumTestsPerEvictionRun(100);
>              tds.setMinEvictableIdleTimeMillis(450000);
>

Does your code hold on to connections for a long time while they are 
checked out of the pool?  The configuration above should guarantee 
that a connection returned by tds.getConnection() has just been 
validated.  Are you getting this error immediately after retrieving 
a connection from the pool?  DBCP does nothing to validate 
connections being held by clients, so if your code is holding onto 
the connections for a while and a server-side or connectivity 
problem happens while you are using a connection, there is nothing 
that DBCP can do to help.

In any case, you should determine the source of the server-side 
connection resets or connectivity problems. DBCP's ability to handle 
these problems is currently limited to testing connections when they 
are borrowed, returned or sitting idle in the pool.  We are looking 
at options for improving robustness in version 2.0.  Suggestions for 
improvement are welcome via JIRA or on the dev list.  Suggestions 
including patches are *most* welcome.

Phil

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org