MySQL Bugs: #84069: Improve ALTER TABLE documentation to reflect Online DDL

Bug #84069	Improve ALTER TABLE documentation to reflect Online DDL
Submitted:	6 Dec 2016 5:31	Modified:	8 Feb 2017 16:29
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The documentation states that the table is read-only while the ALTER statement is running but that is not the case for many ALTER TABLE operations on InnoDB tables.

How to repeat:
Compare

http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary copy of the original table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

with

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

and

http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

with

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

Suggested fix:
Update the documentation.

The ALTER documentation states

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads (and writes).

Does that apply to Online DDL?  The Online DDL documentation is vague about what happens during the exclusive access.

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-concurrency.html

Depending on the internal workings of the online DDL operation and the LOCK clause of the ALTER TABLE statement, an online DDL operation may wait for currently executing transactions that are accessing the table to commit or roll back before completing because exclusive access to the table is required for a brief time during the initial and final phases of the DDL operation

Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end

Thank you for the bug report.

Posted by developer:
 
ALTER TABLE documentation has been revised to clarify that the description of the ALTER TABLE copy method applies to operations that are not performed in place. Surrounding content was reorganized. Links to relevant online DDL content were added.

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#alter-table-performance

The changes should appear online soon.

Thank you for the bug report.