Use MySQL as Your Repository Database (Manual Installation)

Step 1: Change Default Passwords

For your production server, we highly recommend that you change the default passwords in the following SQL script files to make the databases more secure.

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

To change the passwords, go to the pentaho/server/pentaho-server/data/mysql5 directory and use any text editor to change the passwords in these SQL scripts:

• create_jcr_mysql.sql
• create_quartz_mysql.sql
• create_repository_mysql.sql
• pentaho_mart_mysql.sql

Step 2: Run SQL Scripts

You will need to run the SQL scripts in the table below.

These scripts require administrator permissions on the server in order to run them.

If you have a different port or different password, make sure that you change the password and port numbers in these examples to match the ones in your configuration.

Run these scripts from the MySQL Command Prompt window or from MySQL Workbench.

Step 3: Verify MySQL Initialization

After you run the scripts, perform the following steps to verify that databases and user roles have been created:

1. Open the MySQL Workbench tool. MySQL Workbench is freely available at the MySQL development site.
2. Log in as hibuser.
3. Make sure that the Quartz, Jackrabbit (JCR), Hibernate, and Pentaho Operations Mart databases are present.
4. Exit from the MySQL Workbench.

Step 1: Set Up Quartz on MySQL Pentaho Repository Database

Event information, such as scheduled reports, is stored in the Quartz JobStore. During the installation process, you must indicate where the JobStore is located, by modifying the quartz.properties file.

1. Open the pentaho/server/pentaho-server/pentaho-solutions/system/quartz/quartz.properties file in any text editor.
2. Locate the #_replace_jobstore_properties section and set the org.quartz.jobStore.driverDelegateClass as shown here.

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

3. Locate the # Configure Datasources section and set the org.quartz.dataSource.myDS.jndiURL equal to Quartz, like this.

   org.quartz.dataSource.myDS.jndiURL = Quartz
   

4. Save the file and close the text editor.

Step 2: Set Hibernate Settings for MySQL

Modify the Hibernate settings file to specify where Pentaho should find the Pentaho Repository’s Hibernate config file.

The Hibernate config file specifies driver and connection information, as well as dialects and how to handle connection closes and timeouts.

The files in this section are located in the pentaho/server/pentaho-server/pentaho-solutions/system/hibernate directory.

1. Open the hibernate-settings.xml file in a text editor. Find the <config-file> tags and change postgresql.hibernate.cfg.xml to mysql5.hibernate.cfg.xml as shown.
   From:

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

   To:

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

2. Save and close the file.
3. Open the mysql5.hibernate.cfg.xml file in a text editor.
4. Make sure that the password and port number match the ones you specified in your configuration. Make changes if necessary, then save and close the file.

Step 3: Replace Default Version of Audit Log File with MySQL Version

Since you are using MySQL to host the Pentaho Repository, you need to replace the audit_sql.xml file with one that is configured for MySQL.

1. Locate the pentaho-solutions/system/dialects/mysql5/audit_sql.xml file.
2. Copy it into the pentaho-solutions/system directory.

Step 4: Modify Jackrabbit Repository Information for MySQL

There are parts of code that you will need to alter in order to change the default JCR repository to MySQL.

1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/jackrabbit and open the repository.xml file with any text editor.
2. Following the table below, locate and change the code so that the MySQL lines are not commented out, but the PostgreSQL, MS SQL Server, and Oracle lines are commented out.

If you have a different port or different password, make sure that you change the password and port number in these examples to match those in your configuration.

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
     <param name="driver" value="com.mysql.jdbc.Driver"/>
     <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
</FileSystem>

<DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
</DataStore>

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
      <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

<FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="driver" value="com.mysql.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
</FileSystem>

<PersistenceManager class="org.apache.jackrabbit.core.persistence.bundle.MySqlPersistenceManager">
      <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
…
      <param name="schemaObjectPrefix" value="${wsp.name}_pm_ws_"/>
</PersistenceManager>

<Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
    <param name="revision" value="${rep.home}/revision.log"/>
    <param name="driver" value="com.mysql.jdbc.Driver"/>
    <param name="url" value="jdbc:mysql://localhost:3306/jackrabbit"/>
    <param name="user" value="jcr_user"/>
    <param name="password" value="password"/>
    <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>

Step 1: Download Drivers and Apply to the Pentaho Server

Step 2: Modify JDBC Connection Information in the Tomcat context.xml File

Database connection and network information, such as the username, password, driver class information, IP address or domain name, and port numbers for your Pentaho Repository database are stored in the context.xml file. Modify this file to reflect the database connection and network information to reflect your operating environment. You also modify the values for the validationQuery parameters in this file if you have chosen to use an Pentaho Repository database other than MySQL.

If you have a different port, password, user, driver class information, or IP address, make sure that you change the password and port number in these examples to match those in your configuration environment.

1. Consult your database documentation to determine the JDBC class name and connection string for your Pentaho Repository database.
2. Navigate to the pentaho-server/tomcat/webapps folder and use a zip extraction utility (such as 7-Zip, Winzip, or Archive) to view the contents of the pentaho.war file.
3. Open the context.xml file with any text editor.
4. Add the following code to the file if it does not already exist.

   <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.apache.tomcat.jdbc.pool.DataSourceFactory" 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.apache.tomcat.jdbc.pool.DataSourceFactory" 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.apache.tomcat.jdbc.pool.DataSourceFactory" 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.apache.tomcat.jdbc.pool.DataSourceFactory" 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.apache.tomcat.jdbc.pool.DataSourceFactory" 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.apache.commons.dbcp.BasicDataSourceFactory" 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"/>
   

5. Modify the username, password, driver class information, IP address (or domain name), and port numbers so they reflect the correct values for your environment.
6. Make sure that the validationQuery variable for your database is set to this: validationQuery="select 1"
7. Save the context.xml file, then close it.
8. The zip extraction utility that you used might show a prompt which asks whether you would like to update the file in the pentaho.war archive. Confirm that you would like to update the file.
9. Clear the tomcat/work and tomcat/temp directories.