Connection drop problem with Hibernate-mysql-c3p0

This is an issue which I have seen all across the web. I will bring it up again as till now I don't have a fix for the same.

  I am using hibernate 3. mysql 5 and latest c3p0 jar. I am getting a broken pipe exception. Following is my hibernate.cfg file.

com.mysql.jdbc.Driver org.hibernate.dialect.MySQLDialect

    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.use_sql_comments">true</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="connection.autoReconnect">true</property>
    <property name="connection.autoReconnectForPools">true</property>
    <property name="connection.is-connection-validation-required">true</property>

    <!--<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="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider
</property>
    <property name="hibernate.c3p0.acquireRetryAttempts">30</property>
    <property name="hibernate.c3p0.acquireIncrement">5</property>
    <property name="hibernate.c3p0.automaticTestTable">C3P0TestTable</property>

    <property name="hibernate.c3p0.idleConnectionTestPeriod">36000</property>

    <property name="hibernate.c3p0.ini开发者_如何学PythontialPoolSize">20</property>
    <property name="hibernate.c3p0.maxPoolSize">100</property>
    <property name="hibernate.c3p0.maxIdleTime">1200</property>
    <property name="hibernate.c3p0.maxStatements">50</property>
    <property name="hibernate.c3p0.minPoolSize">10</property>-->  

My connection pooling is occurring fine. During the day it is fine , but once i keep it idle over the night ,next day I find it giving me broken connection error.

public class HibernateUtil {

private static Logger log = Logger.getLogger(HibernateUtil.class);
//private static Log log = LogFactory.getLog(HibernateUtil.class);

private static Configuration configuration;
private static SessionFactory sessionFactory;

static {
    // Create the initial SessionFactory from the default configuration files
    try {

        log.debug("Initializing Hibernate");

        // Read hibernate.properties, if present
        configuration = new Configuration();
        // Use annotations: configuration = new AnnotationConfiguration();

        // Read hibernate.cfg.xml (has to be present)
        configuration.configure();

        // Build and store (either in JNDI or static variable)
        rebuildSessionFactory(configuration);

        log.debug("Hibernate initialized, call HibernateUtil.getSessionFactory()");
    } catch (Throwable ex) {
        // We have to catch Throwable, otherwise we will miss
        // NoClassDefFoundError and other subclasses of Error
        log.error("Building SessionFactory failed.", ex);
        throw new ExceptionInInitializerError(ex);
    }
}

/**
 * Returns the Hibernate configuration that was used to build the SessionFactory.
 *
 * @return Configuration
 */
public static Configuration getConfiguration() {
    return configuration;
}

/**
 * Returns the global SessionFactory either from a static variable or a JNDI lookup.
 *
 * @return SessionFactory
 */
public static SessionFactory getSessionFactory() {
    String sfName = configuration.getProperty(Environment.SESSION_FACTORY_NAME);
    System.out.println("Current s name is "+sfName);
    if ( sfName != null) {
        System.out.println("Looking up SessionFactory in JNDI");
        log.debug("Looking up SessionFactory in JNDI");
        try {
            System.out.println("Returning new sssion factory");
            return (SessionFactory) new InitialContext().lookup(sfName);
        } catch (NamingException ex) {
            throw new RuntimeException(ex);
        }
    } else if (sessionFactory == null) {
        System.out.println("calling rebuild session factory now");
        rebuildSessionFactory();
    }
    return sessionFactory;
}

/**
 * Closes the current SessionFactory and releases all resources.
 * <p>
 * The only other method that can be called on HibernateUtil
 * after this one is rebuildSessionFactory(Configuration).
 */
public static void shutdown() {
    log.debug("Shutting down Hibernate");
    // Close caches and connection pools
    getSessionFactory().close();

    // Clear static variables
    sessionFactory = null;
}


/**
 * Rebuild the SessionFactory with the static Configuration.
 * <p>
 * Note that this method should only be used with static SessionFactory
 * management, not with JNDI or any other external registry. This method also closes
 * the old static variable SessionFactory before, if it is still open.
 */
 public static void rebuildSessionFactory() {
    log.debug("Using current Configuration to rebuild SessionFactory");
    rebuildSessionFactory(configuration);
 }

/**
 * Rebuild the SessionFactory with the given Hibernate Configuration.
 * <p>
 * HibernateUtil does not configure() the given Configuration object,
 * it directly calls buildSessionFactory(). This method also closes
 * the old static variable SessionFactory before, if it is still open.
 *
 * @param cfg
 */
 public static void rebuildSessionFactory(Configuration cfg) {
    log.debug("Rebuilding the SessionFactory from given Configuration");
    if (sessionFactory != null && !sessionFactory.isClosed())
        sessionFactory.close();
    if (cfg.getProperty(Environment.SESSION_FACTORY_NAME) != null) {
        log.debug("Managing SessionFactory in JNDI");
        cfg.buildSessionFactory();
    } else {
        log.debug("Holding SessionFactory in static variable");
        sessionFactory = cfg.buildSessionFactory();
    }
    configuration = cfg;
 }

}

Above is my code for the session factory. And I have only select operations .

And below is the method which is used most often to execute my select queries. One tricky thing which I am not understanding is in my findById method i am using this line of code getSession().beginTransaction(); without which it gives me an error saying that this cannot happpen without a transaction. But nowhere I am closing this transaction. And thers no method to close a transaction apart from commit or rollback (as far as i know) which are not applicable for select statements.

public T findById(ID id, boolean lock) throws HibernateException, DAOException { log.debug("findNyId invoked with ID ="+id+"and lock ="+lock); T entity; getSession().beginTransaction();

    if (lock)
        entity = (T) getSession().load(getPersistentClass(), id, LockMode.UPGRADE);
    else
        entity = (T) getSession().load(getPersistentClass(), id);

    return entity;
}

Can anyone please suggest what can I do ? I have tried out almost every solution available via googling, on stackoverlow or on hibernate forums with no avail. (And increasing wait_timeout on mysql is not a valid option in my case).

---

I understand that MySQL can invalidate connections after 'n' hours of no use (see here) for a reference.

So can you configure C3P0 to validate a connection before giving it to you (the client) ? Or configure C3P0 to time out connections after a certain time ? See this link for more info.