Bug #18252 Disk space leak in updates of InnoDB BLOB rows in 5.0 and 5.1

• Submitted: 15 Mar 2006 10:36
• Modified: 19 Jun 2010 0:05
• Reporter: Marko Mäkelä
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.3 and later
• OS: Any (all)
• Assigned to: Marko Mäkelä
• CPU Architecture: Any

[15 Mar 2006 10:36] Marko Mäkelä

Description:
With the introduction of ROW_FORMAT=COMPACT, random access to the fields of a record became complicated. To simplify and to speed up the logic, we compute an array of offsets to the fields and pass this array to the access functions.

When a record is marked to contain an externally stored field (BLOB), InnoDB will not set the corresponding flag in the array of offsets. In some code paths, InnoDB trusts the flags in the array of offsets to be valid.

How to repeat:
Update BLOB columns. Observe that disk space will remain allocated to garbage BLOBs until DROP TABLE, OPTIMIZE TABLE or ALTER TABLE is executed.

Suggested fix:
Set the "external storage" flag in offsets when marking a column to be externally stored.

[15 Mar 2006 14:25] Heikki Tuuri

A more precise description of the leak problem: if you have an InnoDB table created in the default format of 5.0, that is, the compact format, and you update a row so that a long column is updated or the length of some column changes, then InnoDB will later fail to reclaim the BLOB storage space if the row is deleted.

DROP TABLE, ALTER TABLE, and TRUNCATE TABLE do free the BLOB space.

[15 Mar 2006 14:36] Heikki Tuuri

A correction: the bug also affects old format tables created with 4.1 and earlier.

Fix:

btr0cur.c:
"
        if (rec) {
                offsets = rec_get_offsets(rec, index, offsets,
                                        ULINT_UNDEFINED, &heap);

                lock_rec_restore_from_page_infimum(rec, page);
                rec_set_field_extern_bits(rec, index,
                                                ext_vect, n_ext_vect, mtr);

                if (!rec_get_deleted_flag(rec, rec_offs_comp(offsets))) {
                        /* The new inserted record owns its possible externally
                        stored fields */
                        btr_cur_unmark_extern_fields(rec, mtr, offsets);
                }

                btr_cur_compress_if_useful(cursor, mtr);

                err = DB_SUCCESS;
                goto return_after_reservations;
        }
"

Move the calculation of 'offsets' AFTER rec_set_field_extern_bits().

[5 Apr 2006 19:18] Elliot Murphy

Fixed in InnoDB snapshot368; fixes are in 5.0.20.

[11 Apr 2006 2:24] Paul DuBois

Noted in 5.0.20 changelog.

[5 May 2010 15:08] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 1:58] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 5:47] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:17] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:44] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:28] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 11:47] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:25] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:12] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)