MySQL Bugs: #116719: An overflow page is not used if the value of a column is 788 bytes or less

Bug #116719	An overflow page is not used if the value of a column is 788 bytes or less
Submitted:	20 Nov 2024 6:47	Modified:	21 Nov 2024 15:39
Reporter:	Xizhe Zhang (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When describing "COMPACT Row Format" in the document (https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html), there is a sentence "If the value of a column is 768 bytes or less, an overflow page is not used". I think it needs to be adjusted to "If the value of a column is 788 bytes or less, an overflow page is not used".

Because the BLOB pointer itself occupies 20 bytes, the total is 788 bytes after adding the 768-byte data prefix. When selecting external storage fields in the code, fields with a length less than or equal to 788 bytes will be skipped, because there will be no space saving.

big_rec_t *dtuple_convert_big_rec(dict_index_t *index, upd_t *upd,
                                  dtuple_t *entry) {
  ...
  if (!dict_table_has_atomic_blobs(index->table)) {
    /* local_len=20+768=788, for REDUNDANT and COMPACT row format */
    local_len = BTR_EXTERN_FIELD_REF_SIZE + DICT_ANTELOPE_MAX_INDEX_COL_LEN;
  }
  ...
  while(...) { // find the longest field in one loop
    for(...) { // iterate through all fields
      ...
      /* "dfield_get_len(dfield) <= local_len" means 
         "Fields with a length less than or equal to 788 Bytes
          will not be stored externally" */
      if (ifield->fixed_len || dfield_is_null(dfield) ||
          dfield_is_ext(dfield) || dfield_get_len(dfield) <= local_len ||
          dfield_get_len(dfield) <= BTR_EXTERN_LOCAL_STORED_MAX_SIZE) {
        goto skip_field;
      }
      ...
    }
  }
}

How to repeat:
I provided an example to show that fields with a length of 788 bytes or less will not be stored externally.

-- Create a table
create table t1 (
    c1 TEXT,
    c2 TEXT,
    c3 TEXT,
    c4 TEXT,
    c5 TEXT,
    c6 TEXT,
    c7 TEXT,
    c8 TEXT,
    c9 TEXT,
    c10 TEXT,
    c11 TEXT
) engine=innodb, row_format=compact;

-- This row can be inserted
insert into t1 values(
    repeat('a', 198),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 787)
);
Query OK, 1 row affected (0.00 sec)

-- This row can't be inserted, even though it is only 1 Byte longer than the previous row
insert into t1 values(
    repeat('a', 198),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788),
    repeat('a', 788)
);
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

Hello Xizhe Zhang,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
The documentation under COMPACT Row Format Storage Characteristics on the linked page clearly states:
"The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column."

This is not a bug. Thank you for the report.

Hello, Edward Gilmore:

There is nothing wrong with the sentence you mentioned. I think the problematic part is "If the value of a column is 768 bytes or less, an overflow page is not used", which is the first sentence of the third paragraph of the "COMPACT Row Format" section.

From the code, we can see that the condition "dfield_get_len(dfield) <= local_len" means, "If the value of a column is 788 bytes or less, an overflow page is not used". For example, if I have a field with a length of 780 bytes (bigger than 768), it will never be selected to be stored externally, because this will result in no space being saved, and even 8 bytes more being used.