Bug #65865 In-place ALTER TABLE incorrectly handles prefix keys

• Submitted: 10 Jul 2012 15:28
• Modified: 17 Aug 2012 16:04
• Reporter: Dmitry Lenev
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.6.7-bzr
• OS: Any
• CPU Architecture: Any

[10 Jul 2012 15:28] Dmitry Lenev

Description:
Hello!

This bug report is based on the following mail to [e-mail]
http://lists.mysql.com/internals/38545.

New implementation of in-place ALTER TABLE in 5.6 always marks all existing prefix keys in the table being altered as being dropped and then re-created again. In cases when storage engine doesn't support in-place addition of keys this results in all almost all ALTERs on such table being carried using COPY algorithm. In cases when storage engine supports in-place creation of keys this probably results in extra unnecessary work being carried out by such an ALTER.

This is a regression from 5.5

See How-to-repeat for example.

How to repeat:
# Here is the script for mysqltest tool showing the problem.

CREATE TABLE t1 (a varchar(256) DEFAULT NULL, KEY a(a(50)));
INSERT INTO t1 VALUES ('Test');
--echo # The below statement should always be carried using fast/in-place
--echo # algorithm. So no rows should be copied (number of affected rows
--echo # should be zero). Unfortunately this is not true in 5.6.
--enable_info
ALTER TABLE t1 COMMENT='comment';
# affected rows: 1
# info: Records: 1  Duplicates: 0  Warnings: 0
--disable_info

[17 Aug 2012 16:04] Paul DuBois

Noted in 5.6.7, 5.7.0 changelogs.

In-place ALTER TABLE incorrectly handled indexes using key prefixes
by using a copy algorithm.