MySQL Bugs: #89243: Provide ALTER TABLE ... ALGORITHM=metadata syntax

Bug #89243	Provide ALTER TABLE ... ALGORITHM=metadata syntax
Submitted:	15 Jan 2018 12:56	Modified:	15 Jan 2018 13:33
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.7,8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, DDL, metadata-only

Description:
Current ALTER TABLE commands may be just a metadata change and therefore very fast. e.g. adding a value to the end of an ENUM column. Others may require rebuilding the whole table so may take time depending on the size of the table.

The behaviour may also change from one major version to another so what's a meta data only change on a master may be a full table rebuild on a slave (e.g. 5.7 --> 8.0). This can be problematic as it may take some time to diagnose and it may be necessary to abort the command on the slave and execute it differently to get the meta-data only change, to allow replication to continue.

How to repeat:
see above.

Suggested fix:
When running DDL statements such as ALTER TABLE there's already an ALGORITHM=xxx setting and I'd like to propose an extra option ALGORITHM=metadata which would determine if the change is a metadata only change and if it is proceed. If it is not the command would fail with an error: Requested ALTER TABLE statement is not a metadata-only change.

This would avoid a few situations where you think something is a metadata change only to find it is not. It would be convenient to apply this to 5.7 and 8.0 as during the migration from 5.7 to 8.0 there are various changes in character sets, collations and it's possible that some of these changes may lead to the table being rebuilt on the slave but not on the master and that can be somewhat problematic.

I believe this functionality has been discussed with the MySQL development teams but I can not find an explicit feature request for this. If this is a duplicate please point me at the original request.

Partly related blog post from a colleague: http://jfg-mysql.blogspot.nl/2015/08/unexpected-memory-consumption-for-bulk-index-creation...

Hello Simon,

Thank you for the feature request!

Thanks,
Umesh

See also:
https://bugs.mysql.com/bug.php?id=80792
Enh 24316299 - ALTER TABLE SHOULD SUPPORT ALGORITHM=METADATA