Pentaho BI Server MySQL connection (8 hours death)

The MySQL connection's used by the hibernate layer is causing problems after 8 hours of no usage. As the MySQL Server cut's the connection of your server will fail.

The easiest solution is to integrate a JDBC Connection Pooling software into Hibernate.

We suggest that you implement c3p0, it can be found:

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

Once you downloaded the current build unzip it, please note that you only need to deploy the jar file found in the lib folder.

At the time of writing it is c3p0-0.9.1.2.jar

You deploy / copy it into /biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib

Now you need to open the mysql5.hibernate.cfg.xml in /biserver-ce/pentaho-solutions/system/hibernate

Insert after the <session-factory> and before the tag <property name="cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
the following:

    <!--
            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
        -->