c3p0 configuration

[Pablo M�ller]

Hi,
I have this problem: Our application is running on a tomcat 5.5 and uses hibernate with a mysql 5 database. It works great during office hours, but when we arrive in the morning, the tomcat is still running, but the database connections had been closed by the database. Hibernate doesn't know about this and tries to use them. If we reload the page after the exception we get, it starts working, because it creates new connections.
I've been reading about this (http://www.mchange.com/projects/c3p0/index.html#configuring_recovery) and tried to configure c3p0 to handle this, but I get te same error. Also I don't know how to test this properly, so far I've been killing the connection thread with the mysql administrator.

This is the hibernate.cfg.xml

    
<hibernate-configuration>
 
    <session-factory>
 
        <!-- Database connection settings -->
 

        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="connection.url">jdbc:mysql://localhost/omwa_real?autoReconnect=true</property>
        <property name="connection.username">root</property>
        <property name="connection.password">xxxxx</property>
 

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>
 
        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
 
        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>
 
        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
 
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">false</property>
 
        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">update</property>

<property name="hibernate.c3p0.idle_test_period">3</property>
<!-- property name="hibernate.c3p0.max_size">100</property-->
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>

        <mapping resource="omwa/dao/hibernate/application/Model.hbm.xml"/>
        <mapping resource="omwa/dao/hibernate/model/Model.hbm.xml"/>        
 
    </session-factory>
 
</hibernate-configuration>

This is my c3p0.properties, which is located in the src folder of the project (the root of all packages).. does it go there?

    
c3p0.acquireRetryAttempts=50
c3p0.acquireRetryDelay=25
c3p0.maxConnectionAge=120
c3p0.automaticTestTable=connection_test_table
c3p0.testConnectionOnCheckout=true

Is my configuration ok? How can I prevent this error from happening? If it does happen, how can I prevent the application from failing?
Thanks a lot.
Pablo.

[gustavo sanchez]

Im having the same problem, when i see the stack of tcp connections from tomcat to hibernate, all the connections are in "close wait" state.
I cant figure the reason, i hope someone can help !

[Pablo M�ller]

We modified the hibernate.cfg.xml to use a datasource, instead of passing the connection parameters there, and it seems to be working that way.
Unfortunatelly this is only a workaround and we still need to fix it the other way because we can't add datasources to the production server without restarting it, so applications should be able to handle the connection pool properly on their own.
Thanks again

[Pablo M�ller]

I found this link:
http://michaelstudman.com/fullfathomfive/articles/2004/06/07/mysql-dropping-connections-and-hibernate
He had the same problem, and fixed it using dbcp as the connection pool. So I downloaded it and configured the hibernate.cfg.xml as he did, but nothing happened. He says that hibernate will use dbcp simply adding a dbcp configuration property to the hibernate.cfg.xml. Well, I copied his config and this is what I get from hibernate:

    
17:08:30,550  INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
17:08:30,560  INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
17:08:30,560  INFO DriverManagerConnectionProvider:45 - autocommit mode: false
17:08:30,570  INFO DriverManagerConnectionProvider:80 - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://localhost/omwa_real?autoReconnectForPools=true
17:08:30,570  INFO DriverManagerConnectionProvider:83 - connection properties: {user=root, password=xxxxxx}
17:08:30,580 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
17:08:30,580 DEBUG DriverManagerConnectionProvider:109 - opening new JDBC connection
17:08:31,121 DEBUG DriverManagerConnectionProvider:115 - created connection to: jdbc:mysql://localhost/omwa_real?autoReconnectForPools=true, Isolation Level: 4
.
.
.

As you can see it's still using hibernate's default connection pool. Am I missing something?
Thanks a lot.