Pentaho BI purely on MySQL

**proofek** · 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?

cheers

Seb

**mbatchelor** · 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.

http://forums.pentaho.org/showthread.php?t=25720

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,

Marc

**proofek** · 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.

Seb

**guzaldon** · 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?

thanks,

Nic

**mbatchelor** · 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.

**guzaldon** · 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,

Nic

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

**guzaldon** · 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_a...t_to_mysql.zip

**guzaldon** · 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:

pentaho_demo/jboss/server/default/deploy/pentaho.war/classes/
to files like hibernate.cfg.xml
quartz.properties

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.

Thanks,

Nic

**dhartford** · 05-25-2006, 07:03 AM

I am very interested in this, +1 - please keep us posted :-)

**guzaldon** · 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.

Code:

|?xml version='1.0' encoding='UTF-8'?>
|!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hib...tion-3.0.dtd">
|hibernate-configuration>
  |!-- 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>
  |/session-factory>
|/hibernate-configuration>

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
datasourc1
datasourc2
datasourc3
datasourc4
datasourc5
shark-ds
quartz-ds
sampledata-ds
pentahoHibernat-ds

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.

Nic