Dealing with MySQL connection idle connection timeout

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>