MySQL Bugs: #52742: When does ALTER TABLE require a table copy

Bug #52742	When does ALTER TABLE require a table copy
Submitted:	11 Apr 2010 0:40	Modified:	1 May 2015 13:17
Reporter:	Mark Callaghan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	alter, documentation, table

Description:
From the docs for ALTER TABLE and CREATE INDEX, I do not understand when a copy of the table is made.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/create-index.html

This bug fix adds another special case:
http://bugs.mysql.com/bug.php?id=49838

There was at least one other recent bug fix for InnoDB that 

The sections mention "table copy" and "online" and one section states that one implies the other.

MySQL makes this worse by ignoring the ONLINE option when it is not supported. Otherwise a clever user could determine what is ONLINE. I wish MySQL would stop ignoring syntax that it does not support.

"(if it is not possible to perform the CREATE INDEX operation online, then the ONLINE keyword is ignored)"

I also wonder whether there are two notions of _ONLINE_
1) ONLINE as accepted by the MySQL parser
2) "online" as used in the docs, for example
"ADD INDEX and DROP INDEX  operations are performed online when the indexes are on variable-width columns only."

This is a better example of 2), apparently "online" and ONLINE are different:
"It is also possible to rename MyISAM tables and columns online. However, you cannot use ONLINE with operations that add or drop columns or indexes of MyISAM tables."

My requests are:
* move NDB/Cluster specific things to one area
* if online and ONLINE are not the same then use something other than "online" to avoid confusion, "non-blocking" might help
* have one section that makes it clear when ALTER TABLE requires a table copy. This should also describe MyISAM versus InnoDB.

How to repeat:
read the docs

Mark,

This sentence in the bug report seems to be incomplete (missing the last part)?

"There was at least one other recent bug fix for InnoDB that"

Yes, that is incomplete and I don't remember what I was writing. There are other bugs for "alter table innodb fast index"
http://www.google.com/search?hl=en&client=firefox-a&hs=iTo&rls=org.mozilla%3Aen-US%3Aoffic...

I think a lot has changed here with MySQL 5.6 online alter table options for InnoDB. Is this bug still valid?

Good point. And the ONLINE/OFFLINE syntax doesn't even exist as of MySQL 5.7. Closing this without action.