Tomcat DBCP : testOnBorrow not working (?)

[José Paumard]

Hello all,

Here is my problem. I have a very classical configuration where a webapp 
with some Hibernate code in it accesses a MySQL DB. Everything is 
installed in Tomcat 6.

After some time, Hibernate complains about dropped connections, the 
error message looks like this :
com.mysql.jdbc.CommunicationsException : Communications link failure The 
last packet successfully received from the server was 204 734 
milliseconds ago. The last packet sent successfully to the server was 0 
milliseconds ago.
java.io.EOFException : Can not read response from server. Expected to 
read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Refeshing the page once or twice usually fixes the problem.

I could narrow the problem and reproduce it on demand, juste by setting 
the wait timeout parameter of MySQL to a very low value : 10s (the 
default value is 8 hours). If I use my webapp fast enough (less than 10s 
between clicks) everything runs fine. If I wait just 12s, this problem 
shows up, 100% of the time. Reloading the page once or twice fixes it.

So my guess here is that Hibernate is provided with a closed connection 
(please correct me if you think I'm wrong on that point). Setting 
autoReconnect=true on the connection string doesnt help (I red somewhere 
that it was not expected to help in the case of MySQL for some reason, 
so I guess it's normal).

The configuration is the following : the datasource is configured in a 
context.xml file in the META-INF dir of the webapp.
 <Resource name="jdbc/myds" auth="Container" type="javax.sql.DataSource"
           maxActive="20" maxIdle="10" maxWait="3"
           username="***" password="***" 
driverClassName="com.mysql.jdbc.Driver"
           
url="jdbc:mysql://localhost/mydb?useUnicode=true&characterEncoding=utf8"/>

Then the webapp uses it through a declaration in a persistence.xml file 
(JPA standard file).

   <persistence-unit name="MyDataService">
       <provider>org.hibernate.ejb.HibernatePersistence</provider>
       <non-jta-data-source>java:/comp/env/jdbc/myds</non-jta-data-source>
       <!-- JPA stuff removed -->
   </persistence-unit>

To be sure that DBCP is not providing closed connections to Hibernate, I 
added the following to the Resource element, but with no success :
 <Resource name="jdbc/myds" auth="Container" type="javax.sql.DataSource"
           maxActive="20" maxIdle="10" maxWait="3"
           username="***" password="***" 
driverClassName="com.mysql.jdbc.Driver"
           
url="jdbc:mysql://localhost/mydb?useUnicode=true&characterEncoding=utf8"
           validationQuery="select 1" testOnBorrow="true"/>

From what I understand in the DBCP docs :
- the validation query has to be set to some legal value for DBCP to 
test the connection, which is the case
- testOnBorrow is true by default, setting it again cant harm can it ?

I tried to tune timeBetweenEvictionRunsMillis, numTestsPerEvictionRun 
and minEvictableIdleTimeMillis with testWhileIdle set to true, but with 
no more success.

Any hint would be much appreciated, especially on the logging side 
(Tomcat / DBCP), since I cant see how to get more information on what is 
going on with those buggy connections.

Thank you,

José




---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org