Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement?

Question

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?

Answer 1

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.