Pentaho and MySQL: connection lost after 8 hours

After configuring Pentaho to use MySQL as database backend you eventually discover that Pentaho will give some errors that only clear with a tomcat restart:

com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed due to underlying exception/error

This means that Pentaho has lost the connection to the repository. From the Pentaho wiki:

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.

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.

Installation

We have to download the java binary and install it in your webapps/pentaho/WEB-INF/lib folder.

#stop tomcat
sudo /etc/init.d/tomcat5.5 stop

# download c3p0 somewhere and unzip, copy it to
wget http://downloads.sourceforge.net/project/c3p0/c3p0-bin/c3p0-0.9.1.2/c3p0-0.9.1.2.bin.zip?use_mirror=kent
unzip c3p0-0.9.1.2.bin.zip
sudo cp c3p0-0.9.1.2/lib/*.jar /usr/share/tomcat5.5/webapps/pentaho/WEB-INF/lib/

# give tomcat user ownership
sudo chown -R tomcat55\:  /usr/share/tomcat5.5/webapps/pentaho/WEB-INF/lib/

Configure

We have to configure Hibernate to use c3p0, therefore we have to edit the configuration file mysql5.hibernate.cfg.xml, located in the pentaho-solutions/system/hibernate folder.

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>

Save the file and restart Tomcat.