MySQL Bugs: #77138: NO_ZERO_DATE in STRICT_TRANS_TABLES breaks create table w/ timestamps

Bug #77138	NO_ZERO_DATE in STRICT_TRANS_TABLES breaks create table w/ timestamps
Submitted:	22 May 2015 21:27	Modified:	28 May 2015 10:40
Reporter:	Nakoa Mccullough	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.7.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In 5.7.4+ the combination of including STRICT_TRANS_TABLES in the default sql_mode, NO_ZERO_DATE becoming a part of STRICT_TRANS_TABLES, and the default value for timestamp columns of '0000-00-00 00:00:00' causes creating a not null timestamp column to blow up.  

How to repeat:
mysql> create table t2 (c1 timestamp not null)engine=innodb;
ERROR 1067 (42000): Invalid default value for 'c1'

Suggested fix:
Move the NO_ZERO_DATE behavior back to its own separate, not default, flag for sql_mode.  Including new behavior into an already existing flag is also creating other migration problems on our path to testing 5.7.

Thank you for the bug report. Which OS are you using and which server package?. Thanks.

c:\tmp\mysql-5.7.7-rc-winx64>bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.7-rc MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE d1;
Query OK, 1 row affected (0.00 sec)

mysql> USE d1
Database changed
mysql> SELECT @@GLOBAL.SQL_MODE;
+-----------------------------------------------------------------------------------+
| @@GLOBAL.SQL_MODE                                                                 |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.SQL_MODE;
+-----------------------------------------------------------------------------------+
| @@SESSION.SQL_MODE                                                                |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t2 (c1 timestamp not null)engine=innodb;
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t2 VALUES (NULL);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM t2;
+---------------------+
| c1                  |
+---------------------+
| 2015-05-23 13:39:41 |
+---------------------+
1 row in set (0.00 sec)

is this really same as https://bugs.mysql.com/bug.php?id=75439 ?

Hi Miguel,

I created a new default install and was not able to reproduce the bug.  Someone was trying to clean up warnings in the error log and added the 'explicit_defaults_for_timestamp' flag to our my.cnf.  Please close this bug.
We still have issues but, they are related to the bug Shane pointed out so, I'll follow up on that ticket.

--Koa

Thank you for the feedback.