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 )

Search not more ! here it is

01.  First you might want to, download the proper JDBC Driver from the Sybase website. download the ZIP file and find the jconn.jar file which is located in the \ jConnect-6_0\classes folder.
02.  Place the jconn.jar file in your {Kettle Folder}\libext\JDBC\ folder

Kettle installation\libext\JDBC folder

And now the database settings.

03.  Start Kettle, and bring up your DB settings window, and configure is as follows.

Connection Type : Generic database
Access :  Native(JDBC)

And under DB access settings

Custom Connection URL     : jdbc:sybase:Tds:<HOST ADDRESS>:<PORT NUMBER>/<DB NAME>
Custom Driver Class Name : com.sybase.jdbc3.jdbc.SybDriver
User Name : <USER NAME>
Password                                             : <DB PASSWORD>

Yup that should be it ! now test your connection, if you did everything correct, Wolah !!

Today when I was running one of my kettle scripts against a large Data Ware House DB, an error popped out in my log, something looks like this,

Unexpected error :
org.pentaho.di.core.exception.KettleDatabaseException:
Couldn’t get row from result set

and further

Unexpected error closing query : org.pentaho.di.core.exception.KettleDatabaseException:
Couldn’t close query: resultset or prepared statements
Communications link failure due to underlying exception: ** BEGIN NESTED EXCEPTION ** java.net.SocketException
MESSAGE: Connection reset STACKTRACE:

java.net.SocketException: Connection reset

This is simply because, after long time in execution the MySQL server get confused at somewhere in the corner and decides to not to feed the rows back to JDBC driver. As I found in MySQL massage board placing SQL_NO_CACHE next to your SELECT keyword (SELECT SQL_NO_CACHE * FROM users) should solve the issue.

I will confirm, if this works since my script is still running, so far so good!

To be verified….

REF :: http://forums.mysql.com/read.php?39,203407,217196#msg-217196