Pentaho 3.6 Connection Pooling with MySQL using c3p0

This post is in relation to connection timeouts when using MySQL 5.x with Pentaho.  The default setting is for MySQL DB connections to timeout after 8 hrs, causing Tomcat to lose connections overnight, and resulting in strange errors within the Pentaho User Console the next day. One solution to this problem is to use c3p0 for connection pooling, and also to change some of the default connection settings for MySQL.

I had some issues setting this up correctly, and the steps outlined in the Pentaho Solutions book are slightly incorrect, so I figured I would document what I did to resolve this. I set this up on Ubuntu 10.04.

Download c3p0 for linux from sourceforge and follow the instructions below.

Copy c3p0-x.x.x.x.jar to the following locations (and verify proper permissions):

<pentaho_install_directory>/biserver-ce/tomcat/webapps/pentaho/WEB-INF/lib/
<pentaho_install_directory>/biserver-ce/tomcat/common/lib/
<pentaho_install_directory>/administration-console/lib/

File:

<pentaho_install_directory>/biserver-ce/pentaho-solutions/system/hibernate/mysql5.hibernate.cfg.xml

Edit as follows:

1. Insert the text below immediately before the <!-- mySQL configuration --> comment.

<!-- c3p0 connection pooling 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">14400</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>