Configure Hippo for MySQL

Introduction

Goal

Configure Hippo'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.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.

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.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="64"/>
    </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"/>
    </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"/>
    </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.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>