Bugzilla #1879 (Connection Pool handling of Stale Connections)

["Craig R. McClanahan" <cr...@apache.org>]

For the details of this bug report, see

  http://nagoya.apache.org/bugzilla/show_bug.cgi?id=1879

As the report points out, GenericConnectionPool does not do anything about
"stale" connections (i.e. those that the database has timed out, or what
will happen to all of them if the database is rebooted).  The proposal is
to add a call to

  connection.getConnection().isClosed()

in an attempt to assess the current state of the connection.  I don't
think this is the correct approach, because it depends on the JDBC driver
doing something active to test for this (as opposed to just checking to
see if the application has called close() on it).  Doing this would also
contradict the "JDBC 2.1 Core API" spec, section 11.1:

    The Connection.isClosed() method is only guaranteed to
    return true after Connection.close() has been called.
    Connection.isClosed() cannot be called, in general, to
    determine if a database connection is valid or invalid.
    A typical client can determine that a connection is
    invalid by catching the exception that is thrown when an
    operation is attempted.

Therefore, it seems to me that allowing the configuration of an optional 
"do nothing" SQL command that GenericConnectionPool can use to test the
validity of the connection would be a better approach.  Does that make
sense to folks?

Craig

Re: Bugzilla #1879 (Connection Pool handling of Stale Connections)

["Craig R. McClanahan" <cr...@apache.org>]

On Fri, 1 Jun 2001, Ted Husted wrote:

> "Craig R. McClanahan" wrote:
> > Therefore, it seems to me that allowing the configuration of an optional
> > "do nothing" SQL command that GenericConnectionPool can use to test the
> > validity of the connection would be a better approach.  Does that make
> > sense to folks?
> 
> So, we'd add a testConnection property to control this and also provide
> a TestCommand property for the command. Then before returning a
> connection, the GCP would try the TestCommand first, and if it generates
> an exception, we discard the stale connection and get a new one. 
> 

I was thinking more along the lines of a single property
(testCommand).  If there was no such command, no test would be done.

> Do you think a default TestCommand of ";" work as a NOP ?
> 

There is no safe default for this that will work across even the few
databases I've used i the past (Oracle, Postgres, MySQL).  Therefore, I
believe the default should be "no test is performed".

> Is there a good way to get a connection to go stale, or do you just have
> to wait it out?
> 

Open some connections and then restart the database server :-).

> I've worked on this area of the code before, and could sign-up for this.
> 

That would be cool.  I'm going to cut beta-3 tonight, so if you do it
today you'll make the cut.

> 
> -- Ted Husted, Husted dot Com, Fairport NY USA.
> -- Custom Software ~ Technical Services.
> -- Tel 716 737-3463.
> -- http://www.husted.com/about/struts/
> 

Craig

Re: Bugzilla #1879 (Connection Pool handling of Stale Connections)

[Ted Husted <hu...@apache.org>]

"Craig R. McClanahan" wrote:
> Therefore, it seems to me that allowing the configuration of an optional
> "do nothing" SQL command that GenericConnectionPool can use to test the
> validity of the connection would be a better approach.  Does that make
> sense to folks?

So, we'd add a testConnection property to control this and also provide
a TestCommand property for the command. Then before returning a
connection, the GCP would try the TestCommand first, and if it generates
an exception, we discard the stale connection and get a new one. 

Do you think a default TestCommand of ";" work as a NOP ?

Is there a good way to get a connection to go stale, or do you just have
to wait it out?

I've worked on this area of the code before, and could sign-up for this.


-- Ted Husted, Husted dot Com, Fairport NY USA.
-- Custom Software ~ Technical Services.
-- Tel 716 737-3463.
-- http://www.husted.com/about/struts/

RE: Bugzilla #1879 (Connection Pool handling of Stale Connections)

["Deadman, Hal" <ha...@tallan.com>]

I agree, isClosed() won't work. An option for a testing a connection with a
do-nothing SQL statement is needed. This is pretty standard for most
connection pools that I have seen.

> -----Original Message-----
> From: Craig R. McClanahan [mailto:craigmcc@apache.org]
> Sent: Friday, June 01, 2001 5:41 PM
> To: struts-dev@jakarta.apache.org
> Subject: Bugzilla #1879 (Connection Pool handling of Stale
> Connections)
>
>
> For the details of this bug report, see
>
>   http://nagoya.apache.org/bugzilla/show_bug.cgi?id=1879
>
> As the report points out, GenericConnectionPool does not do
> anything about
> "stale" connections (i.e. those that the database has timed
> out, or what
> will happen to all of them if the database is rebooted).  The
> proposal is
> to add a call to
>
>   connection.getConnection().isClosed()
>
> in an attempt to assess the current state of the connection.  I don't
> think this is the correct approach, because it depends on the
> JDBC driver
> doing something active to test for this (as opposed to just
> checking to
> see if the application has called close() on it).  Doing this
> would also
> contradict the "JDBC 2.1 Core API" spec, section 11.1:
>
>     The Connection.isClosed() method is only guaranteed to
>     return true after Connection.close() has been called.
>     Connection.isClosed() cannot be called, in general, to
>     determine if a database connection is valid or invalid.
>     A typical client can determine that a connection is
>     invalid by catching the exception that is thrown when an
>     operation is attempted.
>
> Therefore, it seems to me that allowing the configuration of
> an optional
> "do nothing" SQL command that GenericConnectionPool can use
> to test the
> validity of the connection would be a better approach.  Does that make
> sense to folks?
>
> Craig
>
>