Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.1.26, 5.5.57, 10.0.32, 10.2.9
Description
A user is seeing an 'Undo log record is too big.' error that only occurs if they update 2 mediumtext fields at once, and if the length of the existing values in each those fields happen to be between 3962 and 4030 characters long.
Some other interesting notes about this:
- The table definition has ROW_FORMAT=COMPACT, so it uses the Antelope format.
- The only indexes on the two mediumtext fields have prefix lengths of 255.
- The problem is repeatable on MySQL 5.5 and 5.7, and MariaDB 10.1 and 10.2, so I don't think it has been fixed in any released version of MySQL or MariaDB.
I have attached an SQL file that can be used to reproduce this issue.
Upstream bug report:
Attachments
Issue Links
- relates to
-
MDEV-11657 Cross-engine transaction metadata
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
Activity
For completeness the problem is reproducible also with one single field, upper limit should be of 8061 characters.
------------------
| LENGTH(a_str_18) |
------------------
| 63381 |
------------------
1 row in set (0.02 sec)
mysql> update test_tab set a_str_18=CONCAT(a_str_18,1);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE test_tab set a_str_18=LEFT(a_str_18,8000);
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_tab set a_str_18=CONCAT(a_str_18,1);
ERROR 1713 (HY000): Undo log record is too big.
I believe that the problem must be in the function dtuple_convert_big_rec() or in the related logic.
When a clustered index leaf page record is too long to fit in a page, InnoDB would start moving the longest columns to off-page storage.
The goal is to have room for at least 2 records in each leaf page. (For ROW_FORMAT=COMPRESSED, the minimum is 1 record for leaf pages and 2 for non-leaf pages.)
Some boundary condition must be off by a few bytes.
My guess was wrong, and this indeed is a bug with the InnoDB undo log format.
Here is how I debugged it. I took the attached test, prepended the line
--source include/have_innodb.inc |
saved into a .test file, executed with
./mtr --manual-gdb …
|
and then
break ha_innobase::update_row
|
run
|
break trx_undo_page_report_modify
|
c
|
fin
|
In this function, I started to single-step. The first invocation of trx_undo_page_report_modify() can ‘safely’ fail; there is a fall-back that retries with an empty undo log page.
On the second invocation, we can see the following:
(gdb) p first_free
|
$3 = 56
|
(gdb) p undo_page
|
$4 = (ib_page_t *) 0x7fffef1f4000 ""
|
(gdb) display ptr-$3-$4
|
After some single-stepping I set breakpoints to every return statement in the function.
There are two loops that are the problem:
for (i = 0; i < upd_get_n_fields(update) - extended; i++) { |
|
|
ulint pos = upd_get_nth_field(update, i)->field_no;
|
|
|
/* Write field number to undo log */ |
if (trx_undo_left(undo_page, ptr) < 5) { |
|
|
return(0); |
}
|
…
|
|
|
if (flen != UNIV_SQL_NULL) { |
if (trx_undo_left(undo_page, ptr) < flen) { |
|
|
return(0); |
}
|
|
|
ut_memcpy(ptr, field, flen);
|
ptr += flen;
|
}
|
}
|
The first one (above) logs every updated column.
The second loop (below) logs every indexed column, including columns that were already logged above:
for (col_no = 0; col_no < dict_table_get_n_cols(table); |
col_no++) {
|
|
|
const dict_col_t* col |
= dict_table_get_nth_col(table, col_no);
|
|
|
if (col->ord_part) { |
ulint pos;
|
|
|
/* Write field number to undo log */ |
if (trx_undo_left(undo_page, ptr) < 5 + 15) { |
|
|
return(0); |
}
|
|
|
pos = dict_index_get_nth_col_pos(index,
|
col_no);
|
ptr += mach_write_compressed(ptr, pos);
|
…
|
}
|
}
|
The space runs out during this loop, apparently at the very last user column. We abort at ptr-$3-$4=15933, right here in the second loop:
/* Write field number to undo log */ |
if (trx_undo_left(undo_page, ptr) < 5 + 15) { |
|
|
return(0); |
}
|
Because this second loop is writing the column number to the undo log record, the fix might be as simple as omitting those indexed columns that were logged as updated.
I will have to carefully check that such a change would be safe with the undo log parsing code.
Furthermore, in MariaDB 10.2 (and MySQL 5.7) the undo log format has changed due to indexed virtual columns (MDEV-5800).
Omitting the numbers of updated non-virtual columns must be determined to be safe also when indexed virtual columns are present (and affected by the update).
I pushed to bb-5.5-marko.
The merge upwards will require some effort. At least in 10.2 there are additional tests failing, because the undo log record size limitations are no longer being exceeded.
(In 10.2, also the code is slightly different due to spatial indexes and indexed virtual columns.)
This bug caused a permanent ‘corruption’ of data files that causes performance regression for accessing secondary indexes:
MDEV-14799 After UPDATE of indexed columns, old values will not be purged from secondary indexes
Some thoughts from marko: