Configure Repository for MySQL Group Replication - Bloomreach Experience Manager - The Fast and Flexible Headless CMS

Configure Repository for MySQL Group Replication

Introduction

Goal

Set up MySQL Group Replication for Bloomreach Experience Manager.

Background

Apache Jackrabbit (on which Bloomreach Experience Manager Repository is based) tables have some limitations in their default structure that makes them incompatible with MySQL Group Replication. MySQL Group Replication requires that every table that is to be replicated by the group must have a defined primary key or a non-null unique key (see MySQL Group Replication Requirements). Additionally, in case an index includes a field of the Text field type, this results in an error in MySQL Group Replication. A few Jackrabbit tables have these cases. To be able to overcome related errors, it is needed to update the structure of these tables.

Update Jackrabbit tables

Run the following script to make Jackrabbit tables aligned with MySQL Group Replication:

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

Troubleshooting

  • If you encounter an error about the maximum length of the index key, most probably the length 2048 is too much for your MySQL configuration. You can set a new size to be able to fit your max size. For more information, please check MySQL official documentation for InnoDB limits.
  • A data truncation error like ‘Data too long for column …’ should not happen while running the script. But if it does, please contact our support.
Did you find this page helpful?
How could this documentation serve you better?
On this page
    Did you find this page helpful?
    How could this documentation serve you better?