Bug #58912 InnoDB unnecessarily avoids update-in-place on column prefix indexes

• Submitted: 14 Dec 2010 9:15
• Modified: 11 Jan 2011 0:06
• Reporter: Marko Mäkelä
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any
• Tags: column prefix index, UPDATE

[14 Dec 2010 9:15] Marko Mäkelä

Description:
This is forked from Bug #55284. The function row_upd_changes_ord_field_binary() returns TRUE if the update vector changes a column that is covered by a prefix index, but does not change the column prefix. In this case, it should return FALSE, so that the record can be updated in place.

It is unclear if this bug used to be a mere performance issue, or if it could cause corruption. When I tested my fix of Bug #55284, this bug caused corruption.

How to repeat:
Set a breakpoint on row_upd_changes_ord_field_binary() and run the following:

CREATE TABLE bug55284 (a BLOB, b TEXT, PRIMARY KEY(a(1))) ENGINE=InnoDB;
INSERT INTO bug55284 VALUES(REPEAT('a',8000),REPEAT('b',8000));
UPDATE bug55284 SET a=REPEAT('a',7999);

Suggested fix:
Pass row_ext_t to row_upd_changes_ord_field_binary() and check whether the index prefixes really change before returning TRUE.

[21 Dec 2010 9:40] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127361

[21 Dec 2010 9:40] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/127362

[8 Jan 2011 15:08] Bugs System

Pushed into mysql-trunk 5.6.2 (revid:[e-mail]-20110108150732-8qygun7nuaqf9d3u) (version source revid:[e-mail]-20110108150646-44c9j7ck64ocey31) (merge vers: 5.6.2) (pib:24)

[8 Jan 2011 15:09] Bugs System

Pushed into mysql-5.5 5.5.9 (revid:[e-mail]-20110108150508-gpanhz48z8069qot) (version source revid:[e-mail]-20110108150048-b1y9m8xe72hay0ch) (merge vers: 5.5.9) (pib:24)

[8 Jan 2011 15:12] Bugs System

Pushed into mysql-5.1 5.1.55 (revid:[e-mail]-20110108145923-0tjqdpa5w3d6tuwn) (version source revid:[e-mail]-20110108145923-0tjqdpa5w3d6tuwn) (merge vers: 5.1.55) (pib:24)

[11 Jan 2011 0:06] John Russell

Adding to change log:

An UPDATE statement for an InnoDB table could be slower than
necessary if it changed a column covered by a prefix index, but did
not change the prefix portion of the value. The fix improves
performance for InnoDB 1.1 in MySQL 5.5 and higher, and the InnoDB
Plugin for MySQL 5.1.

[18 Jan 2011 14:13] Bjørn Munch

This introduced a compiler warning which breaks by sandbox build, Bug #59585.

[21 May 2012 22:48] John Russell

Changelog entry appears in 5.6.2, 5.5.9, 5.1.55.