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:
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: Marko Mäkelä CPU Architecture:Any

[22 Nov 2010 2:52] Jimmy Yang
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
[22 Nov 2010 4:32] Jimmy Yang
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.
[22 Nov 2010 11:36] Jimmy Yang
The alter table rename table name issue is discussed in bug #58215
[16 Aug 2011 19:42] Sujoy Gupta
It appears that this bug requires changes from the MySQL side. Is that correct?
[16 Aug 2011 19:47] Alexy Khrabrov
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?
[8 Dec 2011 8:01] manu patet

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!

[9 Mar 2012 12:43] Simon Mudd
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.
[12 Apr 2012 9:29] Marko Mäkelä
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/
[28 Aug 2012 14:54] Paul DuBois
Fixed in 5.6.6: