Bug #114015 'Undo log record is to big error' when updating column
Submitted: 16 Feb 10:14 Modified: 16 Feb 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

[16 Feb 10:14] Wilhelm Laschinger
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;
[16 Feb 10:29] Wilhelm Laschinger
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;
[16 Feb 11:24] MySQL Verification Team
Hello Wilhelm Laschinger,

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

regards,
Umesh
[20 Feb 14:58] huahua xu
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.
[20 Feb 15:02] huahua xu
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.