Pentaho Server 8.1 with MariaDB (MySql)

[Edited on:
– December 2018 to correct config drivers for MariaDB.
– June 13th 2018, to add the solution note to Sql parameters.]

Migrate your Database

The official way to change the database is at pentaho-wiki and pentaho-doc. These are notes to my 2011 post that modifies the DEMO to use MariaDB instead of the HSQLDB that comes with it.

Important: I verified this on a Ubuntu 18.04 and MariaDB 10.1, keeping the  jcr (Jackrabbit repository) in thefilesystem and have a problem with JINDI SQL Queries that I have to substitute with JDBC SQL Queries (see the final note).

I. DATABASE DRIVER

Download the driver [Connector/J mariadb-java-client-2.3.0.jar] to ~/Pentaho8/pentaho-server/tomcat/lib.

If you have the desktop tools, copy the driver also to ~/Pentaho8/report-designer/lib, ~/Pentaho8/data-integration/lib, ~/Pentaho8/shema-workbench/lib.

II. DATABASE CONTENT

Create content in Database

Start the MariaDB engine and run the suplied scripts in ~/Pentaho8/pentaho-server/data/mysql5, to create required databases:

cd ~/Pentaho8/pentaho-server/data/mysql5
 mysql -u root-user -p root-password < create_quartz_mysql.sql
 mysql -u root-user -p root-password < create_repository_mysql.sql

Download the Sample Database (here), unzip and upload it:

mysql -u root-user -p root-password < SampleData_2018_mysql.sql

III. TOMCAT CONFIG

Hibernte Settings

1. Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
2. Open hibernate-settings.xml.
3. Find the  tag config-file and change it to

   system/hibernate/mysql5.hibernate.cfg.xml

4. Open mysql5.hibernate.cfg.xml change the class drivers

   <property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
   <property name="connection.url">jdbc:mariadb://localhost:3306/hibernate</property>

System connections

1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
2. Open context.xml
3. Change classname, url and SQL query to:

   <?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.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
   		maxWait="10000" username="hibuser" password="password"
   		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
   		validationQuery="select 1" />
   
   	<Resource name="jdbc/Quartz" auth="Container" type="javax.sql.DataSource"
   		factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" maxActive="20" minIdle="0" maxIdle="5" initialSize="0"
   		maxWait="10000" username="pentaho_user" password="password"
   		driverClassName="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/quartz"
   		validationQuery="select 1"/>
   
   </Context>

Spring config

1. Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
2. Open applicationContext-spring-security-hibernate.properties
3. Change driver, url and dialect to

   jdbc.driver=org.mariadb.jdbc.Driver
   jdbc.url=jdbc:mariadb://localhost:3306/hibernate
   jdbc.username=hibuser
   jdbc.password=password
   hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Add datasources to jndi list

1. Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
2. Open jdbc.properties
   1. Change the driver definitions to mysql

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

Stop HSQLDB from starting

1. Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
2. Open web.xml
3. Find the word HSQLDB. There are to points to edit. You will delete “–>” on the first line and “<–” on the last, to make it into a comment.In version 8.1 go to lines 25 and 167. They should look like 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] -->

   and

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

IV. Change Web Server Datasources

1. Start your BI server and log-in  (localhost:8080/pentaho) as Admin.
2. Click at the left top menu: Manage Data Sources
3. You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
4. Change the database type to MariaDb. Then change the Database Name to sampledata (the database name is in lowercase), port to 3306, type again the password (password). Click on TEST. Click OK if its succesfull.

Here, you can define a connection to your databases or you can return later.

That’s it, you should have a working Server with MariaDB. To test it by using the samples: Browse: Samples → Public → Steel Wheels.

V. Modify your SQL Queries that uses Parameters

But there is a problem if you want to use a query wit SQL with parameters. Maybe there is a missconfiguration in CDA or the repository, or a problem with the MySql driver for a MariaDB engine, as the parameters in the JNDI configuration report an error and do not work.

To get a result I use only SQL JDBC, by supplying these config parameters on each query

driver:    org.mariadb.jdbc.Driver
 url=      jdbc:mariadb://localhost:3306/sampledata?generateSimpleParameterMetadata=true
 user:     pentaho_admin
 password: password

Troubleshoot Log

If you have problems check the log file is ~/Pentaho8/pentaho-server/tomcat/logs/catalina.out its very verbose so look for ERROR lines

Advertisement