DO NOT REPLY [Bug 22736] New: - validationQuery parameter hangs getConnection method.

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://nagoya.apache.org/bugzilla/show_bug.cgi?id=22736>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

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

validationQuery parameter hangs getConnection method.

           Summary: validationQuery parameter hangs getConnection method.
           Product: Commons
           Version: 1.0 Final
          Platform: Other
        OS/Version: Other
            Status: NEW
          Severity: Major
          Priority: Other
         Component: Dbcp
        AssignedTo: commons-dev@jakarta.apache.org
        ReportedBy: sudhir@myway.com


I am using connection pool via the "BasicDataSourceFactory.java" in Tomcat 
4.1.27 and MS SQL Server. I was having issues with having "stale connections" 
because of the datbase dropping unused connections.

TO fix this I added the validation query parameter with a query string to the 
resource configuration but this causes the getConnection() withing 
BasicDataSourceFactory to hang. It  is almost like a while(1) loop as no 
exceptions are thrown.

My server.xml snippet is below. If I remove the "validationQuery" parameter 
everything works fine. Could somebody please point out what this issue is? Is 
this a know bug? what is the workaround for this?
	<!-- Datasource config -->
	<Resource name="jdbc/TestDB"
	               auth="Container"
	               type="javax.sql.DataSource"/>
	
	  <ResourceParams name="jdbc/TestDB">
	    <parameter>
	      <name>factory</name>
	      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
	    </parameter>
	
	    <!-- Maximum number of dB connections in pool. Make sure you
	         configure your mysqld max_connections large enough to handle
	         all of your db connections. Set to 0 for no limit.
	         -->
	    <parameter>
	      <name>maxActive</name>
	      <value>100</value>
	    </parameter>
	
	    <!-- Maximum number of idle dB connections to retain in pool.
	         Set to 0 for no limit.
	         -->
	    <parameter>
	      <name>maxIdle</name>
	      <value>30</value>
	    </parameter>
	
	    <!-- Maximum time to wait for a dB connection to become available
	         in ms, in this example 10 seconds. An Exception is thrown if
	         this timeout is exceeded.  Set to -1 to wait indefinitely.
	         -->
	    <parameter>
	      <name>maxWait</name>
	      <value>10000</value>
	    </parameter>
	
	    <!-- SQL server dB username and password for dB connections  -->
	    <parameter>
	     <name>username</name>
	     <value>webapp</value>
	    </parameter>
	    <parameter>
	     <name>password</name>
	     <value>webapp</value>
	    </parameter>
		<!-- valdationg query that will be run to validate a connection 
object before returning
			helps avoid the "stale conneciton" issue.
		<parameter>
			<name>validationQuery</name>
			<value>SELECT * FROM SEQUENCE_NUMBER</value>
		</parameter>
		-->

	    <!-- Class name for SQL Server JDBC driver -->
	    <parameter>
	       <name>driverClassName</name>
	       <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
	    </parameter>
	
	    <!-- The JDBC connection url for connecting to your SQL dB.-->
	    <parameter>
	      <name>url</name>
	      
<value>jdbc:microsoft:sqlserver://cgichi2klapsd:1433;SelectMethod=cursor;Databas
eName=nglic</value>
	    </parameter>
	  </ResourceParams>