Re: Connection failed if DB restarted?
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection failed if DB restarted?
Hi Armin,
Could we handle this by defining an interface and allowing users to specify their own
tester class? Here is what I am picturing:
=
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.SequenceManagerNextValImpl
attribute attribute-name=autoNaming attribute-value=false /
/sequence-manager
/jdbc-connection-descriptor
Thoughts?
Sincerely yours,
Justis Peters
Oculan Corp.
[EMAIL PROTECTED]
Armin Waibel [EMAIL PROTECTED] 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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Connection failed if DB restarted?
Hi again,
On Fri, 17 Oct 2003 11:54:56 -0400, Justis Peters [EMAIL PROTECTED]
wrote:
Hi Armin,
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?
And how can we reduce validation query calls?
Or do you see an alternative way to check the connection?
regards,
Armin
=
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.SequenceManagerNextValImpl
attribute attribute-name=autoNaming attribute-value=false /
/sequence-manager
/jdbc-connection-descriptor
Thoughts?
Sincerely yours,
Justis Peters
Oculan Corp.
[EMAIL PROTECTED]
Armin Waibel [EMAIL PROTECTED] 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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: ConnectionValidator (was: Re: Connection failed if DB restarted?)
Hi Armin,
Armin Waibel [EMAIL PROTECTED] wrote:
On Fri, 17 Oct 2003 11:54:56 -0400, Justis Peters [EMAIL PROTECTED]
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.
[EMAIL PROTECTED]
=
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.SequenceManagerNextValImpl
attribute attribute-name=autoNaming attribute-value=false /
/sequence-manager
/jdbc-connection-descriptor
Thoughts?
Sincerely yours,
Justis Peters
Oculan Corp.
[EMAIL PROTECTED]
Armin Waibel [EMAIL PROTECTED] 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
[EMAIL PROTECTED] 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
Re: ConnectionValidator (was: Re: Connection failed if DB restarted?)
Hi Armin,
Armin Waibel [EMAIL PROTECTED] wrote:
I propose to add the associated JdbcConnectionDescriptor
as argument too
=
ConnectionValidator.java
=
package org.apache.ojb.broker.accesslayer;
public interface ConnectionValidator
{
public boolean isValid(JdbcConnectionDescriptor jcd, Connection
conn);
}
then it's possible to lookup the org.apache.ojb.broker.platforms.Platform
implementation for the used RDBMS in the ConnectionValidator
implementation.
In Platform interface we can add a String defaultValidationQuery() method
and in the implemetation classes we build queries against e.g. a DB system
table
or return null if not supported. Then it will be easy to build some sort of
base ConnectionValidator.
What do you think?
This sounds fantastic. Thanks for the discussion!
Sincerely yours,
Justis Peters
Oculan Corp.
[EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Connection failed if DB restarted?
Thanks for the answer...worked the way I wanted it to for my immediate problem in production. Not keen on the fact that I know have a ton of extra SQL calls--but I guess that is the price I pay right now! :) Thanks again! -Andrew -Original Message- From: Armin Waibel [mailto:[EMAIL PROTECTED] Sent: Friday, October 17, 2003 11:30 AM To: OJB Users List Subject: Re: Connection failed if DB restarted? 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
