Submitted: 18 Mar 2016 16:03 Modified: 19 Jul 2016 8:06
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, explain

[18 Mar 2016 16:03] Simon Mudd
ALTER TABLE now has many ways of working and that can be a bit confusing even for a seasoned MySQL DBA especially as things change all the time.

How to repeat:
Run ALTER TABLE and see which behaviour happens depending on the statement you execute.

There are various possible behaviours:

1. METADATA change, no change to data
2. INLINE change, no full rebuild and apps can use the table while the change is happening (e.g. new indexes being added)
3. TABLE REBUILD, traditional full rebuild of the table

Suggested fix:
I'd like to see EXPLAIN EXTENDED to show what it would do with the ALTER TABLE statement.

There are other subtle things that may trigger a full rebuild of the table including changes due to data type changes (e.g. the "old" and "new" timestamp or datetime formats), so having a way to see what MySQL would do without actually triggering the action would be really good.
[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:06] MySQL Verification Team
Hello Simon,

Thank you for the feature request!

[16 Oct 2016 19:11] Morgan Tocker
Duplicate of BUG #34354