> 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/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/use-mysql-or-mariadb-as-your-repository-database-archive-installation.md).

# 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](#prerequisites)
* [Components](#components)
* [Initialize the repository database](#initialize-the-repository-database)
* [Configure the repository database](#configure-the-repository-database)
* [Perform Tomcat-specific connection tasks](#perform-tomcat-specific-connection-tasks)
* [Start the Pentaho Server](#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](/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/prepare-your-windows-environment-for-an-archive-install.md)
* [Prepare your Linux environment for an archive install](/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/prepare-your-linux-environment-for-an-archive-install.md)

{% hint style="warning" %}
MySQL Connector/J 8.0 cannot be used when configuring Tomcat with the JDBC driver JAR.
{% endhint %}

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

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

{% hint style="warning" %}
If your user, password, host, or port differs, update the examples to match your environment.
{% endhint %}

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

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

{% stepper %}
{% step %}

### Step 1: Change default passwords

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

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

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.
   {% endstep %}

{% step %}

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

{% hint style="info" %}
You may need administrator permissions to run these scripts 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 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.

{% hint style="info" %}
The original `QRTZ5_` data is retained. You can delete it after you confirm migration success.
{% endhint %}

{% hint style="info" %}
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](/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/prepare-your-windows-environment-for-an-archive-install.md) or [Prepare your Linux environment for an archive install](/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/prepare-your-linux-environment-for-an-archive-install.md).
{% endhint %}
{% endstep %}

{% step %}

### Step 3: Verify MySQL or MariaDB initialization

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

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

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>The Jackrabbit tables might not appear until after you start Pentaho for the first time.</p></div>
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.
{% endstep %}
{% endstepper %}

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

{% hint style="warning" %}
If your port or password differs, update the examples to match your environment.
{% endhint %}

{% stepper %}
{% step %}

### 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`:

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

3. Save the file.
   {% endstep %}

{% step %}

### 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. Find the driver class and change it from MySQL to MariaDB:

From:

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

To:

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

{% endstep %}

{% step %}

### 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`.
   {% endstep %}

{% step %}

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

<table data-header-hidden><thead><tr><th></th><th></th></tr></thead><tbody><tr><td>Item</td><td>Code Section</td></tr><tr><td>Repository</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    &#x3C;param name="driver" value="javax.naming.InitialContext"/>
    &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
    &#x3C;param name="schema" value="mysql"/>
    &#x3C;param name="schemaObjectPrefix" value="fs_repos_"/>
  &#x3C;/FileSystem>
</code></pre></td></tr><tr><td>DataStore</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    &#x3C;param name="driver" value="javax.naming.InitialContext"/>
    &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
    &#x3C;param name="databaseType" value="mysql"/>
    &#x3C;param name="minRecordLength" value="1024"/>
    &#x3C;param name="maxConnections" value="3"/>
    &#x3C;param name="copyWhenReading" value="true"/>
    &#x3C;param name="tablePrefix" value=""/>
    &#x3C;param name="schemaObjectPrefix" value="ds_repos_"/>
  &#x3C;/DataStore>
</code></pre></td></tr><tr><td>Workspaces</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      &#x3C;param name="driver" value="javax.naming.InitialContext"/>
      &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
      &#x3C;param name="schema" value="mysql"/>
      &#x3C;param name="schemaObjectPrefix" value="fs_ws_"/>
    &#x3C;/FileSystem>
</code></pre></td></tr><tr><td>PersistenceManager (1st part)</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      &#x3C;param name="driver" value="javax.naming.InitialContext"/>
      &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
      &#x3C;param name="schema" value="mysql"/>
      &#x3C;param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
    &#x3C;/PersistenceManager>
</code></pre></td></tr><tr><td>Versioning</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      &#x3C;param name="driver" value="javax.naming.InitialContext"/>
      &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
      &#x3C;param name="schema" value="mysql"/>
      &#x3C;param name="schemaObjectPrefix" value="fs_ver_"/>
    &#x3C;/FileSystem>
</code></pre></td></tr><tr><td>PersistenceManager (2nd part)</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      &#x3C;param name="driver" value="javax.naming.InitialContext"/>
      &#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
      &#x3C;param name="schema" value="mysql"/>
      &#x3C;param name="schemaObjectPrefix" value="pm_ver_"/>
    &#x3C;/PersistenceManager>
</code></pre></td></tr><tr><td>DatabaseJournal</td><td><pre class="language-xml"><code class="lang-xml">&#x3C;Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
	&#x3C;param name="revision" value="${rep.home}/revision.log"/>
	&#x3C;param name="driver" value="javax.naming.InitialContext"/>
	&#x3C;param name="url" value="java:comp/env/jdbc/jackrabbit"/>
	&#x3C;param name="schema" value="mysql"/>
	&#x3C;param name="schemaObjectPrefix" value="J_C_"/>
	&#x3C;param name="janitorEnabled" value="true"/>
	&#x3C;param name="janitorSleep" value="86400"/>
	&#x3C;param name="janitorFirstRunHourOfDay" value="3"/>
     &#x3C;/Journal>
</code></pre></td></tr></tbody></table>
{% endstep %}
{% endstepper %}

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

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

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

{% hint style="info" %}
Due to licensing restrictions, Pentaho cannot redistribute some third-party database drivers. You must download and install these files yourself.
{% endhint %}

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

{% hint style="warning" %}
If you have a different port, password, user, driver class, or IP address, update the examples to match your environment.
{% endhint %}

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:

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

   ```xml
   <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](/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/starting-the-pentaho-server-after-an-archive-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:

```
GET https://docs.pentaho.com/install/legacy-redirects/archive-installation-of-pentaho-legacy-pages/archive-installation-process-archived-pages/use-mysql-or-mariadb-as-your-repository-database-archive-installation.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
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.