MySQL Connection Timeout Error in Hibernate

Problem

I am using Hibernate and MySQL in myour project. After our application is started, it just works well. However, if the application is not used for some time (like one day), it throws connection exception when it is used again. Trace of the exception:

com.mysql.jdbc.CommunicationsException: 
The last packet successfully received from the server was seconds ago.
The last packet sent successfully to the server was 407270 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.

I googled this problem, and it seems that many other persons also suffer this problem. I found some really nice blog posts about how to solve this problem.

According to the error message, I set the option autoReconnect (http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html) to true. But it still does not work. I searched in google and it shows this option works in some platforms while it doesn't work in other platforms.

Solution

Basically, the solution is 1) use a connection manager 2) tweak parameters of the connection manager.

C3P0 is a free JDBC connection manager.
Some C3p0 configuration parameters are mapped to Hibernate config. Those mapped configuration parameters are listed at http://www.mchange.com/projects/c3p0/index.html#hibernate-specific.

"Please note that configuration parameters that ARE mapped into hibernate's config file MUST be set within hibernate's configuration, or else they will be overridden by hibernate-specified defaults.
[The hibernate-mapped c3p0 parameters are minPoolSize, maxPoolSize, maxIdleTime, maxStatements, acquireIncrement, testConnectionOnCheckout, and idleConnectionTestPeriod. These map to the fllowing hibernate parameters: hibernate.c3p0.min_size, hibernate.c3p0.max_size, hibernate.c3p0.timeout, hibernate.c3p0.max_statements, hibernate.c3p0.acquire_increment, hibernate.c3p0.validate, and hibernate.c3p0.idle_test_period. DataSources configured in Tomcat should always use c3p0-native parameter names. But pools constructed under the covers by Hibernate must be configured in hibernate's config file with hibernate-defined parameter names where applicable.] "

For those C3p0 parameters that are NOT mapped, they should be specified in file WEB-INF/classes/c3p0.properties.
Note: C3P0 configuration parameter <c3p0.testConnectionsOnCheckout> is mapped to <hibernate.c3p0.validate> only in hibernate 2.x!
Also the configuration parameter c3p0.testConnectionsOnCheckout is really expensive. Users can use c3p0.maxIdleTime (it is mapped to hibernate.c3p0.timeout in hibernate) instead.

My hibernate.cfg.xml includes following configuration:

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="c3p0.min_size">5</property>
        <property name="c3p0.max_size">30</property>
        <property name="c3p0.timeout">600</property>
        <property name="c3p0.max_statements">0</property>
        <property name="c3p0.acquire_increment">5</property>
        <property name="c3p0.idle_test_period">60</property>

The idle test operation can be optimized by specifying option preferrredTestQuery.
I am using MySQL as underlying RDB and putting c3p0.preferredTestQuery = SELECT 1 into c3p0.properties works fine (this is stolen from post comment http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/#comment-3156).

Resources

A really good article about how to reconnect from Hibernate to MySQL: http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/
C3P0 (Connection manager): http://www.mchange.com/projects/c3p0/index.html
How to configure C3P0 in Hibernate: https://www.hibernate.org/214.html
A forum thread about C3P0 configuration in Hibernate: https://forum.hibernate.org/viewtopic.php?t=934779&highlight=c3p0

Hibernate installation (run on MySQL)

Recently, I tried to install Hibernate on gridfarm machine. The version I used was 3.3.0 SP1.
Configuration document can be found here. However, the document seems not to be updated.
(1) Configuration file hibernate.properties vs. hibernate.cfg.xml.
In the document, either one can be used. If both are used, configurations in hibernate.cfg.xml would override configurations in hibernate.properties. The document says using hibernate.properties is an easy way to configure Hibernate. So I chose it. However, after trial I found that in Hibernate 3.3.0 SP1 hibernate.cfg.xml. In other words, you MUST have a configuration file "hibernate.cfg.xml". Of course, you can use programmatical configuration which is not covered in this post.
(2) Logger
In documents, I saw "Hibernate logs various events using Apache commons-logging.". However, when I tried to run my Hibernate app, it complained that logger class can not be found. After inspection, I figured out that Hibernate 3.3.0 does NOT use Apache common-logging any more. Instead it uses slf4j. But in Hibernate 3.3.0 SP1 distribution only slf4j-api jar is included while an implementation is needed to run Hibernate(If you turn off logging, I don't know whether it will still complain). In other words, to use logging functionality, you must download an implementation of logging service supported by slf4j. I used slf4j-simple.
(3) JNDI bindings (for session factory and data source).
Because I just wanted to write a simple prototype without deploying in J2EE server, I chose not to use JNDI bindings for data source or session factory. After several hour trial, I could not get it to work!! I always got the error "org.hibernate.impl.SessionFactoryObjectFactory - Could not bind factory to JNDI".It seemed that Hibernate always was always trying to use JNDI to bind Session Factory. However, the official document says

"A JNDI bound Hibernate SessionFactory can simplify the lookup of the factory and the creation of new Sessions. Note that this is not related to a JNDI bound Datasource, both simply use the same registry!

If you wish to have the SessionFactory bound to a JNDI namespace, specify a name (eg. java:hibernate/SessionFactory) using the property hibernate.session_factory_name. If this property is omitted, the SessionFactory will not be bound to JNDI. (This is especially useful in environments with a read-only JNDI default implementation, e.g. Tomcat.)

When binding the SessionFactory to JNDI, Hibernate will use the values of hibernate.jndi.url, hibernate.jndi.class to instantiate an initial context. If they are not specified, the default InitialContext will be used.

Hibernate will automatically place the SessionFactory in JNDI after you call cfg.buildSessionFactory(). This means you will at least have this call in some startup code (or utility class) in your application, unless you use JMX deployment with the HibernateService (discussed later).

If you use a JNDI SessionFactory, an EJB or any other class may obtain the SessionFactory using a JNDI lookup.

We recommend that you bind the SessionFactory to JNDI in a managend environment and use a static singleton otherwise. To shield your application code from these details, we also recommend to hide the actual lookup code for a SessionFactory in a helper class, such as HibernateUtil.getSessionFactory(). Note that such a class is also a convenient way to startup Hibernate—see chapter 1. "

It turned out that a property called "hibernate.current_session_context_class" must be set.
My hibernate.cfg.xml looks like:

<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="show_sql">true</property>
        <property name="current_session_context_class">thread</property>
        <property name="connection.username">username</property>
        <property name="connection.password">password</property>
        <property name="connection.url">jdbc:mysql://localhost/hibernate</property>
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <mapping resource="User.hbm.xml"/> 
    </session-factory>
</hibernate-configuration>

Then, I tried a very very simple read-only JNDI service provider - simple-jndi. My simple-jndi configuration file (jndi.properties which should be put into root of your classpath) looks like:

#The first (required) parameter, org.osjava.sj.root, is the location 
#of your simple-jndi root, which is the location in which simple-jndi
#looks for values when code asks for them.

org.osjava.sj.root=/home/zhguo/share/simple-JNDI/config
java.naming.factory.initial=org.osjava.sj.SimpleContextFactory

Under directory pointed to by org.osjava.sj.root property, I have a jndi data file called hibernate_jndi.properties:

mysql.type=javax.sql.DataSource
mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost/hibernate
mysql.user=username
mysql.password=password

So, my new version of hibernate.cfg.xml looks like:

<hibernate-configuration>
    <session-factory>
        <property name="show_sql">true</property>
        <property name="current_session_context_class">thread</property>
        <property name="connection.datasource">hibernate_jndi.mysql</property>
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
        <mapping resource="User.hbm.xml"/> 
    </session-factory>
</hibernate-configuration>

Note: in the file, session factory name is not set! This is because we are using a read-only JNDI. If you use a read-write JNDI, you can set the session factory name in one of the following ways:
(*) In hibernate.properties file:
hibernate.session_factory_name hibernate/session_factory
(*) In hibernate.cfg.xml file:
<session-factory name="your name goes here">

Mysql error "InnoDB: Unable to lock ./ibdata1, error: 11"

Recently, there was a power outage in the lab. I did not shut down my machines before the power outage. After I restarted my Ubuntu, I could not start up Mysql. The error was:

InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Error in opening ./ibdata1

But I am 100% sure that no another mysqld process was running. After I searched on line, I found that I was not the only one encountering this error. See this post.
This post has an insight into this problem. It seems to be caused by NFS. Mysql is not installed on a local file system. It is installed on a remote file system which is mounted to other file systems by using NFS.

Solution
make a copy of the original files (ibdata1, ib_logfile0, ib_logfile1...).

mv ibdata1 ibdata1.bak
cp -a ibdata1.bak ibdata1
......