http://jjant.com

Some days ago i had have issue with my persistent level. The issue was with mysql connection timeout and hibernate c3p0 default connection setup.
Problem:
After 8 hours of inactivity of my application i have a broken pipe exception.
Root of problem:
MySQL automatically times out, and closes unused connections after 8 hours, and out of the box, Hibernate does not set up C3P0 to appropriately test/refresh its connection pool when connections go stale.
Solution:
After spending some time and googling i have founded couple of articles about this.
One of article says that we should configure idle_test_period, and timeout for c3p0 property(http://www.codefin.net/2007/05/hibernate-and-mysql-connection-timeouts.html). It's not correct, hibernate c3p0 can be used only for development, not for production. For production we should use datasources or other connection pool configuration, but not hibernate c3p0.Here an example of jndi data source on tomcat.

Data source configuration on tomcat 6x, hibernate 3x and jndi
• application/web/META-INF/context.xml or "tomcat installation directory"/conf/context.xml
  

  <Context>
  <Resource name="jdbc/mysqsearch" auth="Container" type="javax.sql.DataSource"
           username="search"
           password="123"
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/sambasearch?autoReconnect=true"
           maxActive="15"
           maxIdle="7"
           validationQuery="Select 1" />
  </Context>

• application/web/META-INF/web.xml
  

  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/mysqsearch</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

• hibernate.cfg.xml
  

  <hibernate-configuration>
      <session-factory>
          <property name="hibernate.connection.datasource">java:comp/env/jdbc/mysqsearch</property>
          <property name="hibernate.hbm2ddl.auto">update</property>
          <property name="hibernate.dialect">org.hibernate.dialect.MySQLMyISAMDialect</property>
      	<mapping resource="demo/beans/NetResources.hbm.xml"/>
      </session-factory>
  </hibernate-configuration>

• An example of getting session factory
  

  SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();

Testing:
Waiting 8 hours to conduct a test like this would be borring, so you can change connection timeout for the MySQL server or re-start the MySQL server, and our application to try it out. You can change the timeout time for MySQL by editing your /ect/my.cnf file (linux) or your my.ini file(windows). You would want to add the following line to the file:

wait_timeout=120

Note that the value after the property is in seconds. Once you're done with your testing, you can remove the property and it will default back to 8 hours.
Some usefull links, which was used when i prepared this article:
http://www.codefin.net/2007/05/hibernate-and-mysql-connection-timeouts.html
http://www.hisham.cc/articles/2008/01/06/on-tomcat-6-x-hibernate-3-x-mys...