Tomcat DBCP : testOnBorrow not working (?)

[José Paumard <Jo...@orange.fr>]

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-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org

Re: Tomcat DBCP : testOnBorrow not working (?)

[Konstantin Kolinko <kn...@gmail.com>]

2010/3/10 José Paumard <Jo...@orange.fr>:
> To be sure that DBCP is not providing closed connections to Hibernate, I
> added the following to the Resource element, but with no success : (...)

Note, that META-INF/context.xml of a web application is copied to
${catalina.base}/conf/Catalina/localhost/<yourappname>.xml
when your application is deployed on Tomcat.

(the path will be different if you have different Service and Host
names. The above ones are the defaults).

Look there and make sure that your updates to context.xml were picked
up by Tomcat.

Best regards,
Konstantin Kolinko

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