Hey all,
Let me introduce myself for those that don't know me from earlier posts. I'm Marc Batchelor, and I'm the Chief Engineer as well as a co-founder of Pentaho. Next, let me regale you all with a tale of my woe relating to this issue. If you wish, you can skip down to the "Final Solution" section at the bottom.
I first encountered this problem at a customer site with MySQL 4.x and the Pentaho platform about two years ago (or so). At the time, the solution was to use the DBCPConnectionProvider class as described in the forum posts above. This information was captured informally, and propagated to others by word-of-keyboard. The bottom line though was, the solution with DBCP and DBCPConnectionProvider worked (and in fact, still works at the aforementioned customer site).
Now, cut to present day. I was tasked with getting a deployment of Pentaho 1.6.1 going on another internal server for some additional internal BI projects (yeah, we eat our own dog food here). I got the product installed and configured with MySQL, and had everything running in an hour or so. I left the machine running, and then the next day, I found I couldn't connect to the platform because of MySQL issues.
This seemed familiar to me, and so I dug around in correspondence and saved hibernate.cfg.xml files, and found the solution (I thought). I just needed to configure things with DBCP, and all would be right with the world. So, I set up DBCP (by basically copying all the settings I had carved retained from the previous customer engagement and copying over the DBCPConnectionProvider class). Just to be sure, after the platform was up and running, I killed and re-started MySQL!!!
Well, the exceptions flooded across the console, but things seemed to recover just fine. I was able to run reports and use the platform. So, I said to myself rather smugly, that's that! I told everyone that the problem was solved. 
Well, this morning, much to my surprise, the problem happened again! What's the deal? 
In looking at the HTML output, it was clear that the solution repository couldn't be accessed (the template problem). I looked at the stack trace on the server console, and the problem was coming from the handling of prepared statements by DBCP. But, I needed a more efficient way to try to troubleshoot the problem. After a bit of googling, I found out that there is a parameter called wait_timeout that seemed to be the likely suspect. According to the MySQL documentation, this is "The number of seconds the server waits for activity on a connection before closing it". The default is 8 hours, but I wanted to see if it was the cause of my problem. So, I set it to 30 seconds, and restarted MySQL and the platform. After the re-start, I did some playing in the platform, and then let it sit for about a minute. Going back into the platform reproduced the error I saw this morning. So, I now had a quick-and-easy way to test to see if any "fix" I came up with was really a fix.
I must have tried 30+ hibernate.cfg.xml tweaks to the DBCP settings to no avail. No matter what I tried while using the DBCPConnectionProvider, it had no effect. So, I decided to approach the problem from a different direction. That direction was to use a different connection pooling implementation. Instead of using DBCP, I decided to switch to C3P0. This is a connection pooling implementation that is shipped in the hibernate3.jar. All I needed to do is get the c3p0 jar file, and tweak the hibernate.cfg.xml.
Well, after playing with it, I was in fact able to solve the problem completely using C3P0. The following is the cook-book way to make the world right for MySQL once more:
Final Solution- Download C3P0 from SourceForge
- Extract c3p0-0.9.1.2.jar from the .zip file, and place it in jboss/server/default/lib
- Remove all hibernate.dbcp.* properties from your hibernate.cfg.xml - don't forget to remove the property connection.provider_class because we're replacing that line with a new connection provider
- Add the following XML fragments to your hibernate.cfg.xml file
Code:
<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>
Next Steps
I'm going to point this little gem out to our build person and VP of Engineering, and see about getting the C3P0 stuff into the next (and future) builds of the MySQL distribution of the platform. I'm sorry that this went on so long, and hopefully, the next one can get crushed much quicker.
Thanks all for your patience and understanding,
Marc Batchelor
Chief Engineer
Originally Posted by wolfgangsz
Solved - For real this time
Posting Permissions
Reply With Quote
