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

[25 May 2016 9:15] Daniël van Eeden
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
[26 May 2016 8:14] Umesh Shastry
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
[24 Jul 2016 12:49] Paul Dubois
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.