MySQL Bugs: #80163: CREATE TABLE fails with multiple TIMESTAMP NOT NULL columns without defaults

Bug #80163	CREATE TABLE fails with multiple TIMESTAMP NOT NULL columns without defaults
Submitted:	26 Jan 2016 22:32	Modified:	27 Jan 2016 10:22
Reporter:	Vitaly Kruglikov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.10	OS:	MacOS (El Capitan 10.11.2)
Assigned to:		CPU Architecture:	Any
Tags:	timestamp

Description:
The statement 

CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL);

fails with: ERROR 1067 (42000): Invalid default value for 'ts'

However, if I only have one TIMESTAMP column with "NOT NULL" and without default, then the operation succeeds. See transcript below

$ mysql -u root 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.10 Homebrew

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> use client_jobs_v29_vkruglikov
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL);
ERROR 1067 (42000): Invalid default value for 'ts'

mysql> CREATE TABLE annotation (`created` TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE annotation (`ts` TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.01 sec)

How to repeat:
On mysql 5.7.10:

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

mysql> use MyTest;
Database changed

mysql> CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL);
ERROR 1067 (42000): Invalid default value for 'ts'

sql_mode in my server:

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You need to enable explicit_defaults_for_timestamp

zhai weixiang, explicit_defaults_for_timestamp is deprecated according to http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaul.... Also, http://dev.mysql.com/doc/refman/5.7/en/create-table.html defines DEFAULT as optional in column_definition: "data_type [NOT NULL | NULL] [DEFAULT default_value]"

Thank you for the bug report. The create table fails due to default sql_mode to convert warnings in error.

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaul...

"In MySQL, the TIMESTAMP data type differs in nonstandard ways from other data types: ...."

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.12 Source distribution PULL: 2016-JAN-14

Copyright (c) 2000, 2016, 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 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL);
ERROR 1067 (42000): Invalid default value for 'ts'
mysql 5.7 > select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------
| @@sql_mode
+-------------------------------------------------------------------------------------------------------------------------------------------
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+-------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql 5.7 > set @@sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.7 > CREATE TABLE annotation (`created` TIMESTAMP NOT NULL, `ts` TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > show create table annotation;
+------------+------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------+
| Table      | Create Table
                                             |
+------------+------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------+
| annotation | CREATE TABLE `annotation` (
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ts` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------+
1 row in set (0.00 sec)

http://bugs.mysql.com/bug.php?id=80239 marked as duplicate of this one.

How can this be "Not a Bug"? Changing the sql_mode leads to invalid dates in the database and also, the resulting table does not have the required semantics (that is two fields not null with no default).

Can you please provide the reason why the table is created with one timestamp field, while it cannot be created with two timestamp fields?