[jbossts-users] Configure check-valid-connection-sql or valid-connection-checker-class-name

[Moratilla, Juan]

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/Connectors_on_JBoss-Configuring_JDBC_DataSources.html :<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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jbossts-users/attachments/20110517/fc7d6bdc/attachment.html