MySQL Bugs: #45124: alter table causes full table rebuild which is not required

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ä	CPU Architecture:	Any

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

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?

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.

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.

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.

See also Bug #44030. MySQL should communicate column renames to InnoDB.

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.