Dealing with MySQL connection death after 8 hours

Hardly anything is more frustrating than coming back the day after to find your beautifully configured Pentaho installation handing up obscure error messages and seeming to hobble about as you log in and try to execute reports.

 

MySQL connections timeout by default every 8 hours.  If Pentaho sits idle for more than 8 hours, its connection to the repository silently drops dead without Pentaho's knowledge.  When something stirs Pentaho after an 8 hour or greater time lapse, Pentaho assumes that its connection is still alive and currently there is no default mechanism in place that handles bringing a connection back to life.  Enter c3p0 (JDBC Connection Pooling software).

 

The solution turns out to be fairly easy.  What is required is a piece of software to manage the database connection pool and keep the connections alive.  The recommended method is to use c3p0.

 

More information about c3p0 can be found on the project's site:

 

http://sourceforge.net/projects/c3p0
http://www.mchange.com/projects/c3p0/index.html

To use c3p0 to solve this problem, first download the c3p0-0.9.1.2.bin.zip, stop your Java application server, take the jar files c3p0-0.9.2.jar and mchange-commons-java-0.2.3.3.jar inside the downloaded archive and copy them to your WEB-INF/lib directory on your java application server. I use Tomcat 6 on Ubuntu 8.10, so my installation path happens to be:

/var/lib/tomcat6/webapps/pentaho/WEB-INF/lib

Next, we'll need to modify the hibernate settings for MySQL.  The file we will modify is called mysql5.hibernate.cfg.xml and is located in the pentaho-solutions/system/hibernate folder by default.

 

You will insert the following text just after the <session-factory> tag and just before the <!-- MySQL Configuration --> comment.

        <!--
            hibernate c3p0 settings
        -->

        <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>
        <property name="hibernate.c3p0.idle_test_period">10</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">75</property>
        <property name="hibernate.c3p0.max_statements">0</property>
        <property name="hibernate.c3p0.timeout">25200</property>
        <property name="hibernate.c3p0.preferredTestQuery">select 1</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>


        <!--
            hibernate cache settings
        -->
	<!-- End of patch added Friday April 3, 2009 to address issues of the database connection going dead
	-->

        <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
        <property name="hibernate.generate_statistics">true</property>
        <property name="hibernate.cache.use_query_cache">true</property>

 

That's all there is to it.  Save the file and restart your application server and the dead connection problem should go away.