MySQL Bugs: #114015: 'Undo log record is to big error' when updating column

Bug #114015	'Undo log record is to big error' when updating column
Submitted:	16 Feb 2024 10:14	Modified:	16 Feb 2024 11:24
Reporter:	Wilhelm Laschinger	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	8.0.32, 8.036, 8.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	longtext, unique key, UPDATE

Description:
We observed an unusual behavior in a table containing multiple longtext columns and unique keys. Each row has a unique_group column to enforce uniqueness for specific fields. Additionally, there are separate columns for different languages such as 'de', 'en', and 'cz'.

In certain combinations of values, an 'Undo log record is too big' error occurs when attempting to update a column. I have provided an example to replicate the issue. It appears that the number of characters currently stored in the columns before the update, as well as the number of characters in the update value, are critical factors. Modifying the update to include a few more or less characters results in a successful update. While updating the value with a specific range of characters results in an error.

How to repeat:
-- create i18n table
CREATE TABLE `i18n` (
  `i18n_id` bigint NOT NULL,
  `unique_group` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `de` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `en` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cz` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- add uniques
ALTER TABLE `i18n`
  ADD PRIMARY KEY (`i18n_id`),
  ADD UNIQUE KEY `unique_group_de` (`unique_group`(30),`de`(100)),
  ADD UNIQUE KEY `unique_group_en` (`unique_group`(30),`en`(100)),
  ADD UNIQUE KEY `unique_group_cz` (`unique_group`(30),`cz`(100));

-- add auto increment
ALTER TABLE `i18n`
  MODIFY `i18n_id` bigint NOT NULL AUTO_INCREMENT;

-- dataset
INSERT INTO `i18n` (`i18n_id`, `unique_group`, `de`, `en`, `cz`) VALUES
(1, NULL, repeat('a', 4313), repeat('a', 3635), repeat('a', 5031));

-- error when en field is updated with char size between 3760 and 4313
-- lower limit 3760
update i18n set en=repeat('b', 3760) where i18n_id=1;
-- upper limit 4313
update i18n set en=repeat('b', 4313) where i18n_id=1;

The problem also occurs, when I define the table with varchar columns:

-- create i18n table
CREATE TABLE `i18n` (
  `i18n_id` bigint NOT NULL,
  `unique_group` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `de` varchar(5100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `en` varchar(5100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `cz` varchar(5100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Hello Wilhelm Laschinger,

Thank you for the report and feedback.
IMHO this is duplicate of Bug #113952, please see Bug #113952.

regards,
Umesh

The size of the undo log record exceeds the page size of the undo tablespace, because the undo log record contains both old value and index value of the blob field stored locally on the page.

I try to fix the bug by enhancing the conditional restriction that lob field can be stored locally on the page.

Attachment: undo_log_record_too_big_for_lob_stored_locally.patch (application/octet-stream, text), 777 bytes.