Bug #80792 ALTER TABLE should support ALGORITHM=METADATA
Submitted: 18 Mar 2016 15:07 Modified: 19 Jul 2016 8:07
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[18 Mar 2016 15:07] Morgan Tocker 
Description:
Currently ALTER TABLE supports a number of assertions to make sure that the system is not being brought down:

ALTER my_table ...., ALGORITHM=INPLACE, LOCK=NONE;

What it is missing is an assertion to make sure it is a meta data only change, for example:

ALTER my_table ...., ALGORITHM=METADATA;

This is particularly important in MySQL 5.7+ because there are more meta-data only changes (via support with virtual columns).  But it is also true for extending enums.

The distinction between this and ALGORITHM=INPLACE/LOCK=NONE is required because (a) slaves can not process this in the same way, (b) there is still competition for resources.

How to repeat:
-

Suggested fix:
ALTER my_table ...., ALGORITHM=METADATA;
[18 Mar 2016 15:10] Simon Mudd 
Adding COMMENTS also applies here as maybe do a few other things.
[18 Jun 2016 21:36] Omer Barnir 
Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0
[19 Jul 2016 8:07] MySQL Verification Team 
Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh
[15 Jan 2018 15:19] MySQL Verification Team 
See also;
https://bugs.mysql.com/bug.php?id=89243
[27 Jul 2018 14:44] Daniël van Eeden 
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-12.html

"InnoDB now supports ALGORITHM=INSTANT for the following ALTER TABLE operations:

    Adding a column. This feature is referred to as “Instant ADD COLUMN”.

    Adding or dropping a virtual column.

    Adding or dropping a column default value.

    Modifying the definition of an ENUM or SET column.

    Changing the index type.

    Renaming a table.

Operations that support ALGORITHM=INSTANT only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution phases of the operation, and table data is unaffected, making the operations instantaneous. If not specified explicitly, ALGORITHM=INSTANT is used by default by operations that support it. If ALGORITHM=INSTANT is specified but not supported, the operation fails immediately with an error.

For more information about operations that support ALGORITHM=INSTANT, see Online DDL Operations.

Thanks to the Tencent Games DBA team for the contribution."