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?
Best Answer
This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for
5.6
,5.7
(and effectively disabled on5.1
) and it is enabled in8.0
.Quoting from above link:
Now the issue with
5.7
is thatNO_ZERO_DATE
mode is enabled. This does not allow the default value of0000-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 theNO_ZERO_DATE
mode enabled, but theexplicit_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 ):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.