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
Triage: Needs Triage: D5 (Feature request)

[11 Apr 2010 0:40] Mark Callaghan
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
[3 Jun 2010 14:49] Paul Dubois
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"
[10 Jun 2010 19:56] Mark Callaghan
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...
[1 May 2015 12:31] Daniƫl van Eeden
I think a lot has changed here with MySQL 5.6 online alter table options for InnoDB. Is this bug still valid?
[1 May 2015 13:17] Paul Dubois
Good point. And the ONLINE/OFFLINE syntax doesn't even exist as of MySQL 5.7. Closing this without action.