MySQL Bugs: #38981: alter table on InnoDB tables with blob datatype columns is impossible to execute

Bug #38981	alter table on InnoDB tables with blob datatype columns is impossible to execute
Submitted:	23 Aug 2008 11:41	Modified:	23 Aug 2008 16:00
Reporter:	Claudio Grillo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:		OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, Blob Datatype, create index

Description:
During execution of alter table  on InnoDB tables with blob columns, it seems that all the blob data are being copied to a temp table, since the command never returns. Create index on others columns (not the blob column) also does not return.

How to repeat:
create a InnoDb table with a longblob column.
Insert a lot of binary data.
alter the table to create a new integer column or
create index on a non-blob column.

This is not a bug.  As documented, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html, in most cases, ALTER TABLE adds an index by making a temporary copy of the original table, with the new index.  This includes BLOB column values because part (or all) of the BLOB data is stored within index pages in InnoDB.

Note that the new InnoDB Plugin for MySQL 5.1 implements "Fast Index Creation", which does not make a copy of the table to add or drop an index.   See http://www.innodb.com/innodb_plugin/features/ for more information.