Elena Stepanova added a comment - 2016-09-19 11:15 This sounds as if the server is forcing DEFAULT 0 clause, while it actually does not. It does not really force the clause, because you wouldn't see it in SHOW CREATE TABLE , but it assumes it, just like it does e.g. for INT type. Case in point, when we remove NO_ZERO_DATE , create table, then run the following insert statement: insert into test(a) values (1); It errors out with SQL Error (1364): Field 'ts' doesn't have a default value , which shows that the server respect the configuration and does not provide a default value. It appears that in your experiment you also had sql_mode='...STRICT_ALL_TABLES...' , which would indeed make this statement produce an error. Otherwise it would produce a warning of the same kind, and insert the zero value: Here and further we use this configuration MariaDB [test]> select @@version; + -----------------+ | @@version | + -----------------+ | 10.1.16-MariaDB | + -----------------+ 1 row in set (0.01 sec)   MariaDB [test]> select @@explicit_defaults_for_timestamp; + -----------------------------------+ | @@explicit_defaults_for_timestamp | + -----------------------------------+ | 1 | + -----------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> set sql_mode= 'ANSI' ; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> create table test (a int , ts timestamp not null ); Query OK, 0 rows affected (0.70 sec)   MariaDB [test]> set sql_mode= 'ANSI,NO_ZERO_DATE' ; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> insert into test (a) values (1); Query OK, 1 row affected, 1 warning (0.07 sec)   MariaDB [test]> show warnings; + ---------+------+-----------------------------------------+ | Level | Code | Message | + ---------+------+-----------------------------------------+ | Warning | 1364 | Field 'ts' doesn't have a default value | + ---------+------+-----------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from test; + ------+---------------------+ | a | ts | + ------+---------------------+ | 1 | 0000-00-00 00:00:00 | + ------+---------------------+ 1 row in set (0.00 sec) Why can't we use TIMESTAMP field like a normal data type, where we force the user to specify a value, but without providing a default? In fact, with explicit_defaults_for_timestamp it does work exactly as a normal data type. The only difference is that there is also NO_ZERO_DATE which is by definition temporal-specific. Lets forget about NO_ZERO_DATE for a moment and compare the "base" behavior for TIMESTAMP vs INTEGER . With strict mode: MariaDB [test]> set sql_mode= 'ANSI,STRICT_ALL_TABLES' ; Query OK, 0 rows affected (0.00 sec) Both tables can be created all right: MariaDB [test]> create table test1 (a int , b int not null ); Query OK, 0 rows affected (0.35 sec)   MariaDB [test]> create table test2 (a int , ts timestamp not null ); Query OK, 0 rows affected (0.36 sec) For both tables INSERT produces an error if it doesn't specify a value for the column in question: MariaDB [test]> insert into test1 (a) values (1); ERROR 1364 (HY000): Field 'b' doesn 't have a default value MariaDB [test]> insert into test2 (a) values (1); ERROR 1364 (HY000): Field ' ts ' doesn' t have a default value Now, without strict mode: MariaDB [test]> set sql_mode= 'ANSI' ; Query OK, 0 rows affected (0.00 sec) MariaDB [test]> create table test1 (a int , b int not null ); Query OK, 0 rows affected (0.44 sec)   MariaDB [test]> create table test2 (a int , ts timestamp not null ); Query OK, 0 rows affected (0.37 sec) For both tables, INSERT produces a warning and inserts zero: MariaDB [test]> insert into test1 (a) values (1); Query OK, 1 row affected, 1 warning (0.07 sec)   MariaDB [test]> show warnings; + ---------+------+----------------------------------------+ | Level | Code | Message | + ---------+------+----------------------------------------+ | Warning | 1364 | Field 'b' doesn 't have a default value | +---------+------+----------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> insert into test2 (a) values (1); Query OK, 1 row affected, 1 warning (0.07 sec)   MariaDB [test]> show warnings; +---------+------+-----------------------------------------+ | Level | Code | Message | +---------+------+-----------------------------------------+ | Warning | 1364 | Field ' ts ' doesn' t have a default value | + ---------+------+-----------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> select * from test1; + ------+---+ | a | b | + ------+---+ | 1 | 0 | + ------+---+ 1 row in set (0.00 sec)   MariaDB [test]> select * from test2; + ------+---------------------+ | a | ts | + ------+---------------------+ | 1 | 0000-00-00 00:00:00 | + ------+---------------------+ 1 row in set (0.00 sec)

Hong Dai Thanh added a comment - 2016-09-20 04:46 I have removed my previous comment, which was made when I didn't have an environment to test. I also removed the part about INSERT statement in the bug report, which detracts everyone from seeing the actual problem in the report. Please reconsider this bug report. The problem here is - why can't we run CREATE TABLE statement, with the options explicit_defaults_for_timestamp and sql_mode=ANSI,NO_ZERO_DATE? I can't see any reason why the NO_ZERO_DATE option must prevent the creation of the table. In your test, you run this sequence of code: set sql_mode='ANSI'; create table test (a int, ts timestamp not null); set sql_mode='ANSI,NO_ZERO_DATE'; -- insert statement follows which successfully creates the table only when NO_ZERO_DATE mode is not specified , which looks like a workaround for this bug.

Elena Stepanova added a comment - 2016-09-20 12:51 You are right, this part is actually a bug. The whole behavior of NO_ZERO_DATE is an unfortunate mess inherited from MySQL. Some related references: https://bugs.mysql.com/bug.php?id=5903 (it's more generic than just temporal times, but some problems come from there) https://bugs.mysql.com/bug.php?id=34280 (it fixed CREATE TABLE for some cases, but not all, and introduced a bug with DML); https://bugs.mysql.com/bug.php?id=68041 (it supposedly fixed the bug with DML, but what else was introduced, we don't know) Additionally, behavior in current 5.7 differs from 5.6. Compare: 5.6.33, sql_mode='NO_ZERO_DATE', explicit_defaults_for_timestamp MySQL [test]> create table test1 (a int , ts timestamp not null ); ERROR 1067 (42000): Invalid default value for 'ts'   MySQL [test]> create table test2 (a int , ts timestamp default 0); Query OK, 0 rows affected, 1 warning (0.40 sec)   MySQL [test]> show warnings; + ---------+------+---------------------------------------------+ | Level | Code | Message | + ---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'ts' at row 1 | + ---------+------+---------------------------------------------+ 1 row in set (0.00 sec)   MySQL [test]> create table test3 (a int , ts datetime not null ); Query OK, 0 rows affected (0.35 sec)   MySQL [test]> create table test4 (a int , ts date not null ); Query OK, 0 rows affected (0.34 sec) 5.7.14, sql_mode='NO_ZERO_DATE', explicit_defaults_for_timestamp MySQL [test]> create table test1 (a int , ts timestamp not null ); Query OK, 0 rows affected (0.36 sec)   MySQL [test]> create table test2 (a int , ts timestamp default 0); Query OK, 0 rows affected, 1 warning (0.37 sec)   MySQL [test]> show warnings; + ---------+------+---------------------------------------------+ | Level | Code | Message | + ---------+------+---------------------------------------------+ | Warning | 1264 | Out of range value for column 'ts' at row 1 | + ---------+------+---------------------------------------------+ 1 row in set (0.00 sec)   MySQL [test]> create table test3 (a int , ts datetime not null ); Query OK, 0 rows affected (0.37 sec)   MySQL [test]> create table test4 (a int , ts date not null ); Query OK, 0 rows affected (0.38 sec) 10.1.18, 10.2.2, sql_mode='NO_ZERO_DATE', explicit_defaults_for_timestamp MariaDB [test]> create table test1 (a int , ts timestamp not null ); ERROR 1067 (42000): Invalid default value for 'ts'   MariaDB [test]> create table test2 (a int , ts timestamp default 0); ERROR 1067 (42000): Invalid default value for 'ts'   MariaDB [test]> create table test3 (a int , ts datetime not null ); Query OK, 0 rows affected (0.39 sec)   MariaDB [test]> create table test4 (a int , ts date not null ); Query OK, 0 rows affected (0.40 sec) So, in all versions behavior is different, none is consistent. Making it consistent would require serious changes; partial fixes only increase the confusion. The problem is, MySQL has already deprecated NO_ZERO_DATE , I doubt they'll fix any more issues with it; and drifting even further away from MySQL behavior is likely to bring more problems than gains. I'll leave it to bar to decide what changes, if any, should be made.

Elena Stepanova added a comment - 2017-04-23 18:47 Removing the ga label since NO_ZERO_DATE hasn't become a default sql_mode.

Alexander Barkov added a comment - 2017-10-07 10:04 MySQL is not affected: git@github.com:mysql/mysql-server.git commit ffa7c5be8e342d607698efb4a8aca58b88ad0ae4 fixed this problem.

Sergei Golubchik added a comment - 2017-10-14 14:13 ok to push