Re: ConnectionValidator

[Armin Waibel <ar...@code-au-lait.de>]

Hi Dave,

started with that stuff, but be stumped how to
handle ConnectionValidator with commons-dbcp pool.
With ConnectionFactoryDefaultImpl there are no problems
using ConnectionValidator, but ConnectionFactoryDBCPImpl
use commons-dbcp to pool connections.
Problem: When ConnectionValidator return 'false' I don't
know how to remove connection from dbcp-pool.
Seems ConnectionFactoryDBCPImpl will not support
ConnectionValidator (thus only validationQuery will
supported by this implementation).

 > Any update on this? Will this be in RC5?
I will spare no pains to make this part of
rc5, but there some other tasks to solve, so ...

regards,
Armin

Dave Hodson wrote:

 > Armin --
 >
 > Any update on this? Will this be in RC5?
 >
 > Regards
 >
 > Dave
 >
 >
 >
 >> -----Original Message-----
 >> From: Justis Peters [mailto:justis@oculan.com] Sent: Friday, October 
17, 2003 9:36 AM
 >> To: OJB Users List; armin@code-au-lait.de
 >> Subject: Re: ConnectionValidator (was: Re: Connection failed if DB 
restarted?)
 >>
 >> Hi Armin,
 >>
 >> Armin Waibel [armin@code-au-lait.de] wrote:
 >>
 >>> On Fri, 17 Oct 2003 11:54:56 -0400, Justis Peters
 >>
 >>
 >> <ju...@oculan.com>
 >>
 >>> wrote:
 >>>
 >>>> Could we handle this by defining an interface and allowing
 >>
 >>
 >> users to
 >>
 >>>> specify their own tester class?  Here is what I am picturing:
 >>>>
 >>>
 >>> yes this will be possible, but can you describe what's the
 >>
 >>
 >> advantage
 >>
 >>> of this solution?
 >>
 >>
 >> Simply that it allows more flexibility in how the test is 
implemented.  We would probably ship an implementation with OJB that 
used the current method (of running a query) and would take an attribute 
that specified the validation query.  Perhaps we could also add some 
sort of string or regex to which the results would be compared ( for 
validation ).  This is just one implementation, though.
 >>
 >>
 >>> And how can we reduce validation query calls?
 >>
 >>
 >> This would be up to the individual implementations of 
ConnectionValidator.  My first thought is that people could implement 
specific tests for the situations they worry about (like a server 
restart).  Here are some other ideas, though:
 >> - Wrap the connection in a decorator that stores information about 
how many times it has been used, or how long it has been since it was 
last validated.  There could be a special ConnectionValidator that 
checked for these types of Connection objects, and if detected, would 
validate less often.  These attributes could be set in the 
"connection-validator" element.  For example, you could ask that it only 
validate every 2 seconds.  (I haven't thought through this whole 
scenario, but I think you would have to specify a different 
ConnectionFactory as well, to get these decorated Connection objects).
 >> - Check the Connection to see if (conn.getWarnings != null).  Run 
the validation query only if this is true.
 >> - Do platform specific checks, like looking to see if the server 
port for your database is even listening.
 >>
 >> Mostly, though, I am just hoping that opening this up to an 
interface will encourage people to write their own implementations.  A 
number of users who don't have time to get deep into the code and submit 
patches would actually have the time to implement a simple interface. 
Perhaps some innovation would come from the user community by having 
this hook available?
 >>
 >>
 >>> Or do you see an alternative way to check the connection?
 >>
 >>
 >> In the case that Andrew was asking about, he could add some sort of 
hook that listens for database server restarts and considers all 
database connections that existed previously to be invalid.  Maybe he 
could do it with a JMX hook?  In this case, though, it seems somewhat 
unintuitive to have the ConnectionValidator wait for the dead 
connections to come in. It would be better (for his case, at least) to 
be able to simply call a method on the connection pool that allows him 
to flush all the existing connections.
 >>
 >>
 >> Any thoughts on all this?  My idea is mainly to open up for 
innovation, by including an interface.  I don't have any direct 
solutions yet.  Looking forward to hearing your response.
 >>
 >> Sincerely yours,
 >> Justis Peters
 >> Oculan Corp.
 >> justis@oculan.com
 >>
 >>
 >>>> =========================
 >>>> ConnectionValidator.java
 >>>> =========================
 >>>> package org.apache.ojb.broker.accesslayer;
 >>>>
 >>>> public interface ConnectionValidator
 >>>> {
 >>>>     public boolean isValid(java.sql.Connection conn); }
 >>>>
 >>>>
 >>>> =========================
 >>>> repository_database.xml
 >>>> =========================
 >>>> <jdbc-connection-descriptor jcd-alias="default" 
driver="org.postgresql.Driver" etc... >
 >>>>
 >>>>   <connection-pool maxActive="51" testOnBorrow="true" 
testOnReturn="false" removeAbandoned="true">
 >>>>       <connection-validator
 >>>> className="com.mydomain.ojbutils.PostgresConnectionValidator">
 >>>>           <attribute attribute-name="foo"
 >>
 >>
 >> attribute-value="testvalue1"
 >>
 >>>> />
 >>>>           <attribute attribute-name="bar"
 >>
 >>
 >> attribute-value="testvalue2"
 >>
 >>>> />
 >>>>       </connection-validator>
 >>>>   </connection-pool>
 >>>>
 >>>>   <sequence-manager
 >>>
 >>>
 >>> className="org.apache.ojb.broker.util.sequence.SequenceManage
 >>
 >>
 >> rNextValImpl">
 >>
 >>>>       <attribute attribute-name="autoNaming"
 >>
 >>
 >> attribute-value="false" />
 >>
 >>>>   </sequence-manager>
 >>>> </jdbc-connection-descriptor>
 >>>>
 >>>> ====================================================
 >>>>
 >>>> Thoughts?
 >>>>
 >>>> Sincerely yours,
 >>>> Justis Peters
 >>>> Oculan Corp.
 >>>> justis@oculan.com
 >>>>
 >>>>
 >>>> Armin Waibel [armin@code-au-lait.de] wrote:
 >>>>
 >>>>> Hi Andrew,
 >>>>>
 >>>>> you can use the 'validationQuery' attribute of connection-pool 
element to specify a validation query. This query will be
 >>
 >>
 >> executed
 >>
 >>>>> each time before a connection was delivered by the pool.
 >>>>>
 >>>>> Any proposals to make this more sophisticated are welcome.
 >>>>>
 >>>>> regards,
 >>>>> Armin
 >>>>>
 >>>>> On Fri, 17 Oct 2003 11:20:02 -0400, Clute, Andrew 
<An...@osn.state.oh.us> wrote:
 >>>>>
 >>>>>
 >>>>>> I apologized in advance for the increase in mail traffic
 >>
 >>
 >> today. I
 >>
 >>>>>> have checked the archives for an answer to this, to no avail.
 >>>>>>
 >>>>>> I am seeing, as expected, if our Database has been
 >>
 >>
 >> restarted that
 >>
 >>>>>> any
 >>>>>
 >>>>>
 >>>>> db
 >>>>>
 >>>>>> connections in the pool as becoming invalid. I
 >>
 >>
 >> understand how this
 >>
 >>>>>> is happening -- the connection doesn't realize the socket 
connection has been reset, and so it fails to persist
 >>
 >>
 >> any data. I
 >>
 >>>>>> have simulated the situation, and I can see the
 >>
 >>
 >> connection when it
 >>
 >>>>>> is retrieved from the pool is
 >>>>>
 >>>>>
 >>>>> still
 >>>>>
 >>>>>> marked as active.
 >>>>>>
 >>>>>> I am using ConnectionFactoryPooledImpl. What seems odd
 >>
 >>
 >> to me, is it
 >>
 >>>>> never
 >>>>>
 >>>>>> self-corrects. It looks to me like when the connection
 >>
 >>
 >> failed, it
 >>
 >>>>>> is still in the pool, and I never get a valid connection.
 >>>>>>
 >>>>>> OJB is being used by EJB's sitting in Jboss. I am not
 >>
 >>
 >> using OJB as
 >>
 >>>>>> a deployed EJB, but calling directly. I am also not using a 
DataSource inside of Jboss, but instead the connection pool from OJB.
 >>>>>>
 >>>>>> It seems to me using the pooled connections, that when
 >>
 >>
 >> the database
 >>
 >>>>>> is restarted, then I have to take down my Jboss server
 >>
 >>
 >> and restart
 >>
 >>>>>> it to refresh the pool. Am I missing something here?
 >>
 >>
 >> Now, I could
 >>
 >>>>>> use no
 >>>>>
 >>>>>
 >>>>> pool,
 >>>>>
 >>>>>> and
 >>>>>> then the problem never arises. Would using the DataSource from jBoss
 >>>>>
 >>>>>
 >>>>> and
 >>>>>
 >>>>>> ConnectionFactoryManagedImpl make it better.
 >>>>>>
 >>>>>> What should me expectations be for db connections when
 >>
 >>
 >> the database
 >>
 >>>>>> has been restarted? (Bigger issue here is our SQL Server
 >>
 >>
 >> cluster --
 >>
 >>>>>> all the connections become invalid when the server fails
 >>
 >>
 >> over to a
 >>
 >>>>>> different server).
 >>>>>>
 >>>>>> -Andrew
 >>>>>>
 >>>>>>
 >>>
 >>>>> -------------------------------------------------------------------
 >>>>>
 >>>>>> -- To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
 >>>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
 >>>>>>
 >>>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>>>
 >>>> --------------------------------------------------------------------
 >>>>
 >>>>> - To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
 >>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
 >>>>
 >>>>
 >>> ---------------------------------------------------------------------
 >>>
 >>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
 >>>> For additional commands, e-mail: ojb-user-help@db.apache.org
 >>>>
 >>>>
 >>>
 >>>
 >>>
 >>>
 >>>
 >> ---------------------------------------------------------------------
 >>
 >>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
 >>> For additional commands, e-mail: ojb-user-help@db.apache.org
 >>
 >>
 >> ---------------------------------------------------------------------
 >> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
 >> For additional commands, e-mail: ojb-user-help@db.apache.org
 >>
 >>
 >
 >
 >





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