Configure Repository for MySQL Group Replication

Overview

This guide explains how to configure the Bloomreach Content repository for compatibility with MySQL Group Replication.

When to Use

Use this procedure when you need to run Bloomreach Content with a MySQL database configured for Group Replication.

Background

The Bloomreach Content repository is based on Apache Jackrabbit. By default, some Jackrabbit tables do not meet MySQL Group Replication requirements. Specifically:

MySQL Group Replication requires each replicated table to have a primary key or a non-null unique key. For details, see the MySQL Group Replication Requirements.
MySQL Group Replication does not support indexes that include columns of the TEXT type.

Several Jackrabbit tables use a TEXT column in indexed fields or lack a unique key. To enable Group Replication, you must update these table definitions.

Prerequisites

A running MySQL instance configured for Group Replication.
Administrative access to the MySQL database used by Bloomreach Content.
A backup of your repository database.

Update Jackrabbit Tables

To align the Jackrabbit tables with MySQL Group Replication requirements, execute the following SQL script against your repository database:

-- 1. Change the type of FSENTRY_PATH field from Text to Varchar. 
-- 1.1. Update DEFAULT_FSENTRY table 
DROP INDEX DEFAULT_FSENTRY_IDX ON DEFAULT_FSENTRY;
ALTER TABLE DEFAULT_FSENTRY MODIFY FSENTRY_PATH VARCHAR(2048);
CREATE UNIQUE INDEX DEFAULT_FSENTRY_IDX on DEFAULT_FSENTRY (FSENTRY_PATH, FSENTRY_NAME);

-- 1.2. Update REPOSITORY_FSENTRY table 
DROP INDEX REPOSITORY_FSENTRY_IDX ON REPOSITORY_FSENTRY;
ALTER TABLE REPOSITORY_FSENTRY MODIFY FSENTRY_PATH VARCHAR(2048);
CREATE UNIQUE INDEX REPOSITORY_FSENTRY_IDX on REPOSITORY_FSENTRY (FSENTRY_PATH, FSENTRY_NAME);

-- 1.3. Update VERSION_FSENTRY table 
DROP INDEX VERSION_FSENTRY_IDX ON VERSION_FSENTRY;
ALTER TABLE VERSION_FSENTRY MODIFY FSENTRY_PATH VARCHAR(2048);
CREATE UNIQUE INDEX VERSION_FSENTRY_IDX on VERSION_FSENTRY (FSENTRY_PATH, FSENTRY_NAME);

-- 2. Create an unique index for REPOSITORY_LOCAL_REVISIONS table
CREATE UNIQUE INDEX REPOSITORY_LOCAL_REVISIONS_IDX on REPOSITORY_LOCAL_REVISIONS (JOURNAL_ID, REVISION_ID);

Notes:

The script modifies the FSENTRY_PATH column from TEXT to VARCHAR(2048) and recreates the relevant indexes as unique indexes.
It also adds a unique index to the REPOSITORY_LOCAL_REVISIONS table.

Verification

After running the script:

Confirm that all SQL statements complete without errors.
Verify that the affected tables (DEFAULT_FSENTRY, REPOSITORY_FSENTRY, VERSION_FSENTRY, REPOSITORY_LOCAL_REVISIONS) have the updated column types and unique indexes.
Start Bloomreach Content and check for repository startup errors.

Troubleshooting

Index Key Length Error

If you receive an error about the maximum length of the index key, the VARCHAR(2048) length may exceed your MySQL configuration limits. Adjust the column size to fit your environment. For details, see the InnoDB limits documentation.
Data Truncation Error

If you encounter a data truncation error such as Data too long for column ... while running the script, this is unexpected. Contact Bloomreach Support for assistance.