Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement? In mysql 5.7.28 I create a table like this:

create table t1 (
id int not null,
d1 timestamp) 
engine=innodb;


which works fine and creates d1 as non-null with default value current_timetamp,on update current_timestamp.

But when I try to create the same table with a two timestamp fields as below:

create table t1 (
id int not null,
d1 timestamp,
d2 timestamp) 
engine=innodb;


I get an error:


  SQL error (1067): invalid default value for 'd2'


Why I get an error only when I add a second timestamp field?
Is this a bug or some expected behavior in mysql?
This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for 5.6,5.7 (and effectively disabled on 5.1) and it is enabled in 8.0.

Quoting from above link:


  (5.7) If explicit_defaults_for_timestamp is disabled, the server enables
  the nonstandard behaviors and handles TIMESTAMP columns as follows:
  
  
  TIMESTAMP columns not explicitly declared with the NULL attribute are    automatically declared with the NOT NULL attribute. Assigning
  such a    column a value of NULL is permitted and sets the column to
  the    current timestamp.
  The first TIMESTAMP column in a table, if not explicitly declared    with the NULL attribute or an explicit DEFAULT or ON UPDATE
  attribute, is automatically declared with the DEFAULT
  CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
  TIMESTAMP columns following the first one, if not explicitly declared    with the NULL attribute or an explicit DEFAULT attribute,
  are    automatically declared as DEFAULT '0000-00-00 00:00:00' (the
  “zero”    timestamp). For inserted rows that specify no explicit value
  for such    a column, the column is assigned '0000-00-00 00:00:00' and
  no warning    occurs.
  
  
  Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is
  enabled, a default value of '0000-00-00 00:00:00' may be invalid.


Now the issue with 5.7 is that NO_ZERO_DATE mode is enabled. This does not allow the default value of 0000-00-00 00:00:00 which as described in the documentation would be added as default value (if not explicitly declared) in timestamp columns following the first one.

Mysql 8.0 still has the NO_ZERO_DATE mode enabled, but the explicit_defaults_for_timestamp envvar is by default enabled which according to the documentation it would add null as the default value see below (which will not cause any errors on table creation ):


  (8.0) If explicit_defaults_for_timestamp is enabled, the server disables the
  nonstandard behaviors and handles TIMESTAMP columns as follows:
  
  [..]
  
  
  TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and
  permit NULL values. Assigning such a column a value of NULL sets it to
  NULL, not the current timestamp.
  
  
  [..]
  
  
  The first TIMESTAMP column in a table is not handled differently from TIMESTAMP columns following the first one.
  


This behavior has also been discussed on MySQL's issue tracker but has marked as "Not a bug".

As @Akina mentioned in comments, do not rely on default values. Always write the full specification for the field.