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.

StackExchange Database Administrators Q#257863, answer score: 5