MySQL Bugs: #52064: MySQL doing a table copy when creating index

Bug #52064	MySQL doing a table copy when creating index
Submitted:	15 Mar 2010 12:40	Modified:	17 Mar 2010 12:33
Reporter:	Marko Mäkelä	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.43-bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	innodb

Description:
MySQL is copying a table when asked to create an index using the InnoDB Plugin. This was reported at http://forums.innodb.com/read.php?3,1144,1144
This could be a duplicate of Bug #39833, but I can repeat this without specifying any character sets or collations.

How to repeat:
Set a breakpoint in compare_tables() on the statements that set
*need_copy_table= ALTER_TABLE_DATA_CHANGED;
Or set a breakpoint on ha_innobase::add_index() and note that it is not triggered when executing the following:

CREATE TABLE table_name ( id int(10) unsigned NOT NULL DEFAULT '0', col1 tinyint(3) unsigned NOT NULL, col2 smallint(5) unsigned NOT NULL, col3 varchar(255) NOT NULL, col4 tinyint(4) unsigned NOT NULL DEFAULT '0', col5 tinyint(7) NOT NULL, col6 mediumint(8) unsigned NOT NULL, col7 tinyint(1) unsigned NOT NULL DEFAULT '1', col8 tinyint(1) unsigned NOT NULL, col9 decimal(8,2) unsigned NOT NULL, col10 mediumint(8) unsigned DEFAULT NULL, col11 tinyint(3) unsigned DEFAULT NULL, col12 tinyint(1) NOT NULL DEFAULT '1', col13 date DEFAULT NULL, col14 tinyint(1) unsigned NOT NULL, col15 tinyint(1) unsigned NOT NULL, col16 tinyint(1) unsigned NOT NULL, col17 tinyint(1) unsigned NOT NULL, col18 tinyint(1) unsigned NOT NULL DEFAULT '0', col19 tinyint(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY(id,col1,col2)) engine=innodb;

alter table table_name add unique index (id);

Suggested fix:
Apparently, compare_tables() in sql/sql_table.cc should be less conservative about this case.

I confirm that:

(gdb) b ha_innobase::add_index  
Breakpoint 1 at 0x7cc257: file handler/handler0alter.cc, line 636.
(gdb) r --gdb

the breakpoint is not executed during the specified CREATE and ALTER.

This is MySQL 5.1-r3366 + InnoDB zip-r6794

Duplicate. See bug #50946: fast index creation still seems to copy the table.