Pentaho BI Server 4.5.0 & Pentaho BI Server 4.8.0- MySQL installation guide


How to install Pentaho BI Server 4.5.0 & 4.8.0 Community Edition with MySQL 5.x and Windows

Hi all this is David Fombella and I would like to share a correct way to deploy Pentaho Community last edition installing its security into a MySQL database server.

Help me keep the guides up to date and the posts flowing by donating, every small amount of money helps!

btn_donatecc_lg

Here is the download links to get the biserver 450 and 480 editions.

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.5.0-stable/biserver-ce-4.5.0-stable.zip/download

http://sourceforge.net/projects/pentaho/files/Business%20Intelligence%20Server/4.8.0-stable/biserver-ce-4.8.0-stable.zip/download

Remember to paste mysql_connector.jar into  /tomcat/lib  for biserver 480 edition since it is not included

Creation of Databases & Users

In our first stage we are going to create hibernate database and the user hibuser. To achieve this execute the SQL script  create_repository_mysql.sql included at biserver-ce\data\mysql5

SQL code

CREATE DATABASE IF NOT EXISTS `hibernate` DEFAULT CHARACTER SET latin1;
USE hibernate;
GRANT ALL ON hibernate.* TO 'hibuser'@'localhost' identified by 'password';
DROP TABLE IF EXISTS DATASOURCE;
--  Create Users Table
CREATE TABLE DATASOURCE(NAME VARCHAR(50) NOT NULL PRIMARY KEY,MAXACTCONN INTEGER NOT NULL,DRIVERCLASS VARCHAR(50) NOT NULL,IDLECONN INTEGER NOT NULL,USERNAME VARCHAR(50) NULL,PASSWORD VARCHAR(150) NULL,URL VARCHAR(512) NOT NULL,QUERY VARCHAR(100) NULL,WAIT INTEGER NOT NULL);
commit;
 

Next we create quartz database and the user pentaho_user. You only need to execute the SQL script  create_quartz_mysql.sql included at biserver-ce\data\mysql5


CREATE DATABASE IF NOT EXISTS `quartz` DEFAULT CHARACTER SET latin1;

grant all on quartz.* to 'pentaho_user'@'localhost' identified by 'password';

USE `quartz`;

DROP TABLE IF EXISTS QRTZ_JOB_LISTENERS;
DROP TABLE IF EXISTS QRTZ_TRIGGER_LISTENERS;
DROP TABLE IF EXISTS QRTZ_FIRED_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_PAUSED_TRIGGER_GRPS;
DROP TABLE IF EXISTS QRTZ_SCHEDULER_STATE;
DROP TABLE IF EXISTS QRTZ_LOCKS;
DROP TABLE IF EXISTS QRTZ_SIMPLE_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_CRON_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_BLOB_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_TRIGGERS;
DROP TABLE IF EXISTS QRTZ_JOB_DETAILS;
DROP TABLE IF EXISTS QRTZ_CALENDARS;
-- continues
 

Appart from that to fix an issue that occurs on BI Server 4.8.0 execute the following code below the error log


ERROR [org.quartz.core.ErrorLogger] An error occured while scanning for the next trigger to fire.org.quartz.JobPersistenceException: Couldn't acquire next trigger: Field 'PRIORITY' doesn't have a default value
 [See nested exception: java.sql.SQLException: Field 'PRIORITY' doesn't have a default value]
 at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:1778)
 at org.quartz.impl.jdbcjobstore.JobStoreTX.acquireNextTrigger(JobStoreTX.java:1218)
 at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:233)
 * Nested Exception (Underlying Cause) ---------------
 java.sql.SQLException: Field 'PRIORITY' doesn't have a default value

 

SQL code to fix PRIORITY column errors


ALTER TABLE  QRTZ_TRIGGERS DROP COLUMN PRIORITY;
ALTER TABLE  QRTZ_FIRED_TRIGGERS DROP COLUMN PRIORITY;

 

Finally you have to get a MySQL  dump of Sampledata  database.

Configuring JDBC Security

This section describes how to configure the Pentaho BI Platform JDBC security to use a MySQL server, this means the Pentaho BI Platform will now point to the hibernate database on the MySQL server instead of the packaged HSQL in memory database.

1. applicationContext-spring-security-jdbc.xml

Edit the file pentaho-solutions\system\applicationContext-spring-security-jdbc.xml.

Original code

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver" />
<property name="url" value="jdbc:hsqldb:hsql://localhost:9001/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
 

Make the changes necessary to get the snippet of code below

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/hibernate" />
<property name="username" value="hibuser" />
<property name="password" value="password" />
</bean>
 

     2. applicationContext-spring-security-hibernate.properties.

Edit the file pentaho-solutions\system\applicationContext-spring-security-hibernate.properties.

Original code

jdbc.driver=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:hsql://localhost:9001/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.HSQLDialect
 

Make the changes necessary to get the snippet of code below

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hibernate
jdbc.username=hibuser
jdbc.password=password
hibernate.dialect=org.hibernate.dialect.MySQLDialect
 

     3. hibernate-settings.xml

Edit the file pentaho-solutions\system\hibernate\hibernate-settings.xml.

Original code

<config-file>system/hibernate/hsql.hibernate.cfg.xml</config-file>
 

Make the changes necessary to get the snippet of code below

<config-file>system/hibernate/mysql5.hibernate.cfg.xml</config-file>
 

4. mysql5.hibernate.cfg.xml

Edit the file pentaho-solutions\system\hibernate\mysql5.hibernate.cfg.xml .

You do not need to make any changes to this file if you would like to use the default user hibuser. However, if you would like to specify your custom user, change connection.username and password  properties.

Original code

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost:3306/hibernate</property>
<property name="dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
<property name="connection.username">hibuser</property>
<property name="connection.password">password</property>
 

Configuring Hibernate and Quartz

Hibernate and Quartz need to specifically use the hibernate and quartz databases which were created on the MySQL server. To do so modifications need to be executed in context.xml file .

     5. context.xml

Edit the file tomcat\webapps\pentaho\META-INF\context.xml.

Remember deleting tomcat\conf\Catalina\localhost\pentaho.xml , Pentaho creates  on startup pentaho.xml as a copy of context.xml.

Original code

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/hibernate"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/quartz"
validationQuery="select count(*) from INFORMATION_SCHEMA.SYSTEM_SEQUENCES"/>
 

Make the changes necessary to get the snippet of code below

<Resource name="jdbc/Hibernate" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="hibuser" password="password"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
validationQuery="select 1" />

<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
factory="org.apache.commons.dbcp.BasicDataSourceFactory" maxActive="20" maxIdle="5"
maxWait="10000" username="pentaho_user" password="password"
driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
validationQuery="select 1"/>
 

Configuring Apache-Tomcat Web Server and Servlet Container

In order to configure the settings of the Apache-Tomcat server for your Pentaho BI Server 4.5.0 the great majority of the modifications should be made inside the web.xml file which is located under the \tomcat\webapps\pentaho\WEB_INF\ folder.

     6. web.xml

In this first modification we will indicate the web application the location of our solution folder , the place where reports, xactions, dashboards …  will be stored.

Edit the file tomcat\webapps\pentaho\WEB-INF\web.xml.

Original code with empty solution-path

<context-param>
<param-name>solution-path</param-name>
<param-value></param-value>
</context-param>
 

Make the changes necessary to indicate your pentaho-solutions path

<context-param>
<param-name>solution-path</param-name>
<param-value>C:/Pentaho/biserver-ce/pentaho-solutions</param-value>
</context-param>

 

fully-qualified-server-url

Now we are going to set the URL to access Pentaho’s BI User console, the default URL is the following http://localhost:8080/pentaho .If you would like others to access the site (remotely or on a network) you will need to make changes to this parameter.

Original code with default url

<context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://localhost:8080/pentaho/</param-value>
</context-param>
 

Make the changes necessary to indicate your server’s domain

 <context-param>
<param-name>fully-qualified-server-url</param-name>
<param-value>http://www.mydomain.com:8080/pentaho/</param-value>
</context-param>
 

Quit HSQL Hypersonic automatic startup

By default  Hypersonic database starts up automatically – to avoid this  comment or delete locate the following snippets of code from web.xml:

<!-- [BEGIN HSQLDB DATABASES] -->
<context-param>
<param-name>hsqldb-databases</param-name>
<param-value>sampledata@../../data/hsqldb/sampledata,hibernate@../../data/hsqldb/hibernate,quartz@../../data/hsqldb/quartz</param-value>
</context-param>
<!-- [END HSQLDB DATABASES] -->
 

Second  section you need to comment or eliminate

<!-- [BEGIN HSQLDB STARTER] -->
<listener>
<listener-class>org.pentaho.platform.web.http.context.HsqldbStartupListener</listener-class>
</listener>
<!-- [END HSQLDB STARTER] -->
 

Pentaho User Console

Now it is time to run Pentaho BI Server, you only need to execute  start-pentaho.bat script stored in biserver-ce, another script named stop-pentaho.bat is included in the same directory.

If you haven’t changed the URL type on your browser the following: http://localhost:8080/pentaho . The default admin user is joe and joe’s password is password.

Pentaho Administrator Console

Now we are going to start the admin console  a web tool where we could manage users,roles and datasources. We execute start-pac.bat  and  if we open a browser and we typehttp://localhost:8099 we will be prompted for administrator console credentials user: admin and admin’s password:password.

We move to Database Connections and clicking on + we create SampleData datasource, pentaho_user should be previously granted access to this sample database

After creating the datasource restart BI server, stop-pentaho.bat …start-pentaho.bat and enjoy the SampleData database.

Hope you have enjoyed

Advertisements

17 thoughts on “Pentaho BI Server 4.5.0 & Pentaho BI Server 4.8.0- MySQL installation guide

  1. In case you get this error

    Failed to obtain DB connection from data source ‘myDS’: java.sql.SQLException: Could not retrieve datasource via JNDI url ‘java:comp/env/jdbc/Quartz’

    delete the following file
    toncat/conf/Catalina/localhost/pentaho.xml

    and try again. 🙂

    Happy coding!

  2. Thanks – found this after a long search . Solved the problem of Quartz datasource . Updating the Web.xml was not mentioned in most of the links I read before reading this

  3. After step 2, I have an error forever.
    {HTTP Status 404 –

    type Status report

    message

    description The requested resource () is not available.

    Apache Tomcat/6.0.29}

    help me please 😥

  4. Hi,

    While installing pentaho it automatically installs mysql server in port 3307.
    How to move all data from 3307 port to 3306 so that I can have everything running in a single port.

  5. Hey Guys,
    hello,
    I need to know how to bypass the user login page while publishing the report through report-designer.

    We have implemented the SSO as per client, unfortunately unable to implement with pentaho. So I need to remove user login Id as well as password

  6. Hi, i need a sql script pack for pentaho biserver-ee 4.8.0, my current sql pack is out dated
    1_create_repository_mysql.sql
    2_create_quartz_mysql.sql
    3_create_sample_datasource_mysql.sql
    4_load_sample_users_mysql.sql
    5_sampledata_mysql.sql
    SQL Server version: 5.5.19.

    Can anyone send me the new one…thanks

  7. Hi,
    I have problem in pentaho administration console below the error message .please help me…
    2013-09-11 18:09:58,359 INFO [org.apache.commons.httpclient.HttpMethodDirector] I/O exception (java.net.ConnectException) caught when processing request: Connection refused: connect
    2013-09-11 18:09:58,359 INFO [org.apache.commons.httpclient.HttpMethodDirector] Retrying request
    2013-09-11 18:09:59,359 INFO [org.apache.commons.httpclient.HttpMethodDirector] I/O exception (java.net.ConnectException) caught when processing request: Connection refused: connect
    2013-09-11 18:09:59,359 INFO [org.apache.commons.httpclient.HttpMethodDirector] Retrying request
    2013-09-11 18:10:00,468 INFO [org.apache.commons.httpclient.HttpMethodDirector] I/O exception (java.net.ConnectException) caught when processing request: Connection refused: connect
    2013-09-11 18:10:00,468 INFO [org.apache.commons.httpclient.HttpMethodDirector] Retrying request

  8. i received this error while started the pentaho BI 4.8 admin console
    ./start-pec.sh
    /app/enterprise-console
    /app/enterprise-console
    WARNING: Using java from path
    DEBUG: _PENTAHO_JAVA_HOME=
    DEBUG: _PENTAHO_JAVA=java
    DEBUG: PENTAHO_INSTALLED_LICENSE_PATH=
    WARN ConfigurationFactory – No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/app/enterprise-console/lib/ehcache-core-2.0.1.jar!/ehcache-failsafe.xml
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!
    ERROR SystemSettings – ::: WritableSettings.ERROR_0002 – !WritableSettings.ERROR_0002_FILE_NOT_VALID!

  9. “Finally you have to get a MySQL dump of Sampledata database.”

    Where do I get this from? Tried all over the place without much luck.

  10. Hi there,
    I’m having some problems configuring pentaho to use mysql database.
    Although I follow all the steps mentioned, users information are kept in _0.cfs file (..pentaho-solutions\system\jackrabbit\repository\workspaces\default\index\[someindex]). Can this be changed? Can I start pentaho reading users logins/password from mysql?
    BR,
    Ana

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s