View Full Version : Pentaho BI purely on MySQL

05-01-2006, 11:47 PM
I was trying to run Pentaho suite purely against MySQL DB but without success. I'm able to run reports against MySQL datasource but Pentaho BI still requires Hypersonic DB. How can I get rid off it?



05-02-2006, 07:30 AM
Our next big release should be natively integrated with MySQL for everything Pentaho. However, a user posted some information on this topic.


Some additional action sequences (particularly the MDX ones) will need to be changed to use the MySQL JDBC driver and connection information.

I hope this helps,


05-03-2006, 05:46 AM
That's great. I won't chase it anymore and I'm waiting for next release of Pentaho BI.


05-08-2006, 08:45 AM
by the next big release do you mean this milestone 5 that just got release or are you taling version 2? when it gets released? b/c I was looking over the docs for 1.1.5 and hoping to see something about it, but I did not so I figured I would ask a question.

just wondering becuase I'm looking at cuting out the hsqldb by following the link post by marc and following the drictions there?



05-09-2006, 05:11 AM
We're evaluating an installer for Pentaho that handles a pure MySQL solution. We'll keep everyone posted.

But, it's coming.

05-09-2006, 07:09 AM
that's great news Marc.

I wouldn't mind just having the standard PCI of pentaho that also was packaged with the scripts to make the schema's and populate them with the list of files that would need to be altered. that way we wouldn't have to do the whole hsqldbutil thing to move over to mysql, and in that post they do this what needs to be changed.

In my option this would be more for the advanced users not so much for the people that want to evaluate them.

Can I ask if you have some time, how are you planning on implementing this installer? where it's like an RPM prepackaged with mysql were it deploys everything for you or will it be deploying to an excisting DB and the such.

Thanks for the update Marc,

Have a great day,


P.S. I have said the countless times and I just feel the need to keep saying it.... Pentaho what a great solution.

05-12-2006, 03:20 PM
well I started the process of moving stuff over to mysql.

attachted is a zip file that has a kettle transaction in it with sql script files that you can do a mysql < %file%.sql and will create the tables you'll need to create the DB's before you do that though.

it's a start for me. once I get the datasources changed over and everything else changed over i'll post them here as well for anyone else that is interesting. and if you get to them before I do please toss them up here as well.

Thanks enjoy,

Nic http://forums.pentaho.org/archived_att/files/Hibernat_to_mysql.zip

05-24-2006, 10:33 AM
I have been basing what I have been doing on the link that Marc gave a couple post ago ie: http://forums.pentaho.org/showthread.php?t=25720
and as I'm working through it I had a couple of questions.

One has you doing some changes in directories like:

to files like hibernate.cfg.xml

as well as some in the solutions/system/

my consern is that I'm using the SDK and I was wondering if there is perhaps a better place to edit such directories and files for when the solution is build via ant?

From looking over the structure of the SDK I see that similar files are located in the solutions/system directory. would this be a better place to edit such files so that changes will not get over writen and the such.

Any suggestions would be great.



05-25-2006, 07:03 AM
I am very interested in this, +1 - please keep us posted :-)

05-25-2006, 08:20 PM
haha I got it working.

by following the link I mentioned before helps to get you on your way.

but it's not right on anymore. the mysql configuration in the hibernate.cfg.xml is not there at all. I was able to get some help with this from the jboss portal 2.2 reference guide it has a sample config for mysql. the config I ended up using is below.
I'm replacing the first greater than signs with |'s other wise most of the xml will go bye bye.

|?xml version='1.0' encoding='UTF-8'?>
|!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
|!-- a SessionFactory instance listed as /jndi/name -->
|session-factory name="java:«»Pentaho/SessionFactory">
|!-- properties -->
|property name="connection.datasource">java:/comp/env/jdbc/YOURDATASOURCEforMYSQL|/property>
|property name="connection.driver_class">com.mysql.jdbc.Driver|/property>
|property name="connection.url">jdbc:mysql://servername/hibernate|/property>
|property name="connection.username">username|/property>
|property name="connection.password">password|/property>
|property name="dialect">org.hibernate.dialect.MySQLDialect|/property>
|property name="show_sql">false|/property>

besure to copy the mysql driver is the lib directories.
and to change the datasources in the Deploy and the hard coded connections in the other samples.

I have not fully tested it but it worked enough I could run some sample reports and run the stuff that I created.

the datasources I changed were

I did not need to change the hsqlds

I think that's most of it.

I also use the mysql files I posted earlier as well.

I hope this helps other get up and going quicker with just running on mysql.

Now I can work on getting the init scripts for jboss working agian.

May the force be with you.


05-26-2006, 11:31 AM
a note I would like to add for the SDK is that where you can configure the hibernate.cfg.xml. the best place I have found to do that IN THE SDK to in the SDK PATH/pentaho/server/pentaho/res/bibernate/hsqldb

that way when you run the ant update or setup scripts it'll pull the same info each time.

I hope this info helps some people that are trying to do the same.


05-26-2006, 04:42 PM
after digging a bit further into the SDK/pentaho/server/pentaho/res/ directory there is a lot of interesting stuff in there.

Namely under hibernate there is a mysqlX folder that'll help you get the hibernate to mysql connection up faster. as well as under jboss you have a list of all the datasources you'll need to change to point to the mysql server as well.

This seems like the best spot to edit such files. But that brings me to my question that I have now. Never mind I got my question answered it's amazing what a lil looking will do. the question was where to change the ant build.xml to use the mysql5/hibernate.cfg.xml file, which would be your RDBMS.

So to change your rdbms from hsqldb to mysql5 you need to edit the default.properties located by the ant build.xml at around lil 61 looks something like this:

#rdbms choice - valid values are hsqldb, mysql4, mysql5, postgresql

that should really help get things going a LOT easier once you have the db's created in mysql. So far I haven't had any problem with the populate scripts I created and posted earlier.

I hope is it helping someone else besides just me, but you learn a lot from doing and I'm learning a lot.


06-06-2006, 11:32 AM
the rdbms info has been relocated to the default.properties in 1.1.6.


06-14-2006, 11:02 AM
after futher lookings I have found that my hibernate instance is not using the DB i have set for it to use in mysql5/hibernate.cfg.xml file. which I have created for it but it's useing my "warehouse" db instead.

I have checked all my datasource in sdk/server/pentaho/res/jboss/datasources and the hibernate.cfg.xml file and they all seem to point to the correct location though hibernate is trying to use a different db.

Any suggestion of why this might be or where to look.



06-14-2006, 11:06 AM
I think I see my mistake I had the connection.datasource set to the wrong datasource.

in the hibernate.cfg.xml file.


06-15-2006, 06:00 PM
For me to be sure, I'd need to see your web.xml, and your hibernate.cfg.xml. Where are you putting the hibernate.cfg.xml?

Take care,


06-19-2006, 07:16 AM
Marc, thanks for the reply.

I did resolve the problem, it was the datasource issue. The Hibernate.cfg.xml is the one in the SDK once I change the rmdbs to mysql5 and resides in the corrisponding directory.

this line was the problem:

<property name="connection.datasource">java:/comp/env/jdbc/MySqlDS</property>

I changed it to:

<property name="connection.datasource">java:/comp/env/jdbc/Hibernate</property>

and I no longer have a problem.

Thanks for the consern,


06-20-2006, 01:07 PM

Btw, Nic, thanks for your informative posting on this topic. It's really helped me get up and going with Pentaho using MySQL.

Currently though, I'm having a problem getting MDX queries working with MySQL.

I changed the MDX_Datasource.xaction to have this connection information:

<location>mondrian; JdbcDrivers=com.mysql.jdbc.Driver</location>

But now I get this error:



at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1903)
at com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:483)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:965)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2669)
at com.mysql.jdbc.Connection.<init>(Connection.java:1474)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:51)
at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:771)
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)
at mondrian.rolap.RolapSchema.getDialect(RolapSchema.java:263)
at mondrian.rolap.RolapStar.<init>(RolapStar.java:125)
at mondrian.rolap.RolapSchema.makeRolapStar(RolapSchema.java:1350)
at mondrian.rolap.RolapSchema.access$500(RolapSchema.java:49)
at mondrian.rolap.RolapSchema$RolapStarRegistry.getOrCreateStar(RolapSchema.java:1372)
at mondrian.rolap.RolapCube.<init>(RolapCube.java:93)
at mondrian.rolap.RolapCube.<init>(RolapCube.java:152)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:309)
at mondrian.rolap.RolapSchema.load(RolapSchema.java:229)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:170)
at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:49)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:797)
at mondrian.rolap.RolapSchema$Pool.get(RolapSchema.java:636)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:138)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:73)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:120)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:87)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:55)
at org.pentaho.data.connection.mdx.MDXConnection.init(MDXConnection.java:87)
at org.pentaho.data.connection.mdx.MDXConnection.<init>(MDXConnection.java:60)
at org.pentaho.data.PentahoConnectionFactory.getConnection(PentahoConnectionFactory.java:143)
at org.pentaho.plugin.mdx.MDXBaseComponent.getConnection(MDXBaseComponent.java:243)
at org.pentaho.plugin.mdx.MDXBaseComponent.getDatasourceConnection(MDXBaseComponent.java:162)
at org.pentaho.plugin.mdx.MDXBaseComponent.executeAction(MDXBaseComponent.java:109)
at org.pentaho.plugin.ComponentBase.execute(ComponentBase.java:391)
at org.pentaho.core.runtime.RuntimeContext.executeComponent(RuntimeContext.java:967)
at org.pentaho.core.runtime.RuntimeContext.executeAction(RuntimeContext.java:939)
at org.pentaho.core.runtime.RuntimeContext.performActions(RuntimeContext.java:848)
at org.pentaho.core.runtime.RuntimeContext.executeLoop(RuntimeContext.java:812)
at org.pentaho.core.runtime.RuntimeContext.executeSequence(RuntimeContext.java:700)
at org.pentaho.core.runtime.RuntimeContext.executeSequence(RuntimeContext.java:644)
at org.pentaho.core.solution.SolutionEngine.executeInternal(SolutionEngine.java:248)
at org.pentaho.core.solution.SolutionEngine.execute(SolutionEngine.java:201)
at org.pentaho.core.services.BaseRequestHandler.handleActionRequest(BaseRequestHandler.java:132)
at org.pentaho.ui.servlet.ViewAction.doGet(ViewAction.java:117)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:595)


If anyone has any suggestions/fixes, please let me know. This whole thing is giving me a headache. At first, Pentaho didn't even want to give me this level of detail concerning the error. I had to put in a break point at MDXConnection[line: 92] in order to see the exception.

I am using the June 9th version of the SDK, MySQL 5, and Tomcat 5.5.

I have gotten most things to work under MySQL. Birt, Jasper, and JFree reports all work. So does the dashboarding.

David http://forums.pentaho.org/archived_att/files/MDX_Datasource.xaction

06-20-2006, 01:37 PM
Thanks David, that's what a community is for. Helping other get over the same hurdles you have over came.

well the first thing I see is the port for you datasource. I beleive it should be 3306 not 8080.

And since the EOF occurs during the hand shake I would guess that's the solution to your problem as well.

Hope that helps,


06-21-2006, 05:59 AM
Thanks for pointing that out for me. I must have been pretty fried yesterday. Anyways, after I fixed that, I got the typical login error. Apparently setting the user-id and password fields still doesn't work. So, I fixed the URL so that the login information was sent over it, and presto; A working MDX report.


06-21-2006, 08:26 AM

Glad you got it working,

Yeah Mondrian and MDX have a few quarks here and there as you'll discover but most of them are posted about in the analysis issues/features.

If you need any more help we are here.

May the force be with you.


07-08-2006, 05:15 AM
I do not thing that 8080 is the right port number for mysql.
Default value is 3306. 8080 is jboss default port number...