In pxc-operator version 5.7, innodb_undo_tablespaces was set to 0 when creating an instance, and now needs to be adjusted to 3. How can this be done? Directly modifying the configuration prevents the instance from starting normally

MySQL & MariaDB Percona Operator for MySQL
1

In pxc-operator version 5.7, innodb_undo_tablespaces was set to 0 when creating an instance, and now needs to be adjusted to 3. How can this be done? Directly modifying the configuration prevents the instance from starting normally.

1 Like
2

MySQL 5.6 InnoDB has a 767-byte index key limit by default. utf8mb4 uses 4 bytes per character, so VARCHAR(255) = 1020 bytes, which exceeds the limit.

The tables were created correctly on the master with (191) prefix lengths, but Hibernate’s auto-generated ALTER TABLE ... ADD CONSTRAINT UNIQUE (col) DDL omits the prefix, causing replication to fail on the slave with:

  • Error 1071: Specified key was too long; max key length is 767 bytes
  • Error 1170: BLOB/TEXT column 'name' used in key specification without a key length

Fix 1 — Skip Failed Statements on the Slave

Since the unique keys already exist (with prefix lengths), the ALTER statements are redundant. Skip them on the slave:

-- On the SLAVE: skip the broken statement
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- Repeat for the second error
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

Fix 2 — Re-add Constraints with Prefix Lengths

If the constraints were dropped or need to be recreated:

-- UserGroup: varchar(255) utf8mb4 = 1020 bytes, exceeds 767-byte limit
-- Use prefix length of 191 (191 × 4 = 764 bytes < 767)
ALTER TABLE UserGroup
  ADD UNIQUE KEY UK_f6x7vpmw83jptft2toy0wsfk6 (groupName(191));

-- ISP: longtext requires a prefix length for indexing
ALTER TABLE ISP
  ADD UNIQUE KEY UK_tfe1hfqbgydnfe2siovm1i80k (name(191));

Fix 3 — Enable Large Prefix Support (Preferred Long-Term Fix)

Allow full-length keys by enabling innodb_large_prefix with ROW_FORMAT=DYNAMIC:

-- In my.cnf or at runtime on BOTH master and slave:
SET GLOBAL innodb_file_format    = Barracuda;
SET GLOBAL innodb_file_per_table = ON;
SET GLOBAL innodb_large_prefix   = ON;

-- Alter tables to use DYNAMIC row format (supports up to 3072-byte keys):
ALTER TABLE UserGroup ROW_FORMAT = DYNAMIC;
ALTER TABLE ISP       ROW_FORMAT = DYNAMIC;

-- Now the full varchar(255) utf8mb4 unique key works without a prefix:
ALTER TABLE UserGroup
  DROP KEY groupName,
  ADD UNIQUE KEY groupName (groupName);

-- For ISP, longtext ALWAYS requires a prefix — consider changing the column type:
ALTER TABLE ISP
  MODIFY name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE ISP
  DROP KEY name,
  ADD UNIQUE KEY name (name);

Permanent my.cnf Configuration

Add to [mysqld] section on both master and slave:

[mysqld]
innodb_file_format    = Barracuda
innodb_file_per_table = ON
innodb_large_prefix   = ON

1 Like
3

Hi wxc!

What version of the Operator are you using?

Also note that 5.7 reached EOL in October 2023. You are suggested to upgrade to at least 8.0 (which will also reach EOL April 2026)

One possible workaround is to backup and restore your setup into a new instance with innodb_undo_tablespaces = 3. Depending on the Operator version, there might be another workaround but I would suggest working on a migration

Regards