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

Legacy page. Content moved to the main topic.

This page is kept for existing links. Go to Manual installation process.

Install MySQL or MariaDB as the host database for the Pentaho Server repository.

Before you begin

Prerequisites

Prepare your environment first:

• Prepare your Windows environment for a manual installation

• Prepare your Linux environment for a manual installation

Repository components

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

It includes these components:

• Jackrabbit Contains the solution repository, examples, security data, and report content data.

• Quartz Stores scheduler data for reports and jobs.

• Hibernate Stores audit logging data.

• (Optional) Pentaho Operations Mart Stores system usage and performance data.

Initialize the MySQL or MariaDB Pentaho Repository database

Run SQL scripts to create the Hibernate, Quartz, Jackrabbit (JCR), and Operations Mart components.

Use the ASCII character set when you run these scripts. Do not use UTF-8. Script failures can occur due to string-length limits.

If you use different ports, users, or passwords, update the examples. Match your environment values.

Pentaho 10.2.0.0 and earlier use Quartz 1.x tables (QRTZ5_). Pentaho 10.2.0.1 and later use Quartz 2.x tables (QRTZ6_).

When you upgrade to 10.2.0.1 or later, you must create the QRTZ6_ tables. You can optionally migrate existing schedules using the migration script.

If you run repository SQL scripts on an upgraded system, you can reset data. Back up before you proceed.

If Quartz is not upgraded, the server can fail at startup.

This exception is generated in catalina.log:

Missing Quartz library database error

Step 1: Change default passwords

For production, change the default passwords in the SQL scripts.

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

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

2. Update these scripts for your user, password, database, and other required values:

   • create_jcr_mysql.sql

   • create_quartz_mysql.sql

   • create_repository_mysql.sql

   • pentaho_mart_mysql.sql

3. Save and close the files.

Step 2: Run SQL scripts

When upgrading from older versions (including 10.2.0.0 GA), initialize a new Quartz database. This creates the Quartz 2.x tables.

You might need administrator permissions 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 a MySQL/MariaDB prompt or MySQL Workbench. Then run the 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:

   • Quartz scheduler tables prefixed with QRTZ6_

   • Jackrabbit tables

   • Repository tables

   • (Optional) Operations Mart tables

   Any existing QRTZ5_ tables are retained. If you start the server now, the schedule is empty.

5. If you want to retain existing schedules, open migrate_old_quartz_data_mysql.sql.

6. Update the migration script as needed. Then save it.

7. Run:

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

The original QRTZ5_ tables are retained. Delete them only after you confirm migration success.

Step 3: Verify MySQL or MariaDB initialization

Unless you changed it in Step 1, the default password is password.

1. Open MySQL Workbench.

2. Log in as hibuser.

3. Under Schemas, expand hibernate > Tables. Verify you see tables.

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

5. Log in as jcr_user.

6. Under Schemas, expand jackrabbit > Tables. Verify tables.

   Jackrabbit tables might not appear until the first server start.

7. Log in as pentaho_user.

8. Under Schemas, expand quartz > Tables. Verify tables.

9. Exit MySQL Workbench.

Configure the MySQL or MariaDB Pentaho Repository database

Configure Quartz, Hibernate, Jackrabbit, and Operations Mart.

The examples assume MySQL on port 3306 with default passwords.

If your ports or passwords differ, update the examples. Match your environment values.

Step 1: Set up Quartz on MySQL or MariaDB

Quartz stores scheduler event 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

3. Save the file.

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>

3. Save and close the file.

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

5. Change the driver class:

   From:

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

   To:

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

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.

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.

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 (workspaces)

<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 (versioning)

<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

Configure Tomcat to connect to Hibernate, Jackrabbit, and Quartz.

Pentaho Server is configured for Tomcat by default. Modify context.xml only if you changed ports or passwords.

Step 1: Download the driver and apply it to the Pentaho Server

You need a JDBC driver for your repository database.

Due to licensing restrictions, you must download some drivers yourself.

1. Download a JDBC driver JAR from your database vendor or a third-party provider. See JDBC drivers reference.

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

3. If you want to keep the sample data, copy hsqldb-2.3.2.jar to pentaho-server/tomcat/lib.

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

Database connection information is stored in context.xml.

If your port, password, user, driver class, or host differs, update the examples. Match your environment values.

1. Use your database documentation to confirm:

   • JDBC driver class name

   • JDBC connection string format

2. Open server/pentaho-server/tomcat/webapps/pentaho/META-INF/context.xml.

3. Add resource entries if they do not exist already.

MySQL

<Resource name="jdbc/Hibernate" 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="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<Resource name="jdbc/Audit" 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="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1" />
<Resource name="jdbc/Quartz" 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="pentaho_user" password="password" testOnBorrow="true"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/quartz"
        validationQuery="select 1"/>
<Resource name="jdbc/PDI_Operations_Mart" 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="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/pentaho_operations_mart" 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="hibuser" password="password"
        driverClassName="com.mysql.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/live_logging_info" 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="hibuser" password="password" driverClassName="com.mysql.cj.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/hibernate"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleData" 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="pentaho_user" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        validationQuery="select 1"/>
<Resource name="jdbc/SampleDataAdmin" 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="pentaho_admin" password="password"
        driverClassName="org.hsqldb.jdbcDriver" url="jdbc:hsqldb:hsql://localhost/sampledata"
        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.cj.jdbc.Driver" url="jdbc:mysql://localhost:3306/jackrabbit"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>

MariaDB

1. Update usernames, passwords, hostnames, and ports to match your environment.

2. Comment out resource entries for databases you are not using.

3. Verify validationQuery="select 1" is correct for your database.

4. Save context.xml.

Next steps

Continue with Configure and start the Pentaho Server after manual installation.