Description:
Hello,
The default sql_mode in 5.7.14 includes 'NO_ZERO_DATE'. So it rises error 'incorrect datetime value' if there is '0000-00-00 00:00:00' record in a table even if DDL statement isn't related to the column with this data at all.
I've found that it happens when ALGORITHM=COPY is used for DDL which looks reasonable - a new table is created so all data is copied to it including zero dates.
Statements which use ALGORITHM=INPLACE don't rise this error.
So if some table contains such a data (it was inserted sometime in the past) you have to disable/enable NO_ZERO_DATE for DDL statements to be sure that they won't fail due to this issue.
How to repeat:
mysql> set @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
mysql> create table t1 ( id int, creation_time datetime default null );
mysql> insert into t1 values (1, '0000-00-00 00:00:00');
mysql> set @@sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ZERO_IN_DATE,NO_ZERO_DATE';
mysql> alter table t1 modify id smallint;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'creation_time' at row 1
