[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
- Goto to ~/Pentaho8/server/pentaho-server/pentaho-solutions/system/hibernate
- Open hibernate-settings.xml.
- Find the tag config-file and change it to
system/hibernate/mysql5.hibernate.cfg.xml
- 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
- Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/META-INF/
- Open context.xml
- 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
- Go to ~/Pentaho8/pentaho-server/Pentaho-solutions/system/
- Open applicationContext-spring-security-hibernate.properties
- 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
- Go to ~/Pentaho8/pentaho-server/pentaho-solutions/system/simple-jndi
- Open jdbc.properties
- 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
- Go to ~/Pentaho8/pentaho-server/tomcat/webapps/pentaho/WEB-INF/
- Open web.xml
- 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
- Start your BI server and log-in (localhost:8080/pentaho) as Admin.
- Click at the left top menu: Manage Data Sources
- You need to select SampleDataJDBC data-source and click on the gear icon to open the config window.
- 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
Interesting IT Tip's 