Configure Bloomreach Experience Manager for MySQL

Overview

This guide describes how to configure Bloomreach Experience Manager (XM) to use MySQL as the content repository database and how to set up the Tomcat application container for this integration.

Prerequisites

  • Review the Configuring Databases documentation before proceeding.
  • Follow the Linux installation manual for initial setup.
  • Ensure you have a running MySQL server instance.
  • Confirm that you have administrative access to your Tomcat installation and the MySQL database.

Configuration Steps

Configure the JDBC Datasource

Set up the datasource for MySQL with the following parameters:

  • URL: 
    jdbc:mysql://DBHOST:DBPORT/DBNAME
  • Driver: 
    com.mysql.cj.jdbc.Driver
  • Validation Query: 
    SELECT 1

Update Repository Configuration

To use MySQL, update the repository configuration with MySQL-specific settings. The following values are required:

  • 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

Database User Roles and Privileges

Assign the following privileges to the MySQL user account used by Bloomreach Experience Manager:

CREATE
ALTER
DROP
INDEX
SELECT
INSERT
UPDATE
DELETE
LOCK TABLES

These privileges allow XM to initialize and operate the repository schema.

Important Notes

Info: Bloomreach Experience Manager requires the MySQL InnoDB storage engine.
InnoDB is the default engine for all supported MySQL versions.

Troubleshooting

Increase the Maximum Allowed Asset Size

Binary assets (such as PDF files and images) are stored as single packets in MySQL. The default value for max_allowed_packet (4 MB, or 1 MB before MySQL 5.6.6) may be insufficient for large assets. Increase the max_allowed_packet variable in your MySQL server configuration as needed, but avoid setting it higher than necessary to prevent performance degradation.

SSL Connection Warnings

When connecting to MySQL without server identity verification, you may see the following warning:

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.

For local development, disable SSL by adding useSSL=false to the datasource connection URL. When specifying multiple options, escape the & character in the URL. Example:

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

MySQL Connector 8.x and Metadata Issues

When using mysql-connector 8.x with a MySQL 5.x database, the connector fetches metadata from all authorized databases, not just the selected one. If another Jackrabbit database exists on the server, Jackrabbit may incorrectly assume required tables exist and skip table creation. This results in query failures.

To resolve this, add the following parameter to your JDBC URL:

  • For mysql-connector 8.0.17 and newer: 
    nullDatabaseMeansCurrent=true
  • For older versions: 
    nullCatalogMeansCurrent=true

Example:

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

Example Configurations

Example JNDI Resource (Tomcat conf/context.xml)

Define the datasource as follows:

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

Example repository.xml for Repository Consistency Checker

The Repository Consistency Checker requires a separate repository.xml configuration. 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-2.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>

Verification

  • Start the Tomcat server and verify that Bloomreach Experience Manager initializes without errors.
  • Check the MySQL database to confirm that the required tables are created.
  • Review the logs for any warnings or errors related to database connectivity or schema initialization.

If you encounter issues not covered here, consult the troubleshooting documentation or contact Bloomreach support.

Share Feedback
Page: /deploy/on-premise-deployment/configuration/configuring-brxm-for-mysql
Section: Deploy
Category *
Configure Bloomreach Experience Manager for MySQL | Bloomreach Content Documentation