DealingwithMySQLconnectiondeathafter八hours

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 y

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:

?

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 tag and just before the comment.

        

        org.hibernate.connection.C3P0ConnectionProvider
        3
        10
        5
        75
        0
        25200
        select 1
        true


        
	

        org.hibernate.cache.EhCacheProvider
        true
        true

?

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