FanaticZine

Are you experiencing the same problem as the title ? are you working with a MySQL DB server. Then the cause for this problem is. The MySQL driver remove it’s connection from the connection pool due to inactivity after few hours if the server is not processing anything though the db connection.

There is a solution introduced in the Pentaho forum for this, you can find the thread with the following link.

http://forums.pentaho.com/showthread.php?54939-Pentaho-Server-falls-each-night-..&p=178067#post178067

But above forum post is based on the JBoss server. My intention with this post is to implement the same on the tomcat server since the file structure is a bit different. Ok without further due, let get into action.

01.    Download the C3P0 driver from SourceForge
02.    Extract the  c3p0-0.9.1.2.jar file into \biserver-ce\tomcat\common\lib\
03.    Open the mysql5.hibernate.cfg.xml XML file in the \biserver-ce\pentaho-solutions\system\hibernate\ folder
04.    Add the following code into the file

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

05.    Save and Restart the B.I Server or Service

That should do the trick…

( When you are editing the XML file, please be careful with the quotation characters )

If you are trying to restore a large database backup back to  your MySQL Server, You know that phpMyAdmin is NOT the best solution for the task, beacause of it’s execution limitaion settings it will reject your file being executed. So one way to archive this is to use the mysql command prompt interface. the syntaxt to back up your database unsing command links is.

mysql -u {UserName} -p {TargetDatabase} < {SourceDumpFile}

{UserName} = Your Database username

{TargetDatabase}= Your Target Databse

{SourceDumpFile}=  Your  SQL Dump File

once you run the above line correctly. you should be prompt for a password, Enter your database password and continue. (the restoring time  depends on the size of your database, so be patient and a grab a coffee if it’s so large.)