Use MySQL or MariaDB as your repository database (Archive installation)

Use these steps to install MySQL or MariaDB as the host database for the Pentaho Server in an archive installation of Pentaho.

• Prerequisites

• Components

• Initialize the repository database

• Configure the repository database

• Perform Tomcat-specific connection tasks

• Start the Pentaho Server

Prerequisites

Before you prepare your Pentaho Repository, prepare either a Windows or Linux environment:

• Prepare your Windows environment for an archive install

• Prepare your Linux environment for an archive install

MySQL Connector/J 8.0 cannot be used when configuring Tomcat with the JDBC driver JAR.

Components

The Pentaho Repository resides on the database you installed during the environment preparation step.

It includes these components:

• Jackrabbit Contains the solution repository, examples, security data, and content data from reports that you create.

• Quartz Holds data related to scheduling reports and jobs.

• Hibernate Holds data related to audit logging.

• (Optional) Pentaho Operations Mart Reports on system usage and performance.

Initialize the repository database

To initialize MySQL or MariaDB so that it serves as the Pentaho Repository, run several SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Pentaho Operations Mart components.

Use the ASCII character set when you run these scripts. Do not use UTF-8. UTF-8 can trigger string-length limits that can make scripts fail.

If your user, password, host, or port differs, update the examples to match your environment.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables with the QRTZ5_ prefix. Pentaho 10.2.0.1 and later use Quartz 2.x tables with the QRTZ6_ prefix.

When upgrading to 10.2.0.1 or later, you must create the QRTZ6_ tables by re-running the Quartz database creation step. You can then optionally migrate schedules using the provided migration script.

Back up your data before you run any SQL scripts.

Failure to complete the Quartz upgrade results in a Pentaho Server start-up error. In addition to the server error, the following exception message is generated in the catalina.log file:

Missing Quartz library database error

1

Step 1: Change default passwords

For production, follow best practices and change the default passwords in the SQL script files.

If you are evaluating Pentaho, you can skip this step.

1. Browse to the <your pentaho directory>/pentaho-server/data/mysql folder.

2. Use a text editor to update these scripts as needed for your existing user, password, database, and other values:

   • create_jcr_mysql.sql

   • create_quartz_mysql.sql

   • create_repository_mysql.sql

   • pentaho_mart_mysql.sql

3. Save and close the files.

2

Step 2: Run SQL scripts

When upgrading from previous Pentaho versions (including 10.2.0.0 GA to 10.2.0.1 and later), you must manually initialize a new Quartz database. This creates a new Quartz library in the repository database.

If you want to keep existing Quartz schedules, migrate the old tables to the new tables.

You may need administrator permissions to run these scripts on the host OS.

1. Back up your data. See Back up your existing Pentaho products and install Pentaho 11.0.

2. If the Pentaho Server is running, stop it. See Stop and start the Pentaho Server and repository.

3. Make sure your MySQL or MariaDB instance is running.

4. Open MySQL Workbench or a MySQL/MariaDB command prompt.

5. Run the SQL creation scripts in this order:

   1. <your pentaho directory>/pentaho-server/data/mysql/create_jcr_mysql.sql

   2. <your pentaho directory>/pentaho-server/data/mysql/create_quartz_mysql.sql

   3. <your pentaho directory>/pentaho-server/data/mysql/create_repository_mysql.sql

   4. <your pentaho directory>/pentaho-server/data/mysql/pentaho_mart_mysql.sql

   This creates the Quartz database, the Jackrabbit and MySQL/MariaDB repositories, and Pentaho Operations Mart.

   The scripts create the new Quartz scheduler tables prefixed with QRTZ6_.

   Any existing QRTZ5_ tables are retained. Starting the Pentaho Server at this point results in an empty schedule.

6. If you want to retain your existing scheduler data, open migrate_old_quartz_data_mysql.sql in a text editor.

7. Update the migration script for your user, password, database, and other values. Save the file.

8. Run the migration script:

   <your pentaho directory>/pentaho-server/data/mysql/migrate_old_quartz_data_mysql.sql

The original Quartz scheduler database is migrated to the new Quartz database.

The original QRTZ5_ data is retained. You can delete it after you confirm migration success.

You unpacked the Pentaho Operations Mart SQL file while preparing your environment for the archive installation.

See Prepare your Windows environment for an archive install or Prepare your Linux environment for an archive install.

3

Step 3: Verify MySQL or MariaDB initialization

Unless you changed it in Step 1, the default password for each username below is password.

After you run the scripts, verify the databases and user roles were created properly:

1. Open MySQL Workbench.

2. Log in as hibuser.

3. Under Schemas, expand hibernate > Tables.

4. If you installed Pentaho Operations Mart, expand pentaho_operations_mart > Tables.

5. Log in as jcr_user.

6. Under Schemas, expand jackrabbit > Tables.

   The Jackrabbit tables might not appear until after you start Pentaho for the first time.

7. Log in as pentaho_user.

8. Under Schemas, expand quartz > Tables.

9. Exit MySQL Workbench.

You have initialized the MySQL or MariaDB Pentaho Repository database.

Configure the repository database

Now that you have initialized your repository database, configure Quartz, Hibernate, Jackrabbit, and Pentaho Operations Mart for MySQL or MariaDB.

The examples assume MySQL on port 3306 with default passwords.

If your port or password differs, update the examples to match your environment.

1

Step 1: Set up Quartz on MySQL or MariaDB

Quartz stores scheduler data, such as scheduled reports.

You point Pentaho at the correct JobStore by updating quartz.properties.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/scheduler-plugin/quartz/quartz.properties.

2. In #_replace_jobstore_properties, set org.quartz.jobStore.driverDelegateClass:

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate

1. Save the file.

2

Step 2: Set Hibernate settings for MySQL or MariaDB

Hibernate settings point Pentaho to the correct Hibernate configuration file.

Hibernate also stores the audit logs used by Operations Mart.

Files are in pentaho/server/pentaho-server/pentaho-solutions/system/hibernate.

1. Open hibernate-settings.xml.

2. Find the <config-file> entry. Replace postgresql.hibernate.cfg.xml with mysql5.hibernate.cfg.xml:

From:

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

To:

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

1. Save and close the file.

2. (MariaDB only) Open mysql5.hibernate.cfg.xml.

3. Find the driver class and change it from MySQL to MariaDB:

From:

<property name="connection.driver_class">com.mysql.jdbc.Driver</property>

To:

<property name="connection.driver_class">org.mariadb.jdbc.Driver</property>

3

Step 3: Replace the audit log file with the MySQL version

Replace audit_sql.xml with the MySQL version.

Use the same file for MariaDB.

1. Locate pentaho-solutions/system/dialects/mysql5/audit_sql.xml.

2. Copy it into pentaho-solutions/system.

4

Step 4: Modify Jackrabbit repository information for MySQL or MariaDB

Update Jackrabbit to use the MySQL (or MariaDB) repository.

1. Open pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit/repository.xml.

2. Uncomment the MySQL lines.

3. Comment out the MS SQL Server, Oracle, and PostgreSQL lines.

4. Verify these sections use the MySQL values.

Item	Code Section
Repository	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="fs_repos_"/> </FileSystem>
DataStore	<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="databaseType" value="mysql"/> <param name="minRecordLength" value="1024"/> <param name="maxConnections" value="3"/> <param name="copyWhenReading" value="true"/> <param name="tablePrefix" value=""/> <param name="schemaObjectPrefix" value="ds_repos_"/> </DataStore>
Workspaces	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="fs_ws_"/> </FileSystem>
PersistenceManager (1st part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/> </PersistenceManager>
Versioning	<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="fs_ver_"/> </FileSystem>
PersistenceManager (2nd part)	<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager"> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="pm_ver_"/> </PersistenceManager>
DatabaseJournal	<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal"> <param name="revision" value="${rep.home}/revision.log"/> <param name="driver" value="javax.naming.InitialContext"/> <param name="url" value="java:comp/env/jdbc/jackrabbit"/> <param name="schema" value="mysql"/> <param name="schemaObjectPrefix" value="J_C_"/> <param name="janitorEnabled" value="true"/> <param name="janitorSleep" value="86400"/> <param name="janitorFirstRunHourOfDay" value="3"/> </Journal>

Perform Tomcat-specific connection tasks

After your repository is configured, configure the web application server to connect to the Pentaho Repository.

In this step, you will make JDBC and JNDI connections to the Hibernate, Jackrabbit, and Quartz components.

By default, the Pentaho Server software is configured to deploy and run on Tomcat. Connections are already specified. Only update the Tomcat context.xml file if you changed default ports or passwords.

Step 1: Download driver and apply to the Pentaho Server

To connect to a database, including the Pentaho Repository database, download and copy a JDBC driver to the right locations for the Pentaho Server and the web application server.

Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install these files yourself.

1. Download a JDBC driver JAR from your database vendor or a third-party driver developer. The JDBC drivers reference lists supported drivers.

2. Copy the JDBC driver JAR to the pentaho/server/pentaho-server/tomcat/lib folder.

3. Copy the hsqldb-2.3.2.jar file to pentaho-server/tomcat/lib if you want to keep the Pentaho sample.

Step 2: Modify JDBC connection information in the Tomcat XML file

Database connection and network settings (username, password, driver class, IP address or domain name, and port numbers) for your Pentaho Repository database are stored in the context.xml file.

Update this file to match your environment.

If you have a different port, password, user, driver class, or IP address, update the examples to match your environment.

1. Consult your database documentation to determine the JDBC class name and the connection string for your Pentaho Repository database.

2. Go to server/pentaho-server/tomcat/webapps/pentaho/META-INF and open context.xml in a text editor.

3. Add the following code if it does not already exist.

   For MySQL:

   <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate"
           driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
   <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/hibernate"
           driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
   <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/quartz"
           driverClassName="com.mysql.jdbc.Driver" password="password" username="pentaho_user"
           testOnBorrow="true" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
   <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart"
           driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
   <Resource validationQuery="select 1" url="jdbc:mysql://localhost:3306/pentaho_operations_mart"
           driverClassName="com.mysql.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
   <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           username="hibuser" password="password" driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/pentaho_dilogs" validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="jcr_user" password="password" driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://localhost:3306/jackrabbit" validationQuery="select 1"/>

   For MariaDB:

   <Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/hibernate"
           driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Hibernate"/>
   <Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/hibernate"
           driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Audit"/>
   <Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/quartz"
           driverClassName="org.mariadb.jdbc.Driver" password="password" username="pentaho_user"
           testOnBorrow="true" initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/Quartz"/>
   <Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/pentaho_operations_mart"
           driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/pentaho_operations_mart"/>
   <Resource validationQuery="select 1" url="jdbc:mariadb://localhost:3306/pentaho_operations_mart"
           driverClassName="org.mariadb.jdbc.Driver" password="password" username="hibuser"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           type="javax.sql.DataSource" auth="Container" name="jdbc/PDI_Operations_Mart"/>
   <Resource name="jdbc/live_logging_info" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           initialSize="0" maxActive="20" maxIdle="10" maxWait="10000"
           username="hibuser" password="password" driverClassName="org.mariadb.jdbc.Driver"
           url="jdbc:mariadb://localhost:3306/pentaho_dilogs" validationQuery="select 1"/>
   <Resource name="jdbc/jackrabbit" auth="Container" type="javax.sql.DataSource"
           factory="org.pentaho.di.core.database.util.DecryptingDataSourceFactory"
           maxActive="20" minIdle="0" maxIdle="5" initialSize="0" maxWait="10000"
           username="jcr_user" password="password" driverClassName="org.mariadb.jdbc.Driver"
           url="jdbc:mariadb://localhost:3306/jackrabbit" validationQuery="select 1"/>

4. Update usernames, passwords, driver classes, IP addresses (or domain names), and port numbers to match your environment.

5. Comment out resource references for databases you are not using.

6. Verify validationQuery is set to select 1 (for example: validationQuery="select 1").

7. Save context.xml, then close it.

Start the Pentaho Server

Now that you have completed the initial Pentaho archive installation steps, you are ready to start the Pentaho Server.

Starting the Pentaho Server after an archive installation