Hitachi Vantara Pentaho Community Forums
Results 1 to 22 of 22

Thread: Pentaho BI purely on MySQL

  1. #1
    Join Date
    Mar 2006
    Posts
    10

    Default Pentaho BI purely on MySQL

    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

  2. #2
    Join Date
    Nov 1999
    Posts
    579

    Default Re:Pentaho BI purely on MySQL

    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

  3. #3
    Join Date
    Mar 2006
    Posts
    10

    Default Re:Pentaho BI purely on MySQL

    That's great. I won't chase it anymore and I'm waiting for next release of Pentaho BI.

    Seb

  4. #4
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  5. #5
    Join Date
    Nov 1999
    Posts
    579

    Default Re:Pentaho BI purely on MySQL

    We're evaluating an installer for Pentaho that handles a pure MySQL solution. We'll keep everyone posted.

    But, it's coming.

  6. #6
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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.
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  7. #7
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  8. #8
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  9. #9

    Default Re:Pentaho BI purely on MySQL

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

  10. #10
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  11. #11
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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.

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  12. #12
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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:
    Code:
    #rdbms choice - valid values are hsqldb, mysql4, mysql5, postgresql
    rdbms=mysql5
    hibernate.config=${staging.resources}/hibernate/${rdbms}
    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.

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  13. #13
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    the rdbms info has been relocated to the default.properties in 1.1.6.

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  14. #14
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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.

    Thanks,

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  15. #15
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    I think I see my mistake I had the connection.datasource set to the wrong datasource.

    in the hibernate.cfg.xml file.

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  16. #16
    Join Date
    Nov 1999
    Posts
    579

    Default Re:Pentaho BI purely on MySQL

    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,

    Marc

  17. #17
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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:
    Code:
     <property name="connection.datasource">java:/comp/env/jdbc/MySqlDS</property>
    I changed it to:
    Code:
     <property name="connection.datasource">java:/comp/env/jdbc/Hibernate</property>
    and I no longer have a problem.

    Thanks for the consern,

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  18. #18
    Join Date
    Apr 2006
    Posts
    18

    Default Re:Pentaho BI purely on MySQL

    Hi,

    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:
    Code:
            <location>mondrian; JdbcDrivers=com.mysql.jdbc.Driver</location>
            <user-id>pentaho</user-id>
            <password>password1</password>
            <connection>"jdbc:mysql://localhost:8080/sampledata"</connection>
    But now I get this error:
    Code:
    java.io.EOFException
    
    STACKTRACE:
    
    java.io.EOFException
    	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)
    
    
    ** END NESTED EXCEPTION **
    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.

    Thanks,
    David http://forums.pentaho.org/archived_a...source.xaction

  19. #19
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    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,

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  20. #20
    Join Date
    Apr 2006
    Posts
    18

    Default Re:Pentaho BI purely on MySQL

    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.

    Thanks,
    David

  21. #21
    Join Date
    Mar 2006
    Posts
    683

    Default Re:Pentaho BI purely on MySQL

    David,

    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.

    Nic
    Try not to have too much fun.
    - - - -
    Nic Guzaldo
    That guy who shows up here
    and there

  22. #22
    Join Date
    Feb 2006
    Posts
    3

    Default Re:Pentaho BI purely on MySQL

    I do not thing that 8080 is the right port number for mysql.
    Default value is 3306. 8080 is jboss default port number...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.