MySQL server 5.7.17, multiple timestamp not null fields on table

Question

can somebody please explain why the following is happening? When I run this statement:

CREATE TABLE `MyTable2` (
   `id` int(10) unsigned NOT NULL,
   `start` timestamp NOT NULL,
   `cTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
);

I get my table created. But when I run this one:

CREATE TABLE `MyTable2` (
   `id` int(10) unsigned NOT NULL,
   `start` timestamp NOT NULL,
   `end` timestamp NOT NULL,
   `cTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
);

I get an error stating:

[42000][1067] Invalid default value for 'end'

What am I missing? Is this the expected behaviour?

[Update]: It seems that there's a filed bug here https://bugs.mysql.com/bug.php?id=80163 but I can not make anything out of the description. Silencing the warnings and/or changing the sql_mode is not a solution as it changes the semantics of the created table (two timestamp columns not null with no default).

[Update]: The sql_mode on my server is as follows:

ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

~~[Solution/Explanation]:~~ Turns out that here https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp this behaviour is explained in detail. In order to get the expected semantics, I had to enable sysvar_explicit_defaults_for_timestamp in my server's configuration.

@OP: It is due to Automatic Initialization and Updating for TIMESTAMP and DATETIME. — Ravinder Reddy
– Ravinder Reddy, Commented Feb 3, 2017 at 9:18
@RavinderReddy So what is the solution? Cause reading that leads me to think that it is a bug. — gpol
– gpol, Commented Feb 3, 2017 at 9:27
I just visited link you provided, and observe that this may be cause by SQL Mode, try to Set SQL mode as set @@sql_mode = '' and then execute your CREATE TABLE command, this may resolve your issue — Jaydip Jadhav
– Jaydip Jadhav, Commented Feb 3, 2017 at 9:34
@JaydipJ In that link the sql statement is CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL); and the resulting table has defaults. — gpol
– gpol, Commented Feb 3, 2017 at 9:35

Shadow · Accepted Answer · 2017-02-03 09:59:58Z

6

I agree that this is a bit hidden and mysql could be more explicit about this. Mysql manual on timestamp initialisation says:

With an ON UPDATE CURRENT_TIMESTAMP clause but no DEFAULT clause, the column is automatically updated to the current timestamp but does not have the current timestamp for its default value.

The default in this case is type dependent. TIMESTAMP has a default of 0 unless defined with the NULL attribute, in which case the default is NULL.

The highlighted section applies to you: the implicit default is 0 from the 2nd timestamp field on, which is the same as '0000-00-00 00:00:00'. You have strict and no zero date sql modes enabled that forbid this default value.

However, the 1st timestamp field is assigned current_timestamp() as default (it is treated specially), this is why the 1st query passes with your current sql mode settings.

Either make the 2nd timestamp field nullable or change your sql mode settings. The former is the preferred approach.

edited Feb 3, 2017 at 9:59

answered Feb 3, 2017 at 9:45

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

gpol Over a year ago

I am obviously missing something fundamental here. When I create a table like this CREATE TABLE ``MyTable2`` (``id`` int(10) unsigned NOT NULL, ``start`` timestamp NOT NULL, PRIMARY KEY (``id``)); and then I issue an insert statement like this insert into MyTable2 (id) VALUES (2); shouldn't I get an error? As the link you provided implies, there's always a default for the timestamp field.

Shadow Over a year ago

I added a few more sentences a couple of minutes ago to my answer that explain your question in the comment.

gpol Over a year ago

I am upvoting your answer because it made me dig a lot and gain an understanding on what's going on. Finally I found this dev.mysql.com/doc/refman/5.7/en/… which explains and solves my problem.

Shadow Over a year ago

Pls read the linked documentation very carefully: the referenced setting has already been deprecated. It is not recommended to rely on such feature.

Shadow Over a year ago

I'm sorry, but this was not your question! You asked for an explanation to the behaviour and that's what I provided. You treat your own solution as an answer to a question you did not even ask.

|

Collectives™ on Stack Overflow

MySQL server 5.7.17, multiple timestamp not null fields on table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related