MySQL Bugs: #81587: Combining ALTER operations triggers table rebuild

Bug #81587	Combining ALTER operations triggers table rebuild
Submitted:	25 May 2016 9:15	Modified:	24 Jul 2016 12:49
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.27, 5.6.30, 5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I wanted to change the default charset on a table with: 'DEFAULT CHARSET latin1'
And then I got this error:

mysql> ALTER TABLE t1 DEFAULT CHARSET=latin1;                                    
ERROR 1067 (42000): Invalid default value for 'c1'

So I tried this:
mysql> ALTER TABLE t1
    ->   MODIFY COLUMN `c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
    ->   MODIFY COLUMN `c2` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',
    ->   DEFAULT CHARSET=latin1;
^CCtrl-C -- sending "KILL QUERY 10885002" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

I pressed Ctrl-C because it took a long time

Only changing c1 and c2 was very quick. And then changing the default charset was also.

How to repeat:
Change defaults for two timestamp columns and the default charset in one ALTER statement. It should be quick, but it takes too much time and was probably rebuilding the table. I don't think a MDL lock was in play here.

Suggested fix:
Ensure this is a metadata only change. 

Related:
Bug #80792 	ALTER TABLE should support ALGORITHM=METADATA

Hello Daniël,

Thank you for the report.
Observed this at my end on 5.6.30 build.
First three operations took no time, where as 4th one took 1+min.
ALTER TABLE t1 DEFAULT CHARSET=latin1; 
ALTER TABLE t1 MODIFY COLUMN `c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01';
ALTER TABLE t1 MODIFY COLUMN `c2` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01';
ALTER TABLE t1 MODIFY COLUMN `c1` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',MODIFY COLUMN `c2` timestamp NOT NULL DEFAULT '1970-01-01 01:00:01',DEFAULT CHARSET=latin1;

Thanks,
Umesh

Posted by developer:
 
Noted in 5.6.33, 5.7.15 changelogs.

In-place ALTER TABLE operations which when executed separately caused
no table rebuild could when combined into a single statement result
in a table rebuid.