MySQL Bugs: #58368: Support Fast ALTER TABLE renaming column

Bug #58368	Support Fast ALTER TABLE renaming column
Submitted:	22 Nov 2010 2:52	Modified:	28 Aug 2012 14:54
Reporter:	Jimmy Yang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.5	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any

Description:
The current Fast ALTER TABLE renaming column has its problem for InnoDB (and possible other storage engine) as described in bug #47621 and again reported by #57832:

The issues is "that the MySQL and InnoDB data dictionaries will become out of sync when renaming columns. Columns will be only renamed in the MySQL data dictionary (.frm file), not in the InnoDB system tables. Fixing that might require an API change in MySQL 5.1."

The main issue is that "although mysql passes info that sets the
FIELD_IS_RENAMED bit for the column to be renamed, it does not pass the new column name. And we will not know the new column name until we open the table again after alter table operation completes."

"To fix this correctly, mysql should pass the complete info during alter table rename to storage, so we can update the system table."

How to repeat:
alter table rename column

Another alter command needs to be enhanced is alter table rename (table name) as described in 58368.

Here, if we have a combination of alter table rename table and another alter table command like:

alter table child77
drop column location_id,
rename to new_child77;

InnoDB'd like to know there is a rename (from old table name to new table name) happpening. Currently, it just sees a rename from old table name to temp table, and then a rename from temp table to new table name, the same procedure as alter table drop column. So it does not aware an old table name is renamed to new table name.

The alter table rename table name issue is discussed in bug #58215

It appears that this bug requires changes from the MySQL side. Is that correct?

This is in fact a critical bug.  On large tables, it makes renames infeasible in production, while renaming is the most common case of refactoring, an important part of agile development.

What is the status of this bug as of now?

vote++

Please let us know if there's any light ahead for this feature. We are banking on this one to cut 12-14 hours of outage down to a minute!

Thanks
Manu

Also frustrated by the lack of progress on this and have poked Oracle directly.  The issue has been known to them for some time and 5.5.21 is the current version.  Bitten again today by this.

There is some help in sight. WL#5545 implements DROP FOREIGN KEY and column renaming in-place. It is work-in-progress, waiting for final testing and review. A preview was included in the 5.6 April 2012 Labs release: http://blogs.innodb.com/wp/2012/04/labs-release-online-ddl/

Fixed in 5.6.6:
http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html