11:31 a.m.
Hi all,
We are having problems in production enviroments with datasource connections. We have a
JBOSS 4.2.2.GA with a datasource configured to create connections against an Oracle
Database 10g Enterprise Edition Release 10.2.0.1.0. It seems that some connections
returned by the pool are closed because we get the following exception when some thread
try to use that connections:
2011-05-15 01:32:01,455 ERROR [STDERR] java.sql.SQLException: Conexi?n cerrada
2011-05-15 01:32:01,455 ERROR [STDERR] at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
2011-05-15 01:32:01,455 ERROR [STDERR] at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
2011-05-15 01:32:01,455 ERROR [STDERR] at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
2011-05-15 01:32:01,455 ERROR [STDERR] at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)
2011-05-15 01:32:01,455 ERROR [STDERR] at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:349)
2011-05-15 01:32:01,456 ERROR [STDERR] at
org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:344)
2011-05-15 01:32:01,456 ERROR [STDERR] at
org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:201)
Other connections returned by de DB connection pool more ore less at the same time seems
to work properly. For some reason (firewalls, proxyx may be) this connections are not in
use.
We think that including a configuration in the datasource which forces the pool to test
the connections before being borrowed would solve this problem, because the pool would
returned util connections prepared to be used. We have found two possible solutions after
reading 5.3 Configuring JDBC DataSources
http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Conn...
:<http://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/4/html/Connectors_on_JBoss-Configuring_JDBC_DataSources.html>
We have some questions about how these configurations works:
· check-valid-connection-sql
Before the pool returned one connection, it is checked, executing the sql
sentence specified, such as select * from dual
If the SQL sentence is executed correctly the connection is returned because the
test was OK. Am I right?
If it was not posible to executed the statement, what is the behavior? Would the
datasource try with another connection an consider that the previous connection is not in
use and would be removed from pool? Does exists in JBOSS datasource configuration any
tag which specifies a timeout to decide that a connection is closed in case that there are
any response to the SQL test statement?
Including a check-valid-connections, does ensure that every connection returned by de
pool will be in used in terms of connectivity?
If all the connections of the pool were invalid, Will the JBOSS datasource test all
the connections and when find that all are invalid would create a new pool with new
connections?
As I understand in this kind of solution a "user" of pool connections would
get an exception after pool has test all available connections and all were invalid, Am I
right?
What would be the behaviour of pool in terms of throwing exceptions when request a DB
connection?
* valid-connection-checker-class-name
What are the differences between this solution and the one above beyond the
procedure to decide if a connection is valid? I mean, if I implement the
org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface and the method
SQLException isValidConnection(Connection e) doesn't do anything more than executed
"select * from dual" statement we would be talking about the same solution?
valid-connection-checker-class-name provides more possibilities to test a connection,
right? but the rest of behaviour of datasource is equal in the two solutions, the unique
difference is the method to test if a connection is OK, Am right?
Many thanks, best regards,
Juan Moratilla Peón
HP