Tips on Installing software: Installing Tomcat and Pentaho

Installing Pentaho BI Server on Tomcat 7

Installing Tomcat 7
Upgrading Pentaho to Tomcat 7

Installing Tomcat 7.0.26 on OS X 10.7 Lion

This post is based on this excellent post by John W. Malis, updated to reflect details of Tomcat 7 and OS X 10.7 Lion installation.
Pentaho is currently compiled and distributed with Java 1.5, so it packs a bundle with Tomcat 6.0. I'm interested in run other applications besides Pentaho which demands Java 1.6 and Tomcat 7.0, so I decided to upgrade Pentaho bundle.
At the end of this process, we will have a Tomcat 7.0 running as a daemon, linked to the Apache HTTP server that comes with OS X by means of the MOD_JK connector, with the last Pentaho version deployed.

This installation is for a development environment, not a production environment.

Also, it assumes that you have Java installed. If not the case, follow this instructions to install Java.

Install OS X developer tools

The OS X developer tools must be installed to compile the MOD_JK connector, Native Library, or the JSVC daemon. The developer tools can be found on the OS X installation DVD or online. The XCode command line development tools must be installed along with the graphical tools. Users of Java 6 for Mac OS X 10.6 Update 3 or above must also install the Java Developer Package to compile Java Native Interface (JNI) applications. See the Java 6 Update 3 release notes.
The environment variable $CATALINA_HOME refers to the directory where you installed the Tomcat binaries and $JAVA_HOME refers to the directory of the JAVA runtime environment. At the time of this writing, Tomcat 7.0.26 is the latest Tomcat 7 release and requires at least Java 2 Standard Edition 6 (J2SE 6).
This variables are not used by the Tomcat daemon, but are needed to compile the MOD_JK connector and others.
This document assumes that you are installing on the client version of OS X 10.7.2 Lion with the latest security upgrades and JAVA 6 Framework. It also assumes you are logged in with an account with priviledges to run sudo as an administrator and running the bash shell.
It is helpful to have some knowledge of the basic UNIX commands and permissions, the GNU build system, and the infamous httpd configuration file (httpd.conf).

Name Soup

JAVA has many names that as far as Java on OS X is concerned, are effectively the same. OS X comes with a complete JAVA environment for both program execution and program development. This document uses these names interchangeably.

JAVA Developer's Kit 1.6 - JDK 1.6
JAVA 2 Standard Edition 6 - J2SE 6.0
JAVA 2 Runtime Environment - JRE 6.0
JAVA 2 version 6 - Java 1.6
JAVA 2 Software Development Kit - JAVA SDK
JAVA Virtual Machine - JVM

Selecting a place to install

Create the Tomcat folder in an appropriate directory - /Library or /usr/local is recommended, but for development convenience Tomcat can be also installed in your home directory or another place.
I prefer to have a /opt/servers/ folder where I install servers like Tomcat.
These instructions will follow this line, if another directory is used, you will have to assign appropriate permissions to that directory for these installation instructions to work.

# Create the /opt folder
sudo mkdir /opt

# Create the servers folder and set an appropriate owner and group
sudo mkdir servers
sudo chown username servers
sudo chgrp admin servers

# Change directories to /opt/servers
cd /opt/servers

# Create the Tomcat directory 
mkdir Tomcat

# Change directories to the newly created Tomcat directory and unpack the tar.gz files
cd Tomcat
tar -xvzf ~/Downloads/apache-tomcat-7.0.26.tar.gz
# or
tar -xvf ~/Downloads/apache-tomcat-7.0.26.tar

# Create a symbolic link that will allways point to the current version Tomcat directory
ln -sfhv apache-tomcat-7.0.26 TomcatHome

Understanding how to set environment variables

We need to define some environment variables, so...ops, how to do that?
How to set system wide (for all users and processes) and user (for processes launched by a specific user) environment variables?
The best explanation that I found is this one:

"The fundamental things to understand are:

environment variables are inherited by a process's children at the time they are forked.
the root process is a launchd instance, and there is also a separate launchd instance per user session.
launchd allows you to change its current environment variables using launchctl; the updated variables are then inherited by all new processes it forks from then on."

For local variables, the best solution is the RCenvironment preference pane. A simple FOSS application that sits as a preference panel in your System Preferences and help to maintain the ~/.MacOSX/environment.plist file which is read by loginwindow on login. It applies to all applications, including GUI ones. It requires you to logout and login again for changes to take effect.
As an option, you can use the command line:

launchctl setenv REPLACE_WITH_VAR REPLACE_WITH_VALUE

Or, if you want to set one just for the bash shell where you are working:

export JAVA_HOME=/Library/Java/Home

For "system wide environment variables", there is a better approach. Any application that will be started on boot (as a deamon) may contain its own definition of environment variables in it's property list file, so there is no need to have system wide environment variables. More on this latter.
But, in case you need, variables defined in /etc/launchd.conf are read by launchd when the system starts up and when a user logs in. They affect every single process on the system, because launchd is the root process. Again, to apply changes to the running root launchd you can pipe the commands into sudo launchctl.
So, in short, for system wide variables:

1) edit /etc/launchd.conf to contain, a line like this:

setenv REPLACE_WITH_VAR REPLACE_WITH_VALUE

If launchd.conf doesn't exists, create it.

2) reboot

Set the JAVA_HOME environment variable

On OS X, the JAVA_HOME environment variable must be set to /Library/Java/Home directory, which in turn is a symbolic link that points to the current version of Java SDK you are running.
You don't have to set JAVA_HOME if you are just running Tomcat 7 and not compiling anything. It is also not absolutely necessary to set CATALINA_HOME. It is automatically set in the startup scripts. CATALINA_HOME is set here, because it is referenced throughout this document.
In advance for Pentaho installation, also set JAVA_VERSION, PENTAHO_JAVA and PENTAHO_JAVA_HOME.
Using RCenvironment, you will end with something like this:

Edit tomcat-users.xml configuration file

You will need to add a name and password to the tomcat-users.xml configuration file to access the Tomcat management and administration programs.
Execute the following commands in Terminal:

# Change directories to the Tomcat configuration directory
cd TomcatHome/conf

# Edit the tomcat-users.xml file
nano tomcat-users.xml

# Add the following lines, where admin is the administrator
# name you assign and password is the password.

<role rolename="manager-gui"/>
<role rolename="admin-gui"/>
<user username="admin" password="password" roles="manager-gui,admin-gui"/>
<role rolename="PENTAHO_ADMIN"/>

# The tomcat-users.xml file should end looking something like this:
<tomcat-users>
...
<!--
<role rolename="tomcat"/>
<role rolename="role1"/>
<user username="tomcat" password="tomcat" roles="tomcat" />
<user username="role1"  password="tomcat" roles="role1"  />
<user username="both"   password="tomcat" roles="tomcat,role1" />
-->
<role rolename="manager-gui"/>
<role rolename="admin-gui"/>

<!-- Needed for Pentaho Installation -->
<role rolename="PENTAHO_ADMIN"/>

<user username="admin" password="password" roles="manager-gui,admin-gui"/>
</tomcat-users>

# Save the tomcat-users.xml file and quit the editor

Run and Test

Execute the following commands in Terminal:

# Change directories to where the Tomcat startup scripts are located
cd ../bin

# Remove the .bat scripts
rm *.bat

# Execute the Tomcat startup script
./startup.sh

# Check the Tomcat error log for errors
cd ../logs
less catalina.out

# Some have reported having trouble executing the startup scripts because 
# the execute permission has not been set for some reason. 
# You may have to do the following if this is the case:
cd ../bin
chmod 755 *.sh
# or if you want to tighten up the permissions on everything
chmod 750 *

From your web browser go to the URL http://localhost:8080/
You should see the Tomcat welcome screen:

Use the ./shutdown.sh script to stop Tomcat.

Secure Tomcat

Our configuration will connect Tomcat with the Apache HTTP server that already comes with Mac OS X installation. We want to ensure that all request to Tomcat will came through Apache.
Tomcat uses a mechanism called valves to filter IP source addresses. A particular type of valve element called a remote address filter, can be inserted into the Tomcat processing stream to allow or deny access to the server. The remote address filter may be used in several different containers: Engine, Host, or Context.
If you wish to secure your server for localhost use only, add the following lines to the engine container. Edit the $CATALINA_HOME/conf/server.xml configuration file.
Find the lines ....


<Engine name="Catalina" defaultHost="localhost" >

Add the following statements underneath ..


<Valve className="org.apache.catalina.valves.RemoteAddrValve" allow="127\.\d+\.\d+\.\d+|::1|0:0:0:0:0:0:0:1%0" />

For a complete description on the use of this mechanism see the Server Configuration Reference.

Using launchd to start Tomcat at Boot Time

There is a mismatch between how launchd expects a daemon to behave, and how the default startup scripts for Tomcat operate:

OS X's launchd expects the process it starts to run forever, but 'catalina.sh start' starts the JVM to run Tomcat and then exits.
Tomcat provides 'catalina.sh stop' to shut down Tomcat cleanly by connecting to a socket which Tomcat listens on, but launchd stops daemons by sending them a signal that kills the process immediately if no specific handling is included.

catalina.sh provides another way to start. If you pass "run" as parameter, it will start Tomcat without detaching from console. This is just the behavior launchd expects.
To start Tomcat at boot time, create a Launchd property list to load Tomcat.

In this file we can use keys to set values that are needed by Pentaho:

- the WorkingDirectory key
- the EnvironmentVariables key
- the UserName key

Pentaho BI server expects folders with data relatively to the working directory, so it must be specified to be the $TomcatHome/bin folder. If you fail to do this, you will get the following error message in Pentaho log files:
ERROR [SchemaUpdate] could not get database metadatajava.sql.SQLException: User not found: HIBUSER
Also, Pentaho writes data in the user home directory. When Launchd starts Tomcat during boot time, the /var/root folder becomes the home directory. This means that only the root user can access data written there. daemon.sh script has set the user to be a different user than root, so it can't access the /var/root folder.
Home directory must be set to be the home folder of the user that runs Tomcat or you get the following error:
Unable to read file '/var/root/.kettle/kettle.properties'/var/root/.kettle/kettle.properties (Permission denied)
Setting the UserName key makes the process run with the specified user, even when started by root. This makes all log files to be owned by the selected user.

To have enough room in memory to load applications, I added a CATALINA_OPTS environment variable setting memory options (-Xms256m -Xmx1024m -XX:MaxPermSize=256m), garbage collector options (-Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 ) and also debug options (-Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=8000).

Modify "username running Tomcat" to the value corresponding to your case.

Save the following lines into a file called org.apache.tomcat.plist.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
 <key>Disabled</key>
 <false/>
 <key>Label</key>
 <string>org.apache.tomcat</string>
 <key>ProgramArguments</key>
 <array>
  <string>/opt/servers/Tomcat/TomcatHome/bin/catalina.sh</string>
  <string>run</string>
 </array>
 <key>RunAtLoad</key>
 <true/>
 <key>UserName</key>
 <string>username running Tomcat</string>
 <key>WorkingDirectory</key>
 <string>/opt/servers/Tomcat/TomcatHome/bin</string>
 <key>ServiceDescription</key>   <string>Tomcat Server</string>
 <key>EnvironmentVariables</key>
 <dict>
  <key>HOME</key>
  <string>/Users/username running Tomcat</string>
  <key>CATALINA_OPTS</key>
  <string>-Xms256m -Xmx1024m -XX:MaxPermSize=256m -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000 -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=8000
</string>
  <!-- Next lines are just for my installation of Nexus and Jenkins -->
  <key>PLEXUS_NEXUS_WORK</key>
  <string>/opt/sonatype-work/nexus</string>
  <key>JENKINS_HOME</key>
  <string>/opt/jenkins_home</string>

 </dict>
</dict>
</plist>

Execute the following commands in terminal:

# Copy the org.apache.tomcat.plist file to the LaunchDaemons directory
sudo cp org.apache.tomcat.plist /Library/LaunchDaemons

# Change directories to the LaunchDaemons directory
sudo cd /Library/LaunchDaemons

# Make sure the property list is owned by root
sudo chown root org.apache.tomcat.plist

# Load and Test
sudo launchctl load /Library/LaunchDaemons/org.apache.tomcat.plist

ps -ef | grep Tomcat7

# Stop and unload Tomcat (started by root, so you need to sudo)
sudo launchctl unload /Library/LaunchDaemons/org.apache.tomcat.plist

Install Apache HTTPD Tomcat Connector - MOD_JK

We want to run Tomcat as well as the Apache HTTPD server, we will use the connector to pass requests from HTTPD to Tomcat in a more efficient way.
An alternative to using the mod_jk connector is to use httpd's mod_proxy and mod_proxy_ajp. The Tomcat Wiki includes a discussion on the alternatives to mod_jk.
The httpd.conf file loads for OS X client (<ifdefine !MACOSXSERVER>) configuration the mod_proxy_ajp. You can follow this instructions to configure the module.
I decided for this installation to use the Tomcat connector, but the mod_proxy_ajp may work equally well.

Download and unpack the Apache HTTPD Tomcat Connector - JK 1.2

Download JK 1.2.x tomcat-connectors-1.2.x-src.tar.gz source release archive from the Apache Tomcat Connectors Page
Unpack the source code into the directory of your choice. /opt/servers/Tomcat/src is a suggestion.
Keep in mind that this configuration is designed for only the most basic configuration of httpd.
This documentation doesn't go into much detail about supporting virtual hosts.

Build and install the connector

The OS X developer tools must be installed.

# Make the src dir
cd /opt/servers/Tomcat
mkdir src
# Download the connector from site and unzip it
cd src
tar -xvzf ~/Downloads/tomcat-connectors-1.2.32-src.tar.gz

# Change to the JK source directory
cd tomcat-connectors-1.2.32-src/native

# Configure a make file to create a 64-bit binary (Lion) for the same
# architecture you are compiling on.

./configure --with-apxs=/usr/sbin/apxs
# With the most recent source release you have to clean for some reason
make clean
# Build the connector
make
# Install the connector
sudo make install

Configure the connector

Configure MOD_JK manually by using the MOD_JK quick start instructions.
The following configuration will redirect all http requests for the following directories:

/examples
/host-manager*
/docs*
/manager*
/pentaho*
/pentaho-style*
/sw-style*

to the corresponding Tomcat application contexts.
We are setting pentaho context in advance to get it ready for the installation.
# Tip: If you want to open a folder not shown by File Finder, type the following command in a terminal window:
open /etc/apache2
Create a separate /etc/apache2/other/mod_jk.conf file and insert the following text:

# Load mod_jk module
# Update this path to match your modules location
# LoadModule jk_module libexec/apache2/mod_jk.so

#
# Following configurations are already made on httpd.conf
#
# Where to find workers.properties
# Update this path to match your conf directory location
# Put workers.properties in /etc/apache2 OR /etc/apache2/other
# JkWorkersFile /etc/apache2/other/workers.properties
# Where to put jk shared memory
# Update this path to match your local state directory or logs directory
# JkShmFile /var/log/apache2/mod_jk.shm
# Where to put jk logs
# Update this path to match your logs directory location (put mod_jk.log next to access_log)
# JkLogFile /var/log/apache2/mod_jk.log


# Set the jk log level [debug/error/info]
JkLogLevel info
# Select the timestamp log format
JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "
# Send everything for contexts /examples, /pentaho and others to worker named ajp13 (ajp13)
# If you are using virtual hosts you will need to put the following JkMounts in
# your apache2/extra/httpd-vhosts.conf file, maybe in the default
# ServerName localhost VirtualHost section or whatever is appropriate for your installation.
# If you add applications to your Tomcat server, you must update this list
JkMount /examples*      ajp13
JkMount /host-manager*  ajp13
JkMount /docs*          ajp13
JkMount /manager*       ajp13
JkMount /pentaho*       ajp13
JkMount /pentaho-style* ajp13
JkMount /sw-style*      ajp13

# Next entries are just for my installation of nexus and jenkins
JkMount /nexus*         ajp13
JkMount /jenkins*       ajp13

Create the file workers.properties in the /etc/apache2/ directory by cutting and pasting the following configuration text:

#
# The workers that jk should create and work with
#
worker.list=ajp13

#
# Defining a worker named ajp13 and of type ajp13
# Note that the name and the type do not have to match.
#
worker.ajp13.type=ajp13
worker.ajp13.host=localhost
worker.ajp13.port=8009

Modify httpd.conf

We need to do some small modifications on /etc/apache2/httpd.conf file.
If you open this file, you will notice that some features are controlled by defined environment variables like WEBSHARING_ON.
This environment variables are defined at launchd config file:
/System/Library/LaunchDaemons/org.apache.httpd.plist

This file is controlled by the System Preference Sharing panel, so it is wise to not
directly edit this file.
For OS X client (<ifdefine !MACOSXSERVER>), the httpd.conf file selects mod_proxy_ajp as connector.
For OS X server (<ifdefine MACOSXSERVER>), the jk_module is selected, but we can't just switch the configuration to server because others modules will be affected.
We need just to add the statement:

LoadModule jk_module libexec/apache2/mod_jk.so

to the httpd.conf file to load our compiled jk_module.
Before editing httpd.conf file, remeber to make a safe copy of it or you can get your Apache Web Server installation unusable.

# Make a copy of original httpd.conf file for safety
sudo cp /etc/apache2/httpd.conf /etc/apache2/httpd.conf.original

# Edit httpd.conf appending a statement to load jk_module
sudo nano /etc/apache2/httpd.conf

# Search for LoadModule word and add after it...
# Example:
# LoadModule foo_module modules/mod_foo.so
#
LoadModule jk_module libexec/apache2/mod_jk.so
LoadModule authn_file_module libexec/apache2/mod_authn_file.so
...

If you look ahead the file, you will find near the end, the place where some of mod_jk configurations are made:

<IfModule mod_jk.c>
   JKWorkersFile /etc/apache2/workers.properties
   JKLogFile /var/log/apache2/mod_jk.log
   JkShmFile /var/log/apache2/jk-runtime-status
</IfModule>

To reload httpd.conf file, use the System Preferences Sharing panel or the apachectl utility to stop and restart the HTTPD server.
Execute the following commands in Terminal:

# Stop the HTTPD server if it's already running
sudo apachectl graceful-stop

# Start the HTTPD server
sudo apachectl start

From your web browser go to the URL http://localhost/examples/
You should see the Tomcat JSP Examples screen.
If things are not working as expected, first use the console utility or command line to check the httpd error log at /var/log/apache2/error_log. You should see something like:
[notice] Apache/2.2.20 (Unix) DAV/2 mod_jk/1.2.32 configured -- resuming normal operations

Compile and Install Tomcat Native Library

If you are planning on running Tomcat in a production environment, it is advisable that you install the native library. The native library will improve the performance of your Tomcat installation.
The OS X developer tools must be installed.

Download and unpack Apache Portable Runtime (APR) and APR Utils

Download apr-1.4.x-src.tar.gz source release archive from the Apache APR Download Page
Unpack the source code into the directory of your choice. /opt/servers/Tomcat/src/ is a suggestion.
From the same place, download also the apr-util-1.4.x.tar.gz source archive.

Build and install APR

# Download the APR from site and unzip it
cd /opt/servers/Tomcat/src
tar -xvzf ~/Downloads/apr-1.4.5.tar.gz

# Change to the APR source directory
cd apr-1.4.5/
# Build the make file
# Users of 64-bit Java 6 should use the following configure command:
export CFLAGS='-arch x86_64' 
./configure

# Make the library
make
# Install APR
sudo make install

# Repeat for apr-utils
cd ../
tar -xvzf ~/Downloads/apr-util-1.4.1.tar.gz
cd apr-util-1.4.1/

# Build the make file
./configure --with-apr=/usr/local/apr

# Make the library
make

# Install APR utils
sudo make install

Build the Tomcat Native Library

# Change directories to Tomcat Binaries
cd /opt/servers/Tomcat/TomcatHome/bin

# Unpack the native library source code
tar -xvzf tomcat-native.tar.gz

# Change into the native source directory
cd tomcat-native-1.1.22-src/jni/native/

# Users of 64-bit Java 6 should use the following configure command:
export CFLAGS='-arch x86_64'
./configure --with-apr=/usr/local/apr --with-ssl=/usr/ssl  --with-java-home=/System/Library/Frameworks/JavaVM.framework/

# Make
make

# Copy the native library to /usr/lib/java
sudo cp .libs/libtcnative-1.0.1.22.dylib /usr/lib/java

# Create a symbolic link in /usr/lib/java to the native library
cd /usr/lib/java
sudo ln -sfhv libtcnative-1.0.1.22.dylib libtcnative-1.dylib
sudo ln -sfhv libtcnative-1.dylib libtcnative-1.jnilib

# Restart Tomcat
cd /Library/Tomcat/Home/bin
./shutdown.sh
./startup.sh

# Check the log to make sure APR is loaded
cd ../logs
tail -n 30 catalina.out
# You should see the following message:
# INFO: Loaded APR based Apache Tomcat Native library 1.1.20.


# There has been a problem with the pollersize setting that has appeared,
# disappeared and then reappeared. 
# If you get the error:INFO: Failed to create poller with specified size of 8192
# You will have to edit server.xml.
cd ../conf
nano server.xml

# Change the following line ...
<!-- Define an AJP 1.3 Connector on port 8009 -->
<Connector port="8009" protocol="AJP/1.3" redirectPort="8443" />

# To ..
<Connector port="8009" protocol="AJP/1.3" pollerSize="1024" redirectPort="8443" />

Install MySQL server

Download MySQL packed as a DMG file, complete with an installer, system preferences pane and a startup script directly from MySQL community server download page. Select Mac OS X as the platform and pick the (x86, 64-bit) DMG file. At the currrent date, there are only Mac OS X 10.6 versions available, but can be installed on Lion without problems.
Install in this order: mysql, the startup item, the preference pane.
On System Preferences, look for the MySql icon on "Other", click on it and start the server.

Test MySql installation

/usr/local/mysql/bin/mysql

Add alias to /etc/profile

# Edit /etc/profile file
sudo nano /etc/profile

# Add at the begining of the file
alias mysql=/usr/local/mysql/bin/mysql
alias mysqladmin=/usr/local/mysql/bin/mysqladmin

# To start or stop MySql
sudo /Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

Close the terminal and open a new one, so the alias statement takes effect.

OPTIONAL - RECOMMENDED: Set mysql root password

# Set your mysql root password
mysqladmin -u root password {new-password}
mysqladmin -u root -p{new-password} -h localhost password {new-password}
mysqladmin -u root -p{new-password} reload

# OPTIONAL: Clear the bash history so that {new-password} isn't in plain text on the disk.
# Warning!!! This will clear all your bash history!
history -c

# Ensure that the mysql.sock file can be found by PHP: 
sudo mkdir /var/mysql
sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

# Login with
mysql -u root -p

OPTIONAL - NOT SAFE: Enabling remote access to MySql

This instructions bind MySql on all addresses and let a user to connect from any IP.
Not safe at all, but sometimes I needed it.

The following options on /etc/my.cnf file control remote access:

bind-address : On /etc/my.cnf file, the bind-address option selects the IP mysql listens.
If you wish mysql listen on all IPs, don't use this option, or use bind-address=*.
skip-networking : Don't listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from file or put it in comment state.

# Edit the configuration file
sudo nano /etc/my.cnf

# Make sure that the following lines, if present, are commented in [mysqld] section:
[mysqld]
port = 3306
# bind-address = 127.0.0.1
....
# skip-networking
....

Restart MySQL. Now you should grant access to remote IP address, login to Mysql.

# Login into mysql database with
mysql mysql -u root -p

# Check databases
mysql>show databases;

# Check tables
mysql> show tables

# Grant a <user> access on all databases (*) from all IPs ('%')
mysql> grant all on * to <user>@'%';

...From remote system with mysql client installed...
#Remote login as <user> asking for password (-p)
mysql -u <user> -h <my sql server address> -p

#Test with telnet from another computer
telnet <my sql server address> 3306

MySql Tools

To work with MySql, I recommend this tools:

MySQL Workbench
Sequel Pro

Pentaho BI Suite 4.0 components

As the pentaho forum states here:

"The version numbers for the individual applications that make up the BI Suite 4.0 have not been synchronized to 4.0 for this release. Sorry for any confusion. I have been assured by our build guy that this was unavoidable and not a belligerent act meant to frustrate people."

Anyway, they have a good Pentaho Release Product Version Matrix that helps a lot to solve the confusion.

Pentaho BI Suite 4.0 community distribution available here is comprised of the following modules:

Component	Home Page	Version	File
Business Intelligence Server	Pentaho BI Platform and Server - Pentaho BI Server 3.9.0 stable (BI Suite 4.0)	3.10.0-stable	biserver-ce-3.10.0-stable.tar.gz
Pentaho Metadata	Pentaho BI Platform and Server - Pentaho Metadata Editor (BI Suite 4.0)	4.2.1-stable	pme-ce-4.1.0-stable.tar.gz
Data Integration	Pentaho Data Integration(Kettle)	4.2.1-stable	pdi-ce-4.2.1-stable.tar.gz
Report Designer	Pentaho Reporting - Report Creation Tools: Pentaho Report Designer	3.8.3-stable	prd-ce-mac-3.8.3-GA.tar.gz
Design Studio	Pentaho Reporting - Report Distribution: Pentaho Design Studio	4.0.0-stable	pds-ce-mac-64-4.0.0-stable.tar.gz

We also need Pentaho Analysis Services (Mondrian) components available here ...

Component	Version	File	Observations
Aggregation Designer	1.3.0-stable	pad-ce-1.3.0-stable.tar.gz
Schema Workbench	3.3.0-stable	psw-ce-3.3.0.14703.tar.gz
Mondrian	3.3.0.14703	mondrian-3.3.0.14703.zip	Do not download, it's already embedded in BI Server.

... and Pentaho Data Mining (Weka):

Component	Home Page	Version	File
Weka	Pentaho Data Mining Community Documentation	3-7-5	weka-3-7-5.dmg

Installing Pentaho BI components

Except the first one (Pentaho BI Server), all components from the list are desktop applications very easy to install on Mac OS X:

Create a folder named pentaho on Applications.
On the Downloads folder, click twice on each .tar.gz downloaded file.
Copy to Applications/pentaho the resulting folders.
Weka component is already a dmg file, so install as usual.

All components will appear in Launchpad. You can group them for easy access.
Data Integration and Metadata Editor have 32-bit and 64-bit versions. The 64-bit versions will be used.

Increasing Weka heap memory size

Weka is configured to use a maximum of 256 M as heap size. This can be a problem, so I increased it to start with 256 M and go up to 1024M.
To do this you need to right click on weka application icon, and select the "Show packages contents" menu option.
Open the Contents/info.plist file and locate the following snippet:

		<string>-Xmx256M</string>

Change to:

		<string>-Xms256M -Xmx1024M</string>

Installing Pentaho BI Server

Pentaho BI Server is distributed as a bundle of:

biserver-ce: a web application embedded in a Tomcat 5 container.
administration-console: a web application embedded in a Jetty container.

We will copy the pentaho folders to our recent Tomcat installation, and we will copy also the mysql connector to enable access to MySql database.

I decided to change the default location of pentaho-solutions folder to a separate place. This implies in configuring the new folder on pentaho application, more on this later.
So, my final layout will be:

/opt/pentaho/pentaho-solutions
/opt/servers/administration-console/
/opt/servers/Tomcat/TomcatHome

# Stop and unload Tomcat (started by root, so you need to sudo)
sudo launchctl unload /Library/LaunchDaemons/org.apache.tomcat.plist

# Verify Tomcat stops, next command must return an empty list with just grep
ps -ef | grep Tomcat7

# If Tomcat do not stops, issue the following command
# sudo /opt/servers/Tomcat/TomcatHome/bin/daemon.sh stop

# Copy pentaho apps folders
cp -r ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/tomcat/webapps/pentaho /opt/servers/Tomcat/TomcatHome/webapps
cp -r ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/tomcat/webapps/pentaho-style /opt/servers/Tomcat/TomcatHome/webapps
cp -r ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/tomcat/webapps/sw-style /opt/servers/Tomcat/TomcatHome/webapps
cp -r ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/tomcat/conf/Catalina/localhost /opt/servers/Tomcat/TomcatHome/conf/Catalina/localhost

# Copy mysql connector
cp ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/tomcat/lib/mysql-connector-java-5.1.17.jar /opt/servers/Tomcat/TomcatHome/lib/

# Make pentaho folder on /opt and set an appropriate owner and group
sudo mkdir /opt/pentaho
sudo chown username /opt/pentaho
sudo chgrp admin /opt/pentaho

# Copy pentaho-solutions to /opt/pentaho
cp -r ~/Downloads/biserver-ce-3.10.0-stable/biserver-ce/pentaho-solutions /opt/pentaho/

# Copy the administration console server to /opt/servers
cp -r ~/Downloads/biserver-ce-3.10.0-stable/administration-console /opt/servers/

Fixing broken navigation in JPivot

Here, in Pentaho forum, you can find dduenas fix for the problem of parent child hierarchies not working.

You can download the patched library from the forum (here, log into forum required) or from here (courtesy of stratebi.com)

After download, stop the server and:

# Go to pentaho lib folder
cd /opt/servers/Tomcat/TomcatHome/webapps/pentaho/WEB-INF/lib/

# Delete the original jar or you can get into trouble.  
# If you need, you can get it back from the distribution zip
rm jpivot-1.8.0-100420.jar

# Install the downloaded version
mv ~/Downloads/jpivot-1.8.0-100420.jar .

Remove deprecation warning message

To remove the deprecation warning:

Open <TomcatHome>/webapps/pentaho/jsp/pivot.jsp
Search for "deprecatedWarning"
Comment out the entire <div> node (including the </div>) with the above id.
Save and reload the browser to see changes.

STPivot, JPivot with steroids

People from stratebi improved JPivot in an open source project called STPivot.

If you want to try, here are the links:

Project home: stpivot at code.google
Download: stpivot-pentaho-biserver.beta.zip
Instructions: Installation and use

Database Preparation

Following procedures are derived from this Linux version post of Prashant Raju. I also link the zip with scripts to create the pentaho database in MySql.
In fact, inside pentaho bi distribution, there is a folder: biserver-ce/data/mysql5 with scripts to create the database, but this scripts are not useful because point to HSQLDB in place of MySql.
Insted, I used Prashant Raju SQL Script pack, so download it and also read his excellent post.

Scripts will create the following users-password pairs:

hibuser - password
pentaho_user - password
pentaho_admin - password

And the following databases:

hibernate
quartz
sampledata

Also, this scripts, fill the DATASOURCE table of HIBERNATE database with the correct values for MySQL access to sampledata:

DRIVERCLASS: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost:3306/sampledata
QUERY: SELECT 1

which is equivalent to run this sql (you can use MySQL Workbench):

UPDATE DATASOURCE SET DRIVERCLASS = 'com.mysql.jdbc.Driver', URL = 'jdbc:mysql://localhost:3306/sampledata' , QUERY = 'SELECT 1' WHERE NAME = 'SampleData'

Create Pentaho databases on MySql

First, download and expand the SQL Script Pack for MySql.

# Change to the downloaded pack folder, so it's easy to run the scripts from inside mysql
cd ~/Downloads/Pentaho_3.6.0_MySQL_SQL_Pack/

# Log into MySql 
mysql mysql -u root

# run the scripts
mysql> source 1_create_repository_mysql.sql;
...output
mysql> source 2_create_quartz_mysql.sql;
...output
mysql> source 3_create_sample_datasource_mysql.sql;
...output
mysql> source 4_load_sample_users_mysql.sql;
...output
mysql> source 5_sampledata_mysql.sql;
...output

# Check the created databases
mysql> show databases;

Just for reference here are the databases and tables which should of been created after loading the contents of the MySQL 5.x SQL Script pack:

hibernate*

authorities
datasource
granted_authorities
users

quartz

qrtz_blob_triggers
qrtz_calendars
qrtz_cron_triggers
qrtz_fired_triggers
qrtz_job_details
qrtz_job_listeners
qrtz_locks
qrtz_paused_trigger_grps
qrtz_scheduler_state
qrtz_simple_triggers
qrtz_trigger_listeners
qrtz_triggers

sampledata

customer_w_ter
customers
department_managers
dim_time
employees
offices
orderdetails
orderfact
orders
payments
products
quadrant_actuals
trial_balance

* Hibernate will create new tables into the hibernate database after Pentaho BI Platform has started for the first time.

NOTE↴

If you have problems when running the 5_sample_data_mysql.sql scripts with timestamps you will need to replace all occurances of 00.000000000 with 00.000000

Grant rights to pentaho_user and pentaho_admin over all databases

As we will add new databases, we need to grant rights to pentaho_user and pentaho_admin over them.

# Log into mysql
mysql -u root mysql

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pentaho_user'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pentaho_user'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pentaho_admin'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'pentaho_admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Common mistakes with users accounts in MySql

I knocked my head on the wall with this two kind of errors:

>mysql -u hibuser hibernate
ERROR 1045 (28000): Access denied for user 'hibuser'@'localhost' (using password: NO)

This is simple, you must pass the "-p" option in the command because this user has a password set, so you need to connect using password:

>mysql -u hibuser -p hibernate
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 92
...

The other was not so easy to understand:

>mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

until I read this from MySql Manual (yes, RTFM!!):

Two of the accounts have a user name of monty and a password of some_pass. 
Both accounts are superuser accounts with full privileges to do anything. 
The 'monty'@'localhost' account can be used only when connecting from the local host. 
The 'monty'@'%' account uses the '%' wildcard for the host part, 
so it can be used to connect from any host.

It is necessary to have both accounts for monty to be able 
to connect from anywhere as monty. 
Without the localhost account, the anonymous-user account for 
localhost that is created by mysql_install_db would take 
precedence when monty connects from the local host.
As a result, monty would be treated as an anonymous user.

The reason for this is that the anonymous-user account has 
a more specific Host column value than the 'monty'@'%' account 
and thus comes earlier in the user table sort order. 
(user table sorting is discussed in Section 5.4.4, 
“Access Control, Stage 1: Connection Verification”.)

Also, after recreating the database, appears the following message:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h xxxxxxx-MacBook-Pro.local password 'new-password'

where xxxxxxx-MacBook-Pro.local is your machine name, indicating that you also must fill the values for your hostname.
But, if you try to do this, it will not work, because when you try to connect, MySql finds the IP address as client, not the host name.

Modifying Pentaho configuration files to point to MySql Databases

Following files need to be modified to point to MySql.

jdbc.properties
This file is located under the /opt/pentaho/pentaho-solutions/system/simple-jndi directory.

Change the driver definitions to:

# Go to /opt/pentaho/pentaho-solutions/system/simple-jndi and edit the file
cd /opt/pentaho/pentaho-solutions/system/simple-jndi
nano jdbc.properties

#Edit to make like this:
#You can delete the five line shark statements as this workflow is no longer used.

SampleData/type=javax.sql.DataSource
SampleData/driver=com.mysql.jdbc.Driver
SampleData/url=jdbc:mysql://localhost:3306/sampledata
SampleData/user=pentaho_user
SampleData/password=password
Hibernate/type=javax.sql.DataSource
Hibernate/driver=com.mysql.jdbc.Driver
Hibernate/url=jdbc:mysql://localhost:3306/hibernate
Hibernate/user=hibuser
Hibernate/password=password
Quartz/type=javax.sql.DataSource
Quartz/driver=com.mysql.jdbc.Driver
Quartz/url=jdbc:mysql://localhost:3306/quartz
Quartz/user=pentaho_user
Quartz/password=password
SampleDataAdmin/type=javax.sql.DataSource
SampleDataAdmin/driver=com.mysql.jdbc.Driver
SampleDataAdmin/url=jdbc:mysql://localhost:3306/sampledata
SampleDataAdmin/user=pentaho_admin
SampleDataAdmin/password=password

pentaho-spring-beans.xml
This file is located under the /opt/pentaho/pentaho-solutions/system/ directory.
Edit the Spring XML files to use the JDBC DAOs instead of the Hibernate ones. Change every bean resource from hibernate to jdbc:

# Go to /opt/pentaho/pentaho-solutions/system/ and edit the file
cd /opt/pentaho/pentaho-solutions/system/
nano pentaho-spring-beans.xml

#Edit to make like this:

<beans>
<import resource="pentahoSystemConfig.xml" />
<import resource="adminPlugins.xml" />
<import resource="systemListeners.xml" />
<import resource="sessionStartupActions.xml" />
<import resource="applicationContext-spring-security.xml" />
<import resource="applicationContext-common-authorization.xml" />
<import resource="applicationContext-spring-security-jdbc.xml" />
<import resource="applicationContext-pentaho-security-jdbc.xml" />
<import resource="pentahoObjects.spring.xml" />
</beans>

applicationContext-spring-security-jdbc.xml and the passwordEncoder problem

This file is located under the /opt/pentaho/pentaho-solutions/system/ directory.

Once the file has opened locate this snippet of 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>

<bean id="passwordEncoder"
   class="org.springframework.security.providers.encoding.PlaintextPasswordEncoder"/>

Make changes to the highlighted sections so that the section of code looks similar to this:

<!--  This is only for Hypersonic. Please update this section for any other database you are using -->
<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>


<bean id="passwordEncoder"
   class="org.pentaho.platform.engine.security.DefaultPentahoPasswordEncoder"/>

What is the passwordEncoder problem? Well, in our MySql scripts for hibernate database, passwords are stored encripted, not in plain text as the default passwordEncoder expects.

This means that you can't log into Pentaho because passwords don't match giving FORBIDDEN errors.

The problem is that the Pentaho Administrator Console must be able to update users passwords, so, both applications must define the same passwordEncoder.

Pentaho Administrator Console password encoder definition is stored in the applicationContext-spring-security-hibernate.xml file.

If you decide to use other encoder (org.springframework.security.providers.encoding.Md5PasswordEncoder for example), you must modify both files and use the PAC to update the stored passwords in the hibernate.USERS table to reflect the MD5 encoding or you will not be able to login in Pentaho.

applicationContext-spring-security-hibernate.properties

This file is located under the /opt/pentaho/pentaho-solutions/system/ directory.

Once the file has opened locate this snippet of 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 changes to the highlighted sections so that the section of code looks similar to this:

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

hibernate-settings.xml

This file is located under the /opt/pentaho/pentaho-solutions/system/hibernate/ directory.

Once the file has opened locate this snippet of code:

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

Make changes to the highlighted section so that the section of code looks similar to this:

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

Modifying username/password of hibernate database

I used the default username and password (hibuser/password) for the hibernate database. To use other values, you need to change all occurrences of the pair in the previous files and also in the mysql5.hibernate.cfg.xml file under /opt/pentaho/pentaho-solutions/system/hibernate/ directory.
This also applies to the pentaho_user/password pair used to connect to the Quartz database, present in the next file.

context.xml
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 made to the context.xml file which is located in the /opt/servers/Tomcat/TomcatHome/webapps/pentaho/META-INF/ directory.

Also we need to add an entry to access the SampleData, FoodMart and LoadingArea databases.

Once the file has opened the following piece of code should be visible:

<?xml version="1.0" encoding="UTF-8"?>

    <Context path="/pentaho" docbase="webapps/pentaho/">
    <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"/>
    </Context>

Make changes to the highlighted sections so that the section of code looks similar to this:

<?xml version="1.0" encoding="UTF-8"?>
    <Context path="/pentaho">
    <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"/>

    <Resource name="jdbc/SampleData" 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/sampledata"
    validationQuery="select 1"/>

<Resource name="jdbc/FoodMart" 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/foodmart"
    validationQuery="select 1"/>

<Resource name="jdbc/DataMart" 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/datamart"
    validationQuery="select 1"/>

<Resource name="jdbc/LoadingArea" 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/loading_area"
    validationQuery="select 1"/>

    </Context>

Make the same changes to
/opt/servers/Tomcat/TomcatHome/conf/Catalina/localhost/pentaho.xml or you will get the following error:

ERROR [org.pentaho.platform.util.logging.Logger] misc-org.pentaho.platform.scheduler.QuartzSystemListener: QuartzSystemListener.ERROR_0001 - Scheduler was not properly initialized at startup
org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.JobPersistenceException: Failed to obtain DB connection from data source 'myDS': java.sql.SQLException: Could not retrieve datasource via JNDI url 'java:comp/env/jdbc/Quartz' org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (socket creation error) [See nested exception: java.sql.SQLException: Could not retrieve datasource via JNDI url 'java:comp/env/jdbc/Quartz' org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (socket creation error)]]
...
* Nested Exception (Underlying Cause) ---------------
org.quartz.JobPersistenceException: Failed to obtain DB connection from data source 'myDS': java.sql.SQLException: Could not retrieve datasource via JNDI url 'java:comp/env/jdbc/Quartz' org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (socket creation error) [See nested exception: java.sql.SQLException: Could not retrieve datasource via JNDI url 'java:comp/env/jdbc/Quartz' org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (socket creation error)]
...
* Nested Exception (Underlying Cause) ---------------
java.sql.SQLException: Could not retrieve datasource via JNDI url 'java:comp/env/jdbc/Quartz' org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (socket creation error)

This message appears when HSQL database startup is disabled in tomcat/webapps/pentaho/WEB-INF/web.xml
Don’t make change in quartz.properties especially:

org.quartz.jobStore.dataSource = myDS
…
org.quartz.dataSource.myDS.jndiURL = Quartz

At the end of this page there is an explanation about deletion of docbase="webapps/pentaho/"

Configuring other Pentaho options

web.xml

To configure the settings of the Apache-Tomcat server for your Pentaho BI Platform most of the changes are done inside the web.xml file which is located under the /opt/servers/Tomcat/TomcatHome/webapps/pentaho/WEB_INF/ directory. You are able to configure the following items (and more) for the Pentaho BI Platform:

pentaho-solutions location URL
Disable HSQL database startup
Add SampleData access
TrustedIpAddrs (optional - for the administration console and if you are accessing the server remotely)

If you are happy with the following settings for your Pentaho BI Platform server you will not need to make any changes to this file:

pentaho-solutions/ directory located under the /opt/biserver-ce/ directory
Visit http://localhost:8080/pentaho URL to launch the Pentaho BI Platform

solution-path
The solution-path parameter lets the Pentaho BI Platform know where to locate the pentaho-solutions directory. By default this is set to /opt/biserver-ce/pentaho/ directory. If you have decided to use an existing Apache-Tomcat server (or have moved your pentaho-solutions directory) you will need to point this to where you have placed your pentaho-solutions directory. In this example my pentaho-solutions directory is under the /opt/pentaho/ directory, now my solution-path code snippet looks like this:

<context-param>
    <param-name>solution-path</param-name>
    <param-value>/opt/pentaho/pentaho-solutions/</param-value>
</context-param>

fully-qualified-server-url My setup enables access to pentaho via apache web server. So anyone can If you are happy with visiting the URL http://localhost:8080/pentaho to access Pentaho's BI Platform you will not need to change this parameter, however if you would like others to access the site (remotely or on a network) you will need to make changes to this parameter. Open up the file and locate this line of code:

<param-value>http://localhost:8080/pentaho/</param-value>

Make changes to the highlighted section to your PC or server's domain or IP address so it looks similar to this:

<param-value>http://<your domain>/pentaho/</param-value>
    or
  <param-value>http://<your ip>/pentaho/</param-value>

Disable HSQL Database Startup By default with 3.7 HSQL database starts up automatically - to prevent this from happening locate the following snippets of code:

<!-- [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] -->
...
<!-- [BEGIN HSQLDB STARTER] -->
    <listener>
    <listener-class>org.pentaho.platform.web.http.context.Hsqldb StartupListener</listener-class>
    </listener>
<!-- [END HSQLDB STARTER] -->

You can either remove the above snippets or comment it out, if you are commenting it out it will look similar to this:

<!-- [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] -->
...
<!-- [BEGIN HSQLDB STARTER] -->
    <!-- 
    <listener>
    <listener-class>org.pentaho.platform.web.http.context.Hsqldb StartupListener</listener-class>
    </listener>
    -->
<!-- [END HSQLDB STARTER] -->

Add SampleData access and others

As we disabled the HSQLDB access, we need to add the following lines near the end of the web.xml file, under the comment:

<!-- insert additional tag libs -->
...
	<resource-ref>
		<description>SampleData Connection</description>
		<res-ref-name>jdbc/SampleData</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

	<resource-ref>
		<description>FoodMart Connection</description>
		<res-ref-name>jdbc/FoodMart</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

	<resource-ref>
		<description>DataMart Connection</description>
		<res-ref-name>jdbc/DataMart</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

	<resource-ref>
		<description>LoadingArea Connection</description>
		<res-ref-name>jdbc/LoadingArea</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

Failing to do add SampleData modifying context.xml and web.xml, generates the following error:

ERROR [org.pentaho.platform.util.logging.Logger] misc-org.pentaho.platform.engine.services.connection.datasource.dbcp.PooledDatasourceSystemListener: PooledDatasourceSystemListener.ERROR_0003 – Unable to pool datasource object: SampleData caused by java.sql.SQLException: socket creation error

TrustedIpAddrs

If you want to access your Tomcat-Apache server remotely - so in the above step you have not specified localhost or 127.0.0.1 for the base-url parameter - you will need to add your Tomcat-Apache server's IP address to this list.

Open up the file and locate this line of code:

<param-name>TrustedIpAddrs</param-name>
<param-value>127.0.0.1</param-value>

Make changes to the highlighted section add your PC or server's domain or IP address so it looks similar to this:

<param-name>TrustedIpAddrs</param-name>
<param-value>127.0.0.1,[your_ip_address]</param-value>

This will allow the Pentaho Administration Console to 'ping' the server to see if it is up or down - you do not need to do this if you are hosting your server locally.

Missing jspFiles

Look at the end of this page and comment the lines with references to BackgroundExecutionStatus and RepositoryTree because them refer to missing jsp pages.

Add security role PENTAHO_ADMIN
Add the following to the end of web.xml file:


	<security-role>
		<description>Solve the warning. The role that is required.</description>
		<role-name>PENTAHO_ADMIN</role-name>
	</security-role>


</web-app>

Other Parameters
You can also change the local language and country under the web.xml file, the changes to these parameters are self explanatory.

Configuring Publishing - publisher_config.xml
By default publishing is not enabled, to enable it you will need to specify a password which will need to be used when publishing. To get started you will need to edit the publisher_config.xml file located under the /pentaho-solutions/system/ directory, once open locate the following snippet of code:

<publisher-config>
<publisher-password></publisher-password>
</publisher-config>

Enter a password between the publisher-password tags (this password will be the same for all users) so the snippet of code looks similar to the example below (in this example the publisher password is publishthis):

<publisher-config>
<publisher-password>publishthis</publisher-password>
</publisher-config>

From now on when any user tries to publish content to Pentaho BI Platform they will need to specify this password.

Disable constraints on hibernate database PRO_ACLS_LIST table

The first run of Pentaho BI, I get an error about constraints during a batch update:

ERROR [JDBCExceptionReporter] Cannot add or update a child row: a foreign key constraint fails (`hibernate`.`pro_acls_list`, CONSTRAINT `FKB65646C2B23C5D30` FOREIGN KEY (`ACL_ID`) REFERENCES `PRO_FILES` (`FILE_ID`))
...
[AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

...
Caused by: java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`hibernate`.`pro_acls_list`, CONSTRAINT `FKB65646C2B23C5D30` FOREIGN KEY (`ACL_ID`) REFERENCES `PRO_FILES` (`FILE_ID`))
...
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`hibernate`.`pro_acls_list`, CONSTRAINT `FKB65646C2B23C5D30` FOREIGN KEY (`ACL_ID`) REFERENCES `PRO_FILES` (`FILE_ID`))

To solve this, I dropped the constraint to enable the batch update of permission tables. I started and stopped Pentaho once and after it, I enabled the constraint again. On the second turn, everything run ok.
Log on mysql to make the changes:

# Open a console and log on mysql admin
mysql hibernate -u hibuser -p

-- Discover the constraint name, you can also discover it
-- from the error log.
SHOW CREATE TABLE PRO_ACLS_LIST;

-- Drop the constraint, use the key from the 
-- previous statement (ex: FKB65646C2B23C5D30)  
ALTER TABLE PRO_ACLS_LIST DROP FOREIGN KEY `xxxxxxx`;  

-- Start Pentaho and stop it, after that 
-- add the constraint again
ALTER TABLE PRO_ACLS_LIST ADD CONSTRAINT FOREIGN KEY (`ACL_ID`) REFERENCES PRO_FILES (`FILE_ID`) ON DELETE CASCADE ON UPDATE CASCADE;

Configuring Pentaho Administration Console to run as a service on Mac OS X

Penthao Administration Console is bundled with a Jetty 6.1 version and is not configured by default to run as a service, connect to MySql or run behind Apache HTTP server, so I have some work to do.

Installing Pentaho Administrator Console as a service

To install the Administrator Console as a service, write a plist file and save as org.pentaho.adminconsole.plist at /Library/LaunchDaemons/ with the following content:

# Create the plist file and add the following content
sudo nano /Library/LaunchDaemons/org.pentaho.adminconsole.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
 <key>Disabled</key>
 <false/>
 <key>Label</key>
 <string>org.pentaho.adminconsole</string>
 <key>ProgramArguments</key>
 <array>
  <string>/opt/servers/administration-console/start-pac.sh</string>
  <string>run</string>
 </array>
 <key>RunAtLoad</key>
 <true/>
 <key>UserName</key>
 <string>username running Tomcat</string>
 <key>ServiceDescription</key>
 <string>Pentaho Administrator Console</string>
<key>WorkingDirectory</key>
 <string>/opt/servers/administration-console</string>
 <key>EnvironmentVariables</key>
 <dict>
  <key>PENTAHO_JAVA_HOME</key>
  <string>/Library/Java/Home</string>
  <key>PENTAHO_JAVA</key>
  <string>javae</string>
 </dict>
</dict>
</plist>

Before start the console, edit the file /opt/servers/Tomcat/administration-console/resource/config/console.xml to set the correct configuration values.

# Edit the file and set the correct values as follows
nano  /opt/servers/administration-console/resource/config/console.xml

<?xml version="1.0" encoding="UTF-8"?>
<console>
  <solution-path>./../../pentaho/pentaho-solutions</solution-path>
  <war-path>./../Tomcat/TomcatHome/webapps/pentaho</war-path>
  <platform-username>joe</platform-username>  
  <biserver-status-check-period-millis>30000</biserver-status-check-period-millis>  
  <homepage-url>http://www.pentaho.com/console_home</homepage-url>
  <homepage-timeout-millis>15000</homepage-timeout-millis>
  <!-- comma separated list of roles (no spaces) -->  
  <default-roles>Authenticated</default-roles>
  <default-server-dir>Tomcat/TomcatHome</default-server-dir>
</console>

Where :
- solution-path: Relative path to the pentaho solutions folder
- war-path: Relative path to the war folder of pentaho application war
- default server dir: Server folder name, in our case, TomcatHome

To start the admin console server, just load the plist file:

# Start the administration console server
sudo launchctl load /Library/LaunchDaemons/org.pentaho.adminconsole.plist

Set a logs folder to store Pentaho Administrator logs

Edit the file /opt/servers/Tomcat/administration-console/resource/config/log4j.xml to make logs go to a logs folder.

# Edit the file
nano  /opt/servers/administration-console/resource/config/log4j.xml

# Look for this line:
      <param name="File" value="server.log"/>

# Change to:
      <param name="File" value="logs/server.log"/>

Solving Pentaho warnings and errors

During the migration to Tomcat 7, I solved this errors:

org.apache.catalina.startup.ContextConfig validateSecurityRoles
INFO: WARNING: Security role name PENTAHO_ADMIN used in an <auth-constraint> without being defined in a <security-role>

Modify the .../TomcatHome/webapps/pentaho/WEB-INF/web.xml and add the highlighted text to the end of the file

	<security-constraint>
		<display-name>Default JSP Security Constraints</display-name>
		<web-resource-collection>
			<web-resource-name>Portlet Directory</web-resource-name>
			<url-pattern>/jsp/*</url-pattern>
			<http-method>GET</http-method>
			<http-method>POST</http-method>
		</web-resource-collection>
		<auth-constraint>
			<role-name>PENTAHO_ADMIN</role-name>
		</auth-constraint>
		<user-data-constraint>
			<transport-guarantee>NONE</transport-guarantee>
		</user-data-constraint>
	</security-constraint>

	<security-role>
		<description>Solve the warning. The role that is required.</description>
		<role-name>PENTAHO_ADMIN</role-name>
	</security-role>


</web-app>

Next warning:

org.apache.catalina.startup.SetContextPropertiesRule begin
WARNING: [SetContextPropertiesRule]{Context} Setting property 'docbase' to 'webapps/pentaho/' did not find a matching property.

This was hard to find.

In context.xml and pentaho.xml, you have:
<Context path="/pentaho" docbase="webapps/pentaho/" >

It's not "docbase", it's "docBase", the property was misspelled, see the context doc here.

You need to change it in two places:

.../TomcatHome/webapps/pentaho/META-INF/context.xml
.../TomcatHome/conf/Catalina/localhost/pentaho.xml

But..after that I get the following error:

Mar 16, 2012 9:23:08 AM org.apache.catalina.startup.HostConfig deployDescriptor WARNING: A docBase /opt/servers/Tomcat/TomcatHome/apache-tomcat-7.0.26/webapps/webapps/pentaho inside the host appBase has been specified, and will be ignored

so I changed from docBase="webapps/pentaho/" to docBase="pentaho/" in both files.
But then,...

Mar 16, 2012 9:29:54 AM org.apache.catalina.startup.HostConfig deployDescriptor WARNING: A docBase /opt/servers/Tomcat/TomcatHome/apache-tomcat-7.0.26/webapps/pentaho inside the host appBase has been specified, and will be ignored

So I decided to wipe out the docBase definition ending with:

<Context path="/pentaho" >

Missing jspFile error:

This error appears twice:

Mar 14, 2012 11:49:30 AM org.apache.catalina.core.StandardContext loadOnStartup SEVERE: Servlet /pentaho threw load() exception javax.servlet.ServletException: missing jspFile

To solve, edit the .../TomcatHome/webapps/pentaho/WEB-INF/web.xml and comment references to /jsp/BackgroundStatus.jsp and /jsp/RepositoryTree.jsp because both are missing in webapps/pentaho/jsp folder.

For each missing file, there are two references in web.xml, one for the servlet and another for the servlet mapping, so, you will end with:

...
<!--
	<servlet>
		<servlet-name>BackgroundExecutionStatus</servlet-name>
		<jsp-file>/jsp/BackgroundStatus.jsp</jsp-file>
	</servlet>
-->

...

<!--
	<servlet-mapping>
		<servlet-name>BackgroundExecutionStatus</servlet-name>
		<url-pattern>/BackgroundExecutionStatus</url-pattern>
	</servlet-mapping>
-->

...

<!--
	<servlet>
		<servlet-name>RepositoryTree</servlet-name>
		<jsp-file>/jsp/RepositoryTree.jsp</jsp-file>
	</servlet>
-->

...

<!--
	<servlet-mapping>
		<servlet-name>RepositoryTree</servlet-name>
		<url-pattern>/RepositoryTree</url-pattern>
	</servlet-mapping>
-->

Broken ÇhartBeans Examples

I got this tip form here.

When you change databases, the examples on bi-developers->ChartBeans Examples, broke.

Edit the files on:
/opt/pentaho/pentaho-solutions/bi-developers/chart/*.xaction

Replace Sql text:
CAST(SUM(ORDERFACT.TOTALPRICE) AS INTEGER) AS “PRICE”

to:
CAST(SUM(ORDERFACT.TOTALPRICE) AS UNSIGNED) AS “PRICE”

# Go to folder
/opt/pentaho/pentaho-solutions/bi-developers/charts

# Change all files at once with sed and backup the original version as .bak
find *.xaction -type f -print0 | xargs -0 sed -i .bak -e 's/CAST(SUM(ORDERFACT.TOTALPRICE) AS INTEGER) AS "PRICE"/CAST(SUM(ORDERFACT.TOTALPRICE) AS UNIGNED) AS "PRICE"/g'

In file chartbeans_scatter.xaction, change the sql:

WHERE DEPT='Sales' OR DEPT='Finance'

for:
WHERE DEPARTMENT IN ('Sales','Finance')

Also edit the 'Flash Chart List' SQL and make a search and replace for several INTEGER to UNSIGNED in:

/opt/pentaho/pentaho-solutions/bi-developers/charts/pentahoxml_picker.xaction

To make the simple dial example work (OpenFlash doesn’t work on the CE edition) we use the JFree drawing engine, make a change in:

/opt/pentaho/pentaho-solutions/bi-developers/charts/chartbeans_dialchart.xml

from:
<chartmodel chartEngine="OpenFlashChart"

to:
<chartModel chartEngine="JFreeChart"

No solution for this one:
ERROR [com.tonbeller.tbutils.res.JNDIResourceProvider] error closing context javax.naming.OperationNotSupportedException: Context is read only

It happens only with Tomcat 7. I couldn't solve it yet. It seems to not affect anything.

Adding extra databases

From here, download foodmart example and AWBackup:

I followed sugestion from comment of Guido Legemaate and added the following MYSQL statements to lines 18-21 of the ‘foodmart_mysql.sql‘ script, which basically creates a new database (foodmart), a user (foodmart) with identical password and orders the server to use this database for loading the tables:

CREATE DATABASE IF NOT EXISTS foodmart;
grant all on foodmart.* to foodmart identified by ‘foodmart’;

USE foodmart;

# Change to the downloaded pack folder, so it's easy to run the scripts from inside mysql
cd ~/Downloads/

# Log into MySql 
mysql mysql -u root

-- run the scripts
mysql> source foodmart_mysql.sql;
...output
mysql> source AWBackup.sql;
...output

-- Create aux databases
create database loading_area;
create database datamart;

-- Check the created databases
mysql> show databases;
mysql>quit

# Edit /opt/pentaho/pentaho-solutions/system/simple-jndi/jdbc.properties
# Add references to new databases at the end of the file
nano /opt/pentaho/pentaho-solutions/system/simple-jndi/jdbc.properties

FoodMart/type=javax.sql.DataSource
FoodMart/driver=com.mysql.jdbc.Driver
FoodMart/url=jdbc:mysql://localhost:3306/foodmart
FoodMart/user=pentaho_user
FoodMart/password=password
LoadingArea/type=javax.sql.DataSource
LoadingArea/driver=com.mysql.jdbc.Driver
LoadingArea/url=jdbc:mysql://localhost:3306/loading_area
LoadingArea/user=pentaho_user
LoadingArea/password=password
Datamart/type=javax.sql.DataSource
Datamart/driver=com.mysql.jdbc.Driver
Datamart/url=jdbc:mysql://localhost:3306/datamart
Datamart/user=pentaho_user
Datamart/password=password

You need to update de Hibernate database with datasource connection information.
Follow instructions here, step 9.

Give users rigths over the new databases.

Install Saiku

Download Saiku Pentaho BI Server Plugin 2.2 and Saiku UI War version 2.2 from here.

To install:
$ cp ~/Downloads/saiku-webapp-2.2.war /opt/servers/Tomcat/TomcatHome/webapp/saiku.war

Open and extract the plugin content (a saiku folder) into
/opt/pentaho/pentaho-solutions/system/

That's it.

Pages

Installing Tomcat and Pentaho

Installing Pentaho BI Server on Tomcat 7

Installing Tomcat 7.0.26 on OS X 10.7 Lion

Install OS X developer tools

Name Soup

Selecting a place to install

Understanding how to set environment variables

Set the JAVA_HOME environment variable

Edit tomcat-users.xml configuration file

Run and Test

Secure Tomcat

Using launchd to start Tomcat at Boot Time

Install Apache HTTPD Tomcat Connector - MOD_JK

Download and unpack the Apache HTTPD Tomcat Connector - JK 1.2

Build and install the connector

Configure the connector

Modify httpd.conf

Compile and Install Tomcat Native Library

Download and unpack Apache Portable Runtime (APR) and APR Utils

Build and install APR

Build the Tomcat Native Library

Install MySQL server

Test MySql installation

Add alias to /etc/profile

OPTIONAL - RECOMMENDED: Set mysql root password

OPTIONAL - NOT SAFE: Enabling remote access to MySql

MySql Tools

Pentaho BI Suite 4.0 components

Installing Pentaho BI components

Installing Pentaho BI Server

Fixing broken navigation in JPivot

STPivot, JPivot with steroids

Database Preparation

Create Pentaho databases on MySql

Common mistakes with users accounts in MySql

Modifying Pentaho configuration files to point to MySql Databases

Configuring other Pentaho options

Disable constraints on hibernate database PRO_ACLS_LIST table

Configuring Pentaho Administration Console to run as a service on Mac OS X

Installing Pentaho Administrator Console as a service

Solving Pentaho warnings and errors

Adding extra databases

Install Saiku