Bug #80792 ALTER TABLE should support ALGORITHM=METADATA

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;

Adding COMMENTS also applies here as maybe do a few other things.

Posted by developer:
 
Reported version value updated to reflect release name change from 5.8 to 8.0

Hello Morgan,

Thank you for the feature request!

Thanks,
Umesh

See also;
https://bugs.mysql.com/bug.php?id=89243

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."