PDA

View Full Version : Plan to release this RC against MySQL?



lonifasiko
09-05-2006, 11:12 PM
Hi,

I've somewhere read in the forums that you plan to release this same RC version with MySQL as the backend. Before starting to migrate HyperSonic to MySQL and wasting a lot of time, I hope you could aproximately tell me when you expect to release it.

Thanks very much in advance.

lonifasiko
09-06-2006, 02:28 AM
Hi again,

I finally adventured myself in the task of trying to setup Pentaho against MySQL. After following all the steps (some of them really confusing because some files did not even exist) commented in this post (http://forums.pentaho.org/showthread.php?t=25720),
realized that the post was written in 2005! Ouch!

Hence, it's normal that Pentaho demo is not working for me anymore!
I've downloaded latest Pentaho-demo (RC1 - 1.2 Build 341). Has anybody achieved getting this versiĆ³n of Pentaho working under MySQL? What's the correct way I should follow?

I would really appreciate any feedback or concrete example on that, or maybe as I require in the previous post, an aproximate date for a new release running under MySQL.

Thanks very much in advance.

adeshazor
09-06-2006, 04:24 AM
As you can imagine and probably have experienced, there are a lot of moving pieces to switch to MySQL as the backing database. We are in the final stages of testing a build of the Pentaho server that automates the switch. We have a few cases to resolve but expect to release it with in 2-3 weeks. I've listed some of the issues below so that you can track our progress.

PLATFORM-488
PLATFORM-491
PLATFORM-494
PLATFORM-495
PLATFORM-502

Do you need the MySQL-based build to complete a project or proof of concept?

Anthony

guzaldon
09-06-2006, 03:42 PM
I know of a couple posts that will help you get it over there..

I have been running successfully on "purely" mysql for few months now... and compared to some things is relitively easy.

the tech tips have a few nice tips to help you get verything moved over.
and these two topics are great help. to suppliment the one you were looking at.. which by the way didn't work very well for me.. so I contributed to the other topics on the subject.

some helpfull scripts to create the databases in mysql
http://forums.pentaho.org/showthread.php?t=26607
which would be step one.

step two is to check out the tech tips for a kettle transaction that will help move it over

step three is edit the hibernate.cfg.xml file

step four update sampledata set if need be

more info here
http://www.pentaho.org/index.php?option=com_simpleboard&Itemid=6&func=view&catid=32&id=2352#2352

step 5 we don't know..
step 6 PROFIT!!!!!

Hope that helps get you going if you can't wait the 3 weeks.

Nic

P.S. Make sure your underwear isn't missing... grrr you gnomes...

lonifasiko
09-07-2006, 03:27 AM
Hi again,

I finally achieved having Pentaho platform running against MySQL. I've done a "little combination" of some posts (thanks very much for your contribution guzaldon) and with some more personal imagination, my Pentaho demo is up and running with MySQL.

If I've got some time, during next days, I promise you to post in the forum a brief guide for those who want to see Pentaho demo with MySQL.

However, I still have got one pending issue: I haven't done nothing with quartz properties file. Moreover, I haven't modified nothing related to quartz database. What's the purpose of this database or which class/example uses it?

Thanks very much again.

guzaldon
09-07-2006, 09:45 AM
Congrats.

It's probably a good idea to post your proccess some things do get modified so your updated scripts might help others as well. Specially if you had to pull some resources from other places. But congrats on getting it working. :-)

oh you are wondering about quartz.. well I think I'll point you to some info and give you a summary if that's alright.

check this out.. it's home page:
http://www.opensymphony.com/quartz/

but quartz is what you think it would be. quartz is used in watches to keep time. so it's like cron wrote in java and backed with a persitant database. Some examples that use it are the scheduler component and bursting or most things that have to deal with time.

I do not believe that shark as any idea of what time is. and sharks used for work flow so to allow it to understand time it works with quartz a bit. In the over all scheme... but that's my option... anyone else got some thoughts?

this one the force strong in you, yes indeed,

Nic

lonifasiko
09-08-2006, 12:01 AM
Hi,

I've summed up all my notes (pieces of different posts, own notes, etc.) and here they are! The goal of these notes is to setup pentaho demo (pre-configured installation V1.2 RC1) running with MySQL:

Step 1. HSQL to MySQL transfer:
Preconfigured pentaho-demo installation goes with the repositories (shark workflow, quartz scheduler) and sample databases stored as HSQL databases.

The first thing to do is to create appropriate database schemas in MySQL:
- create user named 'pentaho' in MySQL.
- create the following schemas in MySQL:
'hibernate', 'quartz', 'sampledata', 'shark'
- grant all necessary priveleges on the newly created schemas to 'pentaho' user

The convenient way of migrating databases from HSQL is to use the Transfer utility, but to make it working we need to build the utility from the sources:
- download hsqldb_1_8_0_2 (http://sourceforge.net/project/showfiles.php?group_id=23316), and unzip it to the directory of your choice (will be referenced as HSQLDIR).
- copy the files attached to this post (MySQLTransferHandler.java, HelperFactory.java) to the HSQLDIR/src/org/hsqldb/util directory
- open the command line (or terminal) and change the current directory to HSQLDIR/build, run the following command 'ant hsqldbutil'
- when the build process is successfully finished we will have hsqldbutil.jar file in the HSQLDIR/lib directory. Copy this file to PENTAHO_DEMO/data directory.
- Copy MySQL JDBC driver archive (mysql-connector-java-3.1.11a-bin.jar) to the PENTAHO_DEMO/data directory. Note that the name of the driver could change for future versions.
- Start the HSQL database server using PENTAHO_DEMO/data/start_hypersonic.* script
- open the command line (or terminal) and change the current directory to PENTAHO_DEMO/data. Run the following command (if you are on UNIX change the classpath separator to ':'):
java -cp hsqldbutil.jar;mysql-connector-java-3.1.11a-bin.jar;./lib/hsqldb.jar org.hsqldb.util.Transfer
- The 'Source Database' dialog appears. From the 'Type' list choose 'HSQL Database Engine Server'.
In the 'URL' input field, add the database name 'jdbc:hsqldb:hsql://localhost/hibernate'. Press 'OK'.
- The 'Target Database' dialog appears. From the 'Type' list choose 'MySQL Connector/J'.
Enter the URL pointing to your MySQL 'hibernate' schema. For me it is: 'jdbc:mysql://localhost:3306/hibernate'. (3306 is the default port number for MySQL. Maybe you've it running in another port).
Enter the 'pentaho' username and password. press 'OK'. Press 'Start Transfer' in the 'HSQL Transfer Tool' window.
Quit the Transfer utility after the transfer is finished.
- Repeat the transfer for the 'quartz', 'shark', and 'sampledata' databases.

TRANSFER OF ALL DATA COMPLETE!

Step 2. Copy libraries and change configuration files

- Copy MySQL JDBC driver to the JBoss libraries. You may place mysql-connector-java-3.1.11a-bin.jar file
in the PENTAHO_DEMO/jboss/server/default/lib directory

- Edit pentaho-demo\\jboss\\server\\default\\deploy\\pentaho.war\\WEB-INF\\classes\\hibernate.cfg.xml to point to MySQL. The main section of this file shoud have the following appearance (Note the password for pentaho user in my case is blank):

<hibernate-configuration>
<!-- a SessionFactory instance listed as /jndi/name -->
<session-factory name="java:Pentaho/SessionFactory">
<!-- properties -->
<property name="connection.datasource">java:/comp/env/jdbc/hibernate</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="connection.username">pentaho</property>
<property name="connection.password"></property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="show_sql">false</property>
</session-factory>
</hibernate-configuration>

- Edit C:\\temp\\pentaho-demo\\pentaho-solutions\\system\\shark\\conf\\Shark.conf file.
Add this four lines under "The jdbc driver to use, and Database url" section (I suppose there is no matter in adding them in another place of the file).

DatabaseManager.DB.sharkdb.JdbcDriver="com.mysql.jdbc.Driver"
DatabaseManager.DB.sharkdb.Connection.Url="jdbc:mysql://localhost:3306/shark"
DatabaseManager.DB.sharkdb.Connection.User="pentaho"
DatabaseManager.DB.sharkdb.Connection.Password=""

- Change these datasources:

From datasourc1 to datasourc5 the files should look like this (datasource name varies of course):

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>datasource1</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/sampledata</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>pentaho</user-name>
<password></password>
</local-tx-datasource>
</datasources>

quartz-ds:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>Quartz</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/quartz</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>pentaho</user-name>
<password></password>
</local-tx-datasource>
</datasources>

sampledata-ds:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>SampleData</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/sampledata</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>pentaho</user-name>
<password></password>
</local-tx-datasource>
</datasources>

shark-ds:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>Shark</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/shark</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>pentaho</user-name>
<password></password>
</local-tx-datasource>
</datasources>

PentahoHibernate-ds:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>Hibernate</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/hibernate</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>pentaho</user-name>
<password></password>
</local-tx-datasource>
</datasources>

Again remind that in my example the password for pentaho user is blank.

Step 3. Change samples.

- inside pentaho-demo\\pentaho-solutions\\samples\\reporting: MDX_report_xaction and MDX_report_XLS.xaction

- inside pentaho-demo\\pentaho-solutions\\samples\\datasource: MDS_Datasource.xaction

- inside pentaho-demo\\pentaho-solutions\\samples\\etl: sample.ktr

Step 4. Change startup script. pentaho-demo\\start-pentaho.bat script. Remove the line which starts hypersonic database. HSQL database is not necessary, because we have all of the repositories stored in MySQL.

I think that's all. This way you should enter your Pentaho demo solution and almost all samples should work properly against MySQL.

Hope all this copy&paste from other posts helps.

Best regards.

PS: I attach a .txt file with the four needed steps just in case.

guzaldon
09-08-2006, 10:18 AM
lonifasiko,

Thanks man.. I'm sure that might help out others till they get the install going with mysql. Looks pretty good.

One note is that the txt file didn't get attachted..

Thanks man have a good one,

Nic

lonifasiko
09-10-2006, 10:01 PM
Hi,

Yes, I could not be able to attach the .txt file I mention in the post because this editor did not let me doing it (I think was because of some invalid characters in the file....).

Well, I think the post should be clear enough.

If somebody needs or want the text file, I would try again.

Regards.

radek
09-12-2006, 06:06 AM
Gents...

I attached mysql schema and data in case anyone need it.

ciao,
Radek

radek
09-12-2006, 06:07 AM
Let's try again, this time not as image... :) http://forums.pentaho.org/archived_att/files/mysql.tar.gz

dhartford
09-27-2006, 08:00 AM
Anthony de Shazor wrote:


PLATFORM-488
PLATFORM-491
PLATFORM-494
PLATFORM-495
PLATFORM-502

Do you need the MySQL-based build to complete a project or proof of concept?

Anthony


I would like to see a MySQL-based build as we will not deploy into production the Pentaho suite without an 'official', or included/supported, MySQL install (i.e. hacks/thirdparty patches are discouraged due to upgrade problems). Production on hypersonic is not allowed for some reason ;-).

-D