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

[22 May 2015 21:27] Nakoa Mccullough
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.
[23 May 2015 16:41] MySQL Verification Team
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)
[24 May 2015 7:19] MySQL Verification Team
is this really same as https://bugs.mysql.com/bug.php?id=75439 ?
[27 May 2015 23:14] Nakoa Mccullough
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
[28 May 2015 10:40] MySQL Verification Team
Thank you for the feedback.