Bug #45124 alter table causes full table rebuild which is not required
Submitted: 27 May 2009 9:34 Modified: 21 Dec 2012 20:56
Reporter: Alexey Polyakov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S4 (Feature request)
Version:MySQL 5.1.34, Plugin 1.0.3 OS:Any
Assigned to: Marko Mäkelä
Triage: Triaged: D5 (Feature request) / R4 (High) / E4 (High)

[27 May 2009 9:34] Alexey Polyakov
Description:
When altering column:
`check_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
into 
`check_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
 the database does a full table rebuild. 
But no data gets changed, it's only the data definition.

How to repeat:
1) Create a table with a column from my example
2) Insert enough data so rebuilding a table takes some time
3) Look at query status or temporary files in data directory
[27 May 2009 18:43] Sveta Smirnova
Thank you for the report.

According to http://www.innodb.com/doc/innodb_plugin-1.0/innodb-file-format.html "Note that the ALTER TABLE command will, in many cases, cause a table to be recreated and thereby change its properties. The special case of adding or dropping indexes without rebuilding the table..." this is not special case of adding or dropping indexes. Why do you think this is a bug?
[28 May 2009 10:29] Alexey Polyakov
It's not a bug, I should have chosen S5 severity for this report.

From manual (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html):
 In some cases, no temporary table is necessary:
 * Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents.
[28 May 2009 11:17] Sveta Smirnova
Thank you for the feedback.

Verified as feature request.

Regarding to the quote from the manual please note this can be done differently by differnt storage engines.
[28 May 2009 13:41] Marko Mäkelä
The problem is that there does not appear to be an interface for communicating meta data changes to InnoDB in MySQL 5.1. You know, InnoDB has its own data dictionary, which is used for checking FOREIGN KEY constraints, among other things. See also Bug #44571.

This could possibly be addressed using the revised "smart ALTER TABLE" API that was introduced in MySQL 6.0.
[1 Jun 2009 12:23] Marko Mäkelä
See also Bug #44030. MySQL should communicate column renames to InnoDB.
[21 Dec 2012 20:56] John Russell
Added to changelog for 5.6.7: 

The new online DDL feature addressed long-standing bugs where ALTER
TABLE statements caused table rebuilds unnecessarily. This particular
bug applied to changing default values for TIMESTAMP columns.