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ä Email Updates:
Status: Closed Impact on me:
None 
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:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-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:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:17] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:44] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-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:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-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:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-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:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)