Configure Bloomreach Experience Manager for MySQL

Introduction

Goal

Configure Bloomreach Experience Manager's content repository and the Tomcat application container to use a MySQL database.

Background

This page assumes you've read the parent page on configuring databases for Hippo, if you haven't done so already, please start there. That page in turn assumes you're following the Linux installation manual. This page will contain some basic database specific information, and example configurations for use in that scenario.

Configuration Changes

Datasource

URL

• jdbc:mysql://DBHOSTDBPORT/DBNAME

Driver

•  com.mysql.cj.jdbc.Driver

Validationquery

• SELECT 1

Repository

To use MySQL the repository configuration needs to be changed to use a couple of database specific changes. Below we provide complete example configuration for the most common uses.

It's needed to set the correct databaseType & classes for the FileSystems, Persistencemanagers, Journal & Datastore.

• databaseType

  • mysql

• FileSystem class

  • org.apache.jackrabbit.core.fs.db.DbFileSystem

• PersistenceManager class

  • org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager

• Journal class

  • org.apache.jackrabbit.core.journal.DatabaseJournal

• DataStore class

  • org.apache.jackrabbit.core.data.db.DbDataStore

Roles and Privileges

The following roles and or privileges should give the user enough privileges for Hippo to be able to populate the database and run:

CREATE
ALTER
DROP
INDEX
SELECT
INSERT
UPDATE
DELETE
LOCK TABLES

Notes

Troubleshooting

Increase the Maximum Allowed Asset Size

Assets, i.e. binary documents like pdf and images, are stored as a single packet. This means that max_allowed_packet should be large enough to accommodate these. The 4 MB default (1 MB before MySQL 5.6.6) may not be enough, so you can increase the max_allowed_packet variable of your MySQL server to allow for larger assets. We advise to not raise this further than necessary as performance will be reduced.

SSL Connection Warnings

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

When connecting to MySQL without server identity verification a warning is logged for every connection. For local development SSL can be disabled by adding useSSL=false to the datasource connection URL. When setting multiple options make sure the & is escaped in the datasource url. Example: 

jdbc:mysql://DBHOST:DBPORT/DBNAME?characterEncoding=utf8&useSSL=false

Example JNDI Resource:

Typically, this is defined in Tomcat's conf/context.xml.

    <Resource
                name="jdbc/repositoryDS" auth="Container" type="javax.sql.DataSource"
                maxTotal="20" maxIdle="10" initialSize="2" maxWaitMillis="10000"
                testWhileIdle="true" testOnBorrow="false" validationQuery="SELECT 1"
                timeBetweenEvictionRunsMillis="10000"
                minEvictableIdleTimeMillis="60000"
                username="DBUSER" password="DBPASS"
                driverClassName="com.mysql.cj.jdbc.Driver"
                url="jdbc:mysql://DBHOST:DBPORT/DBNAME?characterEncoding=utf8"/>

Example repository.xml:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE Repository
          PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 2.6//EN"
          "http://jackrabbit.apache.org/dtd/repository-2.6.dtd">

<Repository>

  <DataSources>
    <DataSource name="repositoryDS">
      <param name="driver" value="javax.naming.InitialContext"/>
      <param name="url" value="java:comp/env/jdbc/repositoryDS"/>
      <param name="databaseType" value="mysql"/>
    </DataSource>
  </DataSources>

  <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="dataSourceName" value="repositoryDS"/>
    <param name="schemaObjectPrefix" value="repository_"/>
  </FileSystem>

  <Security appName="Jackrabbit">
    <SecurityManager class="org.hippoecm.repository.security.SecurityManager"/>
    <AccessManager class="org.hippoecm.repository.security.HippoAccessManager"/>
    <LoginModule class="org.hippoecm.repository.security.HippoLoginModule"/>
  </Security>

  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default"/>

  <Workspace name="${wsp.name}">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
    </FileSystem>

    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_"/>
      <param name="externalBLOBs" value="true"/>
      <param name="consistencyCheck" value="false"/>
      <param name="consistencyFix" value="false"/>
      <param name="bundleCacheSize" value="256"/>
    </PersistenceManager>

    <SearchIndex class="org.hippoecm.repository.FacetedNavigationEngineImpl">
      <param name="indexingConfiguration" value="indexing_configuration.xml"/>
      <param name="indexingConfigurationClass" value="org.hippoecm.repository.query.lucene.ServicingIndexingConfigurationImpl"/>
      <param name="path" value="${wsp.home}/index"/>
      <param name="useSimpleFSDirectory" value="true"/>
      <param name="useCompoundFile" value="true"/>
      <param name="minMergeDocs" value="100"/>
      <param name="volatileIdleTime" value="10"/>
      <param name="maxMergeDocs" value="100000"/>
      <param name="mergeFactor" value="5"/>
      <param name="maxFieldLength" value="10000"/>
      <param name="bufferSize" value="1000"/>
      <param name="cacheSize" value="1000"/>
      <param name="onWorkspaceInconsistency" value="log"/>
      <param name="forceConsistencyCheck" value="false"/>
      <param name="enableConsistencyCheck" value="false"/>
      <param name="autoRepair" value="true"/>
      <param name="analyzer" value="org.hippoecm.repository.query.lucene.StandardHippoAnalyzer"/>
      <param name="queryClass" value="org.apache.jackrabbit.core.query.QueryImpl"/>
      <param name="respectDocumentOrder" value="false"/>
      <param name="resultFetchSize" value="1000"/>
      <param name="extractorTimeout" value="100"/>
      <param name="extractorBackLogSize" value="100"/>
      <param name="excerptProviderClass" value="org.apache.jackrabbit.core.query.lucene.DefaultHTMLExcerpt"/>
      <param name="supportSimilarityOnStrings" value="true"/>
      <param name="supportSimilarityOnBinaries" value="false"/>
    </SearchIndex>

    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Workspace>

  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="version_"/>
    </FileSystem>

    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="schemaObjectPrefix" value="version_"/>
      <param name="externalBLOBs" value="true"/>
      <param name="consistencyCheck" value="false"/>
      <param name="consistencyFix" value="false"/>
      <param name="bundleCacheSize" value="64"/>
    </PersistenceManager>

    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Versioning>

  <Cluster>
    <Journal class="org.apache.jackrabbit.core.journal.DatabaseJournal">
      <param name="dataSourceName" value="repositoryDS"/>
      <param name="databaseType" value="mysql"/>
      <param name="schemaObjectPrefix" value="repository_"/>
      <param name="revision" value="${rep.home}/revision.log"/>
      <!-- clean up journal table -->
      <param name="janitorEnabled" value="true"/>
      <param name="janitorSleep" value="86400"/> <!-- a day in seconds -->
      <param name="janitorFirstRunHourOfDay" value="3"/>
    </Journal>
  </Cluster>

  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="dataSourceName" value="repositoryDS"/>
    <param name="minRecordLength" value="1024"/>
    <param name="maxConnections" value="5"/>
    <param name="copyWhenReading" value="true"/>
  </DataStore>

</Repository>

Repository.xml for Repository Consistency Checker:

The Repository Consistency Checker requires its own repository.xml configuration file. Below is an example for MySQL:

<?xml version="1.0"?>
<!DOCTYPE Repository
          PUBLIC "-//The Apache Software Foundation//DTD Jackrabbit 2.6//EN"
          "http://jackrabbit.apache.org/dtd/repository-1.6.dtd">

<Repository>

  <DataSources>
    <DataSource name="ds1">
      <param name="driver" value="com.mysql.cj.jdbc.Driver"/>
      <param name="url" value="jdbc:mysql://DBHOST:DBPORT/DBNAME"/>
      <param name="user" value="DBUSER"/>
      <param name="password" value="DBPASS"/>
      <param name="databaseType" value="mysql"/>
      <param name="validationQuery" value="select 1"/>
      <param name="maxPoolSize" value="10"/>
    </DataSource>
  </DataSources>

  <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
    <param name="dataSourceName" value="ds1"/>
    <param name="schemaObjectPrefix" value="repository_" />
  </FileSystem>

  <Security appName="Jackrabbit">
    <SecurityManager class="org.apache.jackrabbit.core.security.simple.SimpleSecurityManager"/>
    <AccessManager class="org.apache.jackrabbit.core.security.simple.SimpleAccessManager"/>
    <LoginModule class="org.apache.jackrabbit.core.security.simple.SimpleLoginModule"/>
  </Security>

  <DataStore class="org.apache.jackrabbit.core.data.db.DbDataStore">
    <param name="dataSourceName" value="ds1"/>
    <param name="minRecordLength" value="1024" />
    <param name="copyWhenReading" value="true" />
    <param name="tablePrefix" value="" />
    <param name="schemaObjectPrefix" value="" />
  </DataStore>

  <Workspaces rootPath="${rep.home}/workspaces" defaultWorkspace="default" maxIdleTime="2"/>

  <Workspace name="${wsp.name}">

    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_" />
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="${wsp.name}_" />
    </PersistenceManager>

  </Workspace>

  <Versioning rootPath="${rep.home}/version">
    <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="version_" />
    </FileSystem>
    <PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="version_" />
    </PersistenceManager>
    <ISMLocking class="org.apache.jackrabbit.core.state.FineGrainedISMLocking"/>
  </Versioning>

  <Cluster>
    <Journal class="org.apache.jackrabbit.core.journal.CleanOnCloseDatabaseJournal">
      <param name="dataSourceName" value="ds1"/>
      <param name="schemaObjectPrefix" value="repository_"/>
    </Journal>
  </Cluster>

</Repository>