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

Advertisements

6 thoughts on “Pentaho Server 8.1 with MariaDB (MySql)

  1. Is there a reason why you didn’t use the MariaDB driver instead of the MySql driver? I haven’t tested it much as I mainly have Oracle databases to work, but it’s the driver I’ve been using with my Pentaho 8.0 tests.

  2. I’ve been trying for days, but I still have this error .. any ideas?

    Thank you

    ERROR [CompositeUserRoleListService] User/Role List could not be obtained.
    java.lang.IllegalStateException: Target of Bean was never resolved: org.springframework.security.core.userdetails.UserDetailsService

    • Not really.
      It seems like a security issue on a spring config.
      Those are files on ~/pentaho-server/pentaho-solutions/system but I only modify applicationContext-spring-security-hibernate.properties for MySQL Dialect.
      You should check MySql hibuser credentials. or your Jackrabbit repository ones.

  3. Do you use community editions? I also got User/Role List could not be obtained
    and I checked the jackrabbit database is empty, there is no sql for create tables for this database im ce version.

  4. There seems to be three questions:
    1. Yes, CE
    2. The JR repository in the demo is a filesystem one. there is a plugin in the marketplace to navigate it or you can use the BI server file navigator.
    3. The SQL scripts can be found in the data folder (BI server) you have run the commands to create the database and user. The BI server will create the content on boot
    I might add that I have never used that option I don’t see the point of it. To me, even using a repository is a silly requirement I suppose from an enterprise bureaucrat: Just imagine that github decided to force an encrypted and secured area with no navigational tool for all of us. haha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s