> For the complete documentation index, see [llms.txt](https://docs.pentaho.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.pentaho.com/install/legacy-redirects/manual-installation-process-archived-content/use-mysql-or-mariadb-as-your-repository-database-manual-installation.md).

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

{% hint style="info" %}
This page is kept for existing links. Go to [Manual installation process](/install/legacy-redirects/manual-installation-process.md#use-mysql-or-mariadb-as-your-repository-database-manual-installation).
{% endhint %}

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](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-windows-environment-for-a-manual-installation.md)
* [Prepare your Linux environment for a manual installation](/install/legacy-redirects/manual-installation-process-archived-content/prepare-your-linux-environment-for-a-manual-installation.md)

#### 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.

{% hint style="warning" %}
Use the ASCII character set when you run these scripts.\
Do not use UTF-8. Script failures can occur due to string-length limits.
{% endhint %}

{% hint style="warning" %}
If you use different ports, users, or passwords, update the examples.\
Match your environment values.
{% endhint %}

{% hint style="warning" %}
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.
{% endhint %}

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.

{% hint style="info" %}
If you are evaluating Pentaho, you might skip this step.
{% endhint %}

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.

{% hint style="info" %}
You might need administrator permissions on the host OS.
{% endhint %}

1. Back up your data. See [Back up your existing Pentaho products and install Pentaho 11.0](/install/legacy-redirects/back-up-your-pentaho-products-and-install-pentaho-9.1-pentaho-upgrade.md).
2. If the Pentaho Server is running, stop it.\
   See [Stop and start the Pentaho Server and repository](/install/legacy-redirects/tasks-to-be-performed-by-an-it-administrator-legacy-redirects/configure-the-pentaho-server-legacy-pages/stop-and-start-the-pentaho-server-and-repository.md).
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`

{% hint style="info" %}
The original `QRTZ5_` tables are retained.\
Delete them only after you confirm migration success.
{% endhint %}

#### Step 3: Verify MySQL or MariaDB initialization

{% hint style="info" %}
Unless you changed it in Step 1, the default password is `password`.
{% endhint %}

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.

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>Jackrabbit tables might not appear until the first server start.</p></div>
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.

{% hint style="warning" %}
If your ports or passwords differ, update the examples.\
Match your environment values.
{% endhint %}

#### 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.

{% hint style="info" %}
Hibernate also stores the audit logs used by Operations Mart.
{% endhint %}

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:

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

   To:

   ```xml
   <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**

```xml
<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**

```xml
<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**

```xml
<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)**

```xml
<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**

```xml
<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)**

```xml
<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**

```xml
<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.

{% hint style="info" %}
Pentaho Server is configured for Tomcat by default.\
Modify `context.xml` only if you changed ports or passwords.
{% endhint %}

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

You need a JDBC driver for your repository database.

{% hint style="info" %}
Due to licensing restrictions, you must download some drivers yourself.
{% endhint %}

1. Download a JDBC driver JAR from your database vendor or a third-party provider.\
   See [JDBC drivers reference](/install/jdbc-drivers-reference.md).
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`](https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_3/) 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`.

{% hint style="warning" %}
If your port, password, user, driver class, or host differs, update the examples.\
Match your environment values.
{% endhint %}

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.

{% tabs %}
{% tab title="MySQL" %}

```xml
<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"/>
```

{% endtab %}

{% tab title="MariaDB" %}

```xml
<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="org.mariadb.jdbc.Driver" url="jdbc:mariadb://localhost:3306/hibernate"
        validationQuery="select 1" jdbcInterceptors="ConnectionState" defaultAutoCommit="true"/>
<!-- Repeat the same pattern for Audit, Quartz, and jackrabbit. -->
```

{% endtab %}
{% endtabs %}

4. Update usernames, passwords, hostnames, and ports to match your environment.
5. Comment out resource entries for databases you are not using.
6. Verify `validationQuery="select 1"` is correct for your database.
7. Save `context.xml`.

### Next steps

Continue with [Configure and start the Pentaho Server after manual installation](/install/legacy-redirects/manual-installation-process-archived-content/configure-and-start-the-pentaho-server-after-manual-installation.md).


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.pentaho.com/install/legacy-redirects/manual-installation-process-archived-content/use-mysql-or-mariadb-as-your-repository-database-manual-installation.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.