2

I have read so many articles on the Internet about this problem but none seem to have a clear solution. Please could someone give me a definite answer as to why I am getting database timeouts. The app is a GWT app that is being hosted on a Tomcat 5.5 server. I use spring and the session factory is created in the applicationContext.xml as follows

<bean id="sessionFactory"
      class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${connection.dialect}</prop>
            <prop key="hibernate.connection.username">${connection.username}</prop>
            <prop key="hibernate.connection.password">${connection.password}</prop>
            <prop key="hibernate.connection.url">${connection.url}</prop>
            <prop key="hibernate.connection.driver_class">${connection.driver.class}</prop>
            <prop key="hibernate.show_sql">${show.sql}</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.c3p0.min_size">5</prop>
            <prop key="hibernate.c3p0.max_size">20</prop>
            <prop key="hibernate.c3p0.timeout">1800</prop>
            <prop key="hibernate.c3p0.max_statements">50</prop>
            <prop key="hibernate.c3p0.idle_test_period">300</prop>
        </props>
    </property>
    <property name="annotatedClasses">
        <list>
            <value>za.co.xxxx.traintrack.server.model.Answer</value>
            <value>za.co.xxxx.traintrack.server.model.Company</value>
            <value>za.co.xxxx.traintrack.server.model.CompanyRegion</value>
            <value>za.co.xxxx.traintrack.server.model.Merchant</value>
            <value>za.co.xxxx.traintrack.server.model.Module</value>
            <value>za.co.xxxx.traintrack.server.model.Question</value>
            <value>za.co.xxxx.traintrack.server.model.User</value>
            <value>za.co.xxxx.traintrack.server.model.CompletedModule</value>
        </list>
    </property>
</bean>

<bean id="dao" class="za.co.xxxx.traintrack.server.DAO">
    <property name="sessionFactory" ref="sessionFactory"/>
    <property name="adminUsername" value="${admin.user.name}"/>
    <property name="adminPassword" value="${admin.user.password}"/>
    <property name="godUsername" value="${god.user.name}"/>
    <property name="godPassword" value="${god.user.password}"/>
</bean>

All works fine untile the next day:

INFO   | jvm 1    | 2010/06/15 14:42:27 | 2010-06-15 18:42:27,804 WARN 
[JDBCExceptionReporter] : SQL Error: 0, SQLState: 08S01
INFO   | jvm 1    | 2010/06/15 14:42:27 | 2010-06-15 18:42:27,821 ERROR 
[JDBCExceptionReporter] : The last packet successfully received from the server 
was 38729 seconds ago.The last packet sent successfully to the server was 38729
seconds ago, which  is longer than the server configured value of 'wait_timeout'.
You should consider either expiring and/or testing connection validity before 
use in your application, increasing the server configured values for client
timeouts, or using the Connector/J connection property 'autoReconnect=true' to
avoid this problem.
INFO   | jvm 1    | 2010/06/15 14:42:27 | Jun 15, 2010 6:42:27 PM 
org.apache.catalina.core.ApplicationContext log
INFO   | jvm 1    | 2010/06/15 14:42:27 | SEVERE: Exception while dispatching
incoming RPC call

I have read so many different things (none of which worked), please help

Improve this question
6
  • 1
    Which things did you try? The error message is namely already the whole answer at its own. Commented Jun 15, 2010 at 19:04
  • But why does the hibernate.c3p0.idle_test_period seem to be ignored? Is this trace generated by code using C3P0? Commented Jun 15, 2010 at 19:30
  • Your error message says that the data base server was unreachable for eleven hours. Are you really sure you even had a network connection during the night? Commented Jun 15, 2010 at 19:31
  • @Kilian I've had a similar issue, had to restart jboss every day or so and application was being used normally all that time. Gonna watch this. Commented Jun 15, 2010 at 19:36
  • Killian, it is not so much that the database was unreachable, but that a connection has not been requested for 11 hours (so if you don't use the app for a day you are screwed). If I bounce the server and effectively create fresh connections in the pool, the app works fine for as long as I don't leave it for a day Commented Jun 16, 2010 at 17:16

2 Answers 2

Reset to default
1

Look at this link http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/

 In “hibernate.cfg.xml”:

<!-- hibernate.cfg.xml -->
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>
<property name="connection.provider_class">
   org.hibernate.connection.C3P0ConnectionProvider</property>
<!-- no "connection.pool_size" entry! -->

Then create a file “c3p0.properties” which must be in the root of the classpath (i.e. no way to override it for particular parts of the application):

# c3p0.properties
c3p0.testConnectionOnCheckout=true
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Romain, I found this article last week and tried the c3p0.properties file without luck, but I did not add the connection.provider_class property, so I will retry tonight
@Clinton Bosch let us know how it works. Also make sure to put the exact properties as specified in the post. If it works you can tweak later.
Romain, unfortunately this does not work for me, the connection still times out.
@Clinton Can you post your hibernate.cfg.xml, c3p0.properties and applicationContext.xml Also any newer stack traces
0

Romain, I do not have a hibernate.cfg.xml (all done in applicationContext.XML)

c3p0.properties

c3p0.testConnectionOnCheckout=true

applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-2.5.xsd
           http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
           http://www.springframework.org/schema/tx
           http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
           ">

    <context:annotation-config/>

    <context:component-scan base-package="za.co.cmsoftware.traintrack.server"/>

    <!-- Application properties -->
    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>file:${user.dir}/@propertiesFile@</value>
            </list>
        </property>
    </bean>

    <bean id="sessionFactory"
          class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">${connection.dialect}</prop>
                <prop key="hibernate.connection.username">${connection.username}</prop>
                <prop key="hibernate.connection.password">${connection.password}</prop>
                <prop key="hibernate.connection.url">${connection.url}</prop>
                <prop key="hibernate.connection.driver_class">${connection.driver.class}</prop>
                <prop key="hibernate.show_sql">${show.sql}</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
                <prop key="hibernate.c3p0.min_size">5</prop>
                <prop key="hibernate.c3p0.max_size">20</prop>
                <prop key="hibernate.c3p0.timeout">1800</prop>
                <prop key="hibernate.c3p0.max_statements">50</prop>
                <prop key="hibernate.c3p0.idle_test_period">300</prop>
                <prop key="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>
            </props>
        </property>
        <property name="annotatedClasses">
            <list>
                <value>za.co.xxxx.traintrack.server.model.Answer</value>
                <value>za.co.xxxx.traintrack.server.model.Company</value>
                <value>za.co.xxxx.traintrack.server.model.CompanyRegion</value>
                <value>za.co.xxxx.traintrack.server.model.Merchant</value>
                <value>za.co.xxxx.traintrack.server.model.Module</value>
                <value>za.co.xxxx.traintrack.server.model.Question</value>
                <value>za.co.xxxx.traintrack.server.model.User</value>
                <value>za.co.xxxx.traintrack.server.model.CompletedModule</value>
            </list>
        </property>
    </bean>

    <bean id="dao" class="za.co.xxxx.traintrack.server.DAO">
        <property name="sessionFactory" ref="sessionFactory"/>
        <property name="adminUsername" value="${admin.user.name}"/>
        <property name="adminPassword" value="${admin.user.password}"/>
        <property name="godUsername" value="${god.user.name}"/>
        <property name="godPassword" value="${god.user.password}"/>
    </bean>

    <bean id="transactionManager"
          class="org.springframework.orm.hibernate3.HibernateTransactionManager">
        <property name="sessionFactory">
            <ref local="sessionFactory"/>
        </property>
    </bean>

    <!-- enable the configuration of transactional behavior based on annotations -->
    <tx:annotation-driven transaction-manager="transactionManager"/>

</beans>
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.