Pentaho 3.7 with MySQL, PostgreSQL, Oracle and SQL Server.

Posted under BI Server, News, Pentaho, Tips, Tutorials

This guide has been updated and works with the latest version of Pentaho BI Server 3.7.

Posted by Prashant Raju on Dec 14, 2010 — 35 CommentsShare

35 Comments for “Pentaho 3.7 with MySQL, PostgreSQL, Oracle and SQL Server.”

  1. Posted by Sean on Dec 23, 2010

    Great, thanks!

  2. Posted by Abhinandan on Jan 7, 2011

    Hi Prashant,

    First of all thank you for this wonderful blog.
    I would like to add few more points while using oracle with Pentaho.
    1. \biserver-ce\tomcat\conf\Catalina\localhost\pentaho.xml entries need to be changed.
    2. There is a need to change the new changes that is added to 3.7 version in \biserver-ce\tomcat\webapps\pentaho\WEB-INF\web.xml
    Remove this tag




    Hopefully it will help the others as well.

  3. Posted by egonzalez on Jan 14, 2011

    HI Prashant Raju,

    First thing, thanks a lto for the guide ! I’ve folled successfully the guide for the bi-server 3.6.0 But

    I’ve followed the guide for Oracle and bi Server 3.7.0 and I’m stuck cause, just after the pentaho webapp start well ( bi server is ready), I have a SEVERE : Error listenerStart.

    Is there any way you can help. I’ve registered to the pentaho community foum but I can’t post noew thread b now.

    Thanks in advance.

    By the way, on the 3.6.0 oracle sql script pacl, on the 1st file, ; is missing at the end of each table creation table line ! :)

  4. Posted by Prashant Raju on Jan 22, 2011

    Thanks Abhinandan!

    The guide has been updated to include this.


  5. Posted by Jeroen on Jan 25, 2011


    Anybody succes with this guide on Windows7 32bits with SQL server 2008 R2? I have followed the guide several times but it fails every time. Also the install tomcat5 is not working because there is no service.bat in the tomcat/bin directory.

    I saw the changes to the file this morning and tried it once more but still fails. I have disaabled the HSQL database in the WEB.XML and start the tomcat service by the regular start-pentaho.bat because of the installation as a service is not working.

    I would like to hear that somebody got it working with the aboven mentioned configuration … to give me hope :-)


  6. Posted by Prashant Raju on Jan 25, 2011

    Hi Jeroen

    Simply saying that “it fails every time” isn’t helpful with identifying the problem.

    What is catalina.out telling you?

    Please use to paste your log output.


  7. Posted by Jeroen on Jan 26, 2011

    Hello Prashant,

    Thanks for your response. I did a clean install again. All steps executed. Because I have no service.bat on my computer I started the included TOMCAT using the startup.bat in the pentaho/bi-server/tomcat/bin directory.

    Then the BI server is not running. Only the cataline.log is filled. See also for the log files.

    On row 13 I have a fatal error. Looks like pentaho.xml is the problem. I opened the pentaho.xml in ../pentaho-solutions/system but I can not find anything wrong with it. To be sure here is the file

    Is it normal that http://localhost:8080 is not working with the included TOMCAT?

    I will try more but that is it for now.


  8. Posted by Jeroen on Jan 26, 2011


    I have got it working now. I had made an error (of course) in the web.xml. Where to comment out the row that start the hsqldb I had a comment opening tag <!– but no closing tag.

    I looked like the error was in pentaho.xml but it was in fact in web.xml.

    Beside that I found another pentaho.xml file. It is in D:\pentaho\biserver-ce\tomcat\conf\Catalina\localhost directory and it contained still old JDBC connection information (using HSQL instead of MSSQL). I had it changed before I found the error so I do not know if it is anything important.

    Kind regards and thanks for your guide.


  9. Posted by Jeroen on Feb 1, 2011

    After installing it on localhost I now have it installed on our server. I had to edit the file in ..\tomcat 6.0\conf\catalina\localhost\pentaho.xml to make it work.

    Mayby it is because I partially copied it from my local instance but I could not get it working on localhost as on the server without editing that file. Mayby something to add to your guide …:-)

    Kind regards,

  10. Posted by sunny on Feb 18, 2011

    thanks a lot for the tutorial.. worked without a hitch… but i’m facing one problem… when i click on analysis view only the sample schemas are available .. how to change it to a custom database… plz help me..

  11. Posted by alex on Feb 20, 2011

    Hello Prashant,
    first of all thanks for the useful guide! I’ve migrated to mysql but I’ve notified a “strange” behaviour of the server: it opens 2 mysql connections for every datasource defined even if no one is using it, is this normal?



  12. Posted by Will on Mar 11, 2011

    Thanks for the doc. Very very nice. It saves big big headache.

    I have no java background at all and only know Linux little bit. The document is very clear and easy to follow. Definitely recommend for new users.

    Thanks again. Good job.


  13. Posted by Will on Mar 13, 2011

    I agree with Jeroen./opt/pentaho/biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml does need to be updated in version 3.7. Once I update it, Pentaho BI Platform works to me. I can have it work for version 3.5 without edit this xml.


  14. Posted by Allyson on Mar 16, 2011

    Thanks pras!

    i have to confess, it was (to me) a little more difficult than the older versions, rs.

    Great job. Keep the good work.

  15. Posted by Luis Benavides on Mar 29, 2011

    Hello Prashant,

    After update all of file that you mention in your guide, my startup biserver doesn’t work. I had to change the pentaho.xml located in
    An seting with all of parameter of MySQL.

    In this way my biserver-ce 3.8 work well.

    Thanks for you guide,
    Luis Benavides.

  16. Posted by Ranu on Apr 13, 2011


    The document is of great help. But will this work with Mysql 4.1 ? I tried it but its giving a lot of errors.

    When i log into pentaho it says

    The following errors were detected
    One or more system listeners failed. These are set in the systemListeners.xml.
    PentahoSystem.ERROR_0014 – Error while trying to execute startup sequence for org.pentaho.platform.plugin.action.mondrian.MondrianSystemListener

    What is the problem?


  17. Posted by Ranu on Apr 15, 2011


    I installed MySql 5.1 on my computer and made all changes to various files of biserver-ce.

    Pentaho Admin console opens up fine.
    When I add a new datasource, it says
    “connection not valid”. And also when I write a valid sql query it says “query validation failed”.

    Can you tell me where am I going wrong?


  18. Posted by Tobias on Apr 17, 2011

    Like Jereon and Will, I also had to update the file “biserver-ce/tomcat/conf/Catalina/localhost/pentaho.xml”. Maybe it is because I am running Tomcat as a service?

  19. Posted by Andy on Apr 20, 2011

    Hi Prashant. We’ve implemented Pentaho 3.7 CE on a Windows box using Oracle 10G as the repository. We followed the guide, the server starts and everything works just fine for a couple of hours, but at some point the connection between Pentaho and the hibernate database goes nuts. Repository doesn’t refresh, we can’t save a new analysis view, and we can’t open some views. Others work just fine.

    In the log we’re getting lines like these ones:
    10:42:50,160 ERROR [JDBCTransaction] JDBC commit failed
    java.sql.SQLException: Io exception: Connection reset
    10:42:50,176 ERROR [HibernateUtil] HIBUTIL.ERROR_0008 – commitTransaction() failed.
    org.hibernate.TransactionException: JDBC commit failed

    Any thoughts?

    Best regards

  20. Posted by Ashish on Apr 24, 2011

    Hi Prashant,

    Thanks for this guide! But I am unable to download the SQL scripts pack from the provided link in the guide:

    Please help.


  21. Posted by Ashish on Apr 25, 2011

    Hi Prashant,

    Thanks for this guide! Really it is very helpful.But I am unable to download the SQL pack and receiving error. Can you please re-upload them for MySQL 5.


  22. Posted by Ashish on May 8, 2011

    Hi Prashant,

    Please upload the SQL packs once again.


  23. Posted by andtorg on May 11, 2011

    Hi Prashant,

    first of all many thanks for the documents you share. They are invaluable.

    May I suggest to insert into the guide the tips from this forum post, comment #17 by twarfield, (

    It’s about downloading the tomcat6 missing files in 3.7 GA for installing pentaho as a windows service.



  24. Posted by Chema on May 18, 2011

    I have been through the hole process several times, taken into account all the comments and it is still not working. I have the same error as Ranu. I am working with Pentaho 3.7 and Mysql 5.5 trhough mysql Workbench
    I have the following messages:
    10:27:42,126 ERROR [Logger] misc-org.pentaho.platform.engine.core.system.Pentaho
    10:27:42,126 ERROR [Logger] Error: Pentaho
    System: PentahoSystem.ERROR_0026 – Failed to retrieve object with name “ISolutio
    nRepository” from the Pentaho object factory.
    org.pentaho.platform.api.engine.ObjectFactoryException: Failed to create an inst
    ance of object with key [ISolutionRepository].
    System: PentahoSystem.ERROR_0014 – Error mientras se intentaba ejecutar la secue
    ncia de arranque por org.pentaho.platform.plugin.action.mondrian.MondrianSystemL

    Thanks for your help!

  25. Posted by giancarlo on May 28, 2011

    Hi Prashant,
    please check the SQL Server guide, it is not downloadable or printable.


  26. Posted by Complete Pentaho Installation on Ubuntu, Part 2 | Interesting IT Tip's on Jun 18, 2011

    [...] The tip to solve it came from Luis Benavides from in a comment of this [...]

  27. Posted by DaveA on Jun 23, 2011

    Hi Prashant,

    I would also like to offer my thanks on these guides. They have saved hours and hours of struggle.

    One question you or your readers might be able to help me with: I have configured my Pentaho BI server per the document to use a Postgres database but my Quartz engine still appears to be using the default Hypersonic Db.

    I have modified META-INF/context.xml and I have tested the database username and password etc.

    Where would I look from here?

  28. Posted by Nicolas on Aug 9, 2011

    Hi Prashant, sorry but I don’t speak english very good . I am a chilean that be start in pentaho ago 2 month and I learned very much , now I need configure pentaho with jboss and I dont have information that help me, I searched in many places information but not is very explicit for a beginner how me. I need help and I will grateful very much if you help me
    with this problem. my email is for any person that want to help me…..thanks

  29. Posted by Rob on Aug 19, 2011

    A couple of Mac install questions. 10.5.8 Fully updated and javadeveloper_for_mac_os_x_10.5__9m3425.dmg installed.

    First I used the Java Preferences app in the Utilities folder to choose the version of java. It is a bit of an odd app but I drug “Java SE 6″ to the top of the list and “unselected” J2SE 5.0 32 and 64 bit.

    RobsMacAir-2:~ rob$ java -version
    java version “1.6.0_26″
    Java(TM) SE Runtime Environment (build 1.6.0_26-b03-384-9M3425)
    Java HotSpot(TM) 64-Bit Server VM (build 20.1-b02-384, mixed mode)

    So far so good. But…

    RobsMacAir-2:~ rob$ echo $JAVA_HOME

    Hmm what Java is that?

    RobsMacAir-2:~ rob$ ls -li $JAVA_HOME
    39730668 lrwxr-xr-x 1 root admin 48 Aug 18 00:16 /Library/Java/Home -> /System/Library/Frameworks/JavaVM.framework/Home

    Not much help. Let’s track this down.

    RobsMacAir-2:Home rob$ ls -li /System/Library/Frameworks/JavaVM.framework
    39730711 lrwxr-xr-x 1 root wheel 24 Aug 18 00:17 Home -> Versions/CurrentJDK/Home

    RobsMacAir-2:Home rob$ cd /System/Library/Frameworks/JavaVM.framework
    RobsMacAir-2:JavaVM.framework rob$ pwd

    RobsMacAir-2:Home rob$ ls -li /System/Library/Frameworks/JavaVM.framework
    total 72
    39730709 lrwxr-xr-x 1 root wheel 27 Aug 18 00:17 Classes -> Versions/CurrentJDK/Classes
    39736741 lrwxr-xr-x 1 root wheel 24 Aug 18 00:18 CodeResources -> Versions/A/CodeResources
    39730710 lrwxr-xr-x 1 root wheel 28 Aug 18 00:17 Commands -> Versions/CurrentJDK/Commands
    39736742 lrwxr-xr-x 1 root wheel 27 Aug 18 00:18 Frameworks -> Versions/Current/Frameworks
    39736743 lrwxr-xr-x 1 root wheel 24 Aug 18 00:18 Headers -> Versions/Current/Headers
    39730711 lrwxr-xr-x 1 root wheel 24 Aug 18 00:17 Home -> Versions/CurrentJDK/Home
    39736744 lrwxr-xr-x 1 root wheel 23 Aug 18 00:18 JavaVM -> Versions/Current/JavaVM
    39730712 lrwxr-xr-x 1 root wheel 29 Aug 18 00:17 Libraries -> Versions/CurrentJDK/Libraries
    39736745 lrwxr-xr-x 1 root wheel 26 Aug 18 00:18 Resources -> Versions/Current/Resources
    166952 drwxr-xr-x 14 root wheel 476 Aug 18 00:18 Versions

    RobsMacAir-2:JavaVM.framework rob$ cd Versions
    RobsMacAir-2:Versions rob$ ls -li
    total 64
    39730713 lrwxr-xr-x 1 root wheel 5 Aug 18 00:17 1.3 -> 1.3.1
    167191 drwxr-xr-x 3 root wheel 102 Jan 14 2008 1.3.1
    18877862 lrwxr-xr-x 1 root wheel 5 Dec 16 2009 1.4 -> 1.4.2
    388609 lrwxr-xr-x 1 root wheel 3 Jun 24 2008 1.4.1 -> 1.4
    166953 drwxr-xr-x 8 root wheel 272 Jun 23 2008 1.4.2
    39731271 lrwxr-xr-x 1 root wheel 5 Aug 18 00:17 1.5 -> 1.5.0
    166970 drwxr-xr-x 8 root wheel 272 Jun 23 2008 1.5.0
    39730715 lrwxr-xr-x 1 root wheel 5 Aug 18 00:17 1.6 -> 1.6.0
    39730716 lrwxr-xr-x 1 root wheel 59 Aug 18 00:17 1.6.0 -> /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents
    167195 drwxr-xr-x 10 root wheel 340 Aug 18 00:18 A
    39736808 lrwxr-xr-x 1 root wheel 1 Aug 18 00:18 Current -> A
    39730775 lrwxr-xr-x 1 root wheel 3 Aug 18 00:17 CurrentJDK -> 1.5

    So it looks like CurrentJDK is pointing to 1.5.
    Current is pointing to A not sure what that is.

    Any idea if this is a problem or how to fix it. Seems very complex.

    The other question is:
    “also add the following line to the bottom of the /etc/
    environment file:
    CATALINA_OPTS=”-Xms256m -Xmx768m -XX:MaxPermSize=256m -
    Dsun.rmi.dgc.client.gcInterval=3600000 -

    There is no /etc/environment on Mac? Where should this go.

  30. Posted by nadim on Aug 24, 2011

    Shukriya Prashant Raju,

    I followed the steps and got it working.

    Your guide for setting up Pentaho to work with MySQl does not include changing pentaho.xml in \biserver-ce\tomcat\conf\Catalina\localhost\

    I got it here in the comments.

    Anyway thanks again for this blog and this guide.

    Nadim Attari

  31. Posted by Michael on Sep 14, 2011


    I have been using you guide to try and set this up on a Linux box running PostgreSQL 9.0.

    I installed bi-server-ce-3.8, Pentaho_3.6_Postgresql scripts.

    When the webapps starts it quickly fills the logs with loads of errors like this. Any idea what is wrong?

    10:11:49,312 ERROR [JobStoreTX] Error retrieving job, setting trigger state to ERROR.
    org.quartz.JobPersistenceException: Couldn’t retrieve job: Bad value for type long : \xaced0005737200156f72672e71756172747a2e4a6f62446174614d61709fb083e8bfa9b0cb0200015a0013616c6c6f77735472616e7369656e74446174617872001d6f72672e71756172747a2e7574696c732e4469727479466c61674d617013e62ead28760ace0200025a000564697274794c00036d617074000f4c6a6176612f7574696c2f4d61703b787001737200116a6176612e7574696c2e486173684d61700507dac1c31660d103000246000a6c6f6164466163746f724900097468726573686f6c6478703f4000000000000c7708000000100000000174001376657273696f6e52657175657374466c616773737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000007800 [See nested exception: org.postgresql.util.PSQLException: Bad value for type long : \xaced0005737200156f72672e71756172747a2e4a6f62446174614d61709fb083e8bfa9b0cb0200015a0013616c6c6f77735472616e7369656e74446174617872001d6f72672e71756172747a2e7574696c732e4469727479466c61674d617013e62ead28760ace0200025a000564697274794c00036d617074000f4c6a6176612f7574696c2f4d61703b787001737200116a6176612e7574696c2e486173684d61700507dac1c31660d103000246000a6c6f6164466163746f724900097468726573686f6c6478703f4000000000000c7708000000100000000174001376657273696f6e52657175657374466c616773737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000007800]
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.retrieveJob(
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggerFired(
    at org.quartz.impl.jdbcjobstore.JobStoreTX.triggerFired(
    * Nested Exception (Underlying Cause) —————
    org.postgresql.util.PSQLException: Bad value for type long : \xaced0005737200156f72672e71756172747a2e4a6f62446174614d61709fb083e8bfa9b0cb0200015a0013616c6c6f77735472616e7369656e74446174617872001d6f72672e71756172747a2e7574696c732e4469727479466c61674d617013e62ead28760ace0200025a000564697274794c00036d617074000f4c6a6176612f7574696c2f4d61703b787001737200116a6176612e7574696c2e486173684d61700507dac1c31660d103000246000a6c6f6164466163746f724900097468726573686f6c6478703f4000000000000c7708000000100000000174001376657273696f6e52657175657374466c616773737200116a6176612e6c616e672e496e746567657212e2a0a4f781873802000149000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b0200007870000000007800
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toLong(
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getLong(
    at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(
    at org.apache.commons.dbcp.DelegatingResultSet.getBlob(

  32. Posted by Nur on Oct 12, 2011

    hi… i’m using bi server 3.8 and microsoft server 2008.. but i got this error in catalina log.. can u help me to solve it because i’m very new in pentaho..

    Oct 12, 2011 6:00:40 PM org.apache.catalina.core.AprLifecycleListener init
    INFO: Loaded APR based Apache Tomcat Native library 1.1.20.
    Oct 12, 2011 6:00:40 PM org.apache.catalina.core.AprLifecycleListener init
    INFO: APR capabilities: IPv6 [true], sendfile [true], accept filters [false], random [true].
    Oct 12, 2011 6:00:40 PM org.apache.coyote.http11.Http11AprProtocol init
    INFO: Initializing Coyote HTTP/1.1 on http-8010
    Oct 12, 2011 6:00:40 PM org.apache.coyote.ajp.AjpAprProtocol init
    INFO: Initializing Coyote AJP/1.3 on ajp-8009
    Oct 12, 2011 6:00:40 PM org.apache.catalina.startup.Catalina load
    INFO: Initialization processed in 346 ms
    Oct 12, 2011 6:00:40 PM org.apache.catalina.core.StandardService start
    INFO: Starting service Catalina
    Oct 12, 2011 6:00:40 PM org.apache.catalina.core.StandardEngine start
    INFO: Starting Servlet Engine: Apache Tomcat/6.0.29
    Oct 12, 2011 6:00:40 PM org.apache.catalina.startup.HostConfig deployDescriptor
    INFO: Deploying configuration descriptor pentaho.xml
    Oct 12, 2011 6:00:40 PM org.apache.catalina.startup.ContextConfig validateSecurityRoles
    INFO: WARNING: Security role name PENTAHO_ADMIN used in an without being defined in a
    Oct 12, 2011 6:00:42 PM org.apache.catalina.core.StandardContext start
    SEVERE: Error listenerStart
    Oct 12, 2011 6:00:42 PM org.apache.catalina.core.StandardContext start
    SEVERE: Context [/pentaho] startup failed due to previous errors
    Oct 12, 2011 6:00:42 PM org.apache.catalina.loader.WebappClassLoader clearReferencesJdbc
    SEVERE: The web application [/pentaho] registered the JBDC driver [mondrian.olap4j.MondrianOlap4jDriver] but failed to unregister it when the web application was stopped. To prevent a memory leak, the JDBC Driver has been forcibly unregistered.
    Oct 12, 2011 6:00:42 PM org.apache.catalina.startup.HostConfig deployDirectory
    INFO: Deploying web application directory pentaho-style
    Oct 12, 2011 6:00:42 PM org.apache.catalina.startup.HostConfig deployDirectory
    INFO: Deploying web application directory ROOT
    Oct 12, 2011 6:00:42 PM org.apache.catalina.startup.HostConfig deployDirectory
    INFO: Deploying web application directory sw-style
    Oct 12, 2011 6:00:43 PM org.apache.coyote.http11.Http11AprProtocol start
    INFO: Starting Coyote HTTP/1.1 on http-8010
    Oct 12, 2011 6:00:43 PM org.apache.coyote.ajp.AjpAprProtocol start
    INFO: Starting Coyote AJP/1.3 on ajp-8009
    Oct 12, 2011 6:00:43 PM org.apache.catalina.startup.Catalina start
    INFO: Server startup in 2502 ms


  33. Posted by nadim on Jan 3, 2012


    I have some simple questions : How important it is to migrate from HSQLDB to MySQL (for Quartz and Hibernate) ? Do we get better performance when using MySQL (or any DB other than HSQLDB) ? Is it good for production if we leave the BI server with HSQLDB ?


  34. Posted by Nirmal on Feb 24, 2012

    Hai Prasanth,
    I really need your help,want to know two things
    1.How to create data cubes on pentaho server from a Mysql Database.
    2.How the MySql and pentaho server sinkup is happening?
    hope you would reply me with sufficient data as soon as possible and thanks for this wonderful blog about pentaho.

  35. Posted by PRajan on Aug 30, 2012

    Hi Prashant,

    Thanks for the wonderful guide. I have installed bi server 3.9 on Windows XP SP2 and MySQL 5.5. I got the error message “PentahoSystem.ERROR_0014 – Error while trying to execute startup sequence for org.pentaho.platform.scheduler.QuartzSystemListener” and “Failed to obtain DB connection from data source ‘quartz’” when I tried bringing up the service. I uncommented the below section with the changes below in and I was able to fix these –

    org.quartz.dataSource.quartz.driver = com.mysql.jdbc.Driver
    org.quartz.dataSource.quartz.URL = jdbc:mysql://localhost/
    org.quartz.dataSource.quartz.user = pentaho_user
    org.quartz.dataSource.quartz.password = password
    org.quartz.dataSource.quartz.maxConnections = 5
    org.quartz.dataSource.quartz.validationQuery= select 1

    I commented out the JNDI Url as well -
    # org.quartz.dataSource.myDS.jndiURL = Quartz

    Quartzlistener came up as a charm.

    p. rajan.

Post a Comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>