Bug #74529 table creation with timestamp is rejected

• Submitted: 23 Oct 2014 15:00
• Modified: 19 Jul 2015 21:06
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.7.6
• OS: Any
• CPU Architecture: Any

[23 Oct 2014 15:00] Guilhem Bichot

Description:
create table t1(
dummy int,
i1_null_const int null default 42,
t1_null_now timestamp null default current_timestamp,
t1_null_const timestamp null default '2001-01-01 03:03:03',
i2_not_null_const int not null default 42,
t2_not_null_now timestamp not null default current_timestamp,
t2_not_null_const timestamp not null default '2001-01-01 03:03:03',
i3_null int null,
t3_null timestamp null,
i4_not_null int not null,
t4_not_null timestamp not null);

fails with

ERROR 1067 (42000): Invalid default value for 't4_not_null'

(this is the last column). If I turn strict mode off, or if I try to create only this column, no error.

I used:
revision-id:[e-mail]-20141020135525-tgufbj9a2iawbxs3 

How to repeat:
see above

Suggested fix:
Our guess:
- strict mode is on
- t4_not_null is not the first timestamp column, so does not get the implicit "default now" (which the first timestamp column gets), it gets "default 0000-00-00 00:00:00"
- strict mode contains no_zero_date
- so this 0000 implicit default is rejected.

[19 Jul 2015 21:06] Paul DuBois

Noted in 5.7.8, 5.8.0 changelogs.

If the server was started with the explicit_defaults_for_timestamp
system variable enabled, CREATE TABLE statements that defined a
column as TIMESTAMP NOT NULL failed.