MySQL Bugs: #81875: Parsing bug for double TIMESTAMP declaration

Bug #81875	Parsing bug for double TIMESTAMP declaration
Submitted:	16 Jun 2016 6:07	Modified:	21 Jun 2016 23:43
Reporter:	Roel Van de Paar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S1 (Critical)
Version:	5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
5.6.30:

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.02 sec)

5.7.12:

mysql>  CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
ERROR 1067 (42000): Invalid default value for 'c2'

How to repeat:
CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP);

Let me give you a hint:

[openxs@fc23 5.7]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.13 MySQL Community Server (GPL)

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> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
ERROR 1067 (42000): Invalid default value for 'c2'
mysql> show variables like '%explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> set session explicit_defaults_for_timestamp=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
ERROR 1050 (42S01): Table 't1' already exists
mysql> CREATE TABLE t2 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` timestamp NULL DEFAULT NULL,
  `c2` timestamp NULL DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Not what I get on Win10 (64 bit server):

SELECT VERSION(); -- 5.7.12-log 
SHOW VARIABLES LIKE '%explicit%'; -- OFF
CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY; -- success
SHOW CREATE TABLE t1;
/* returns
CREATE TABLE `t1` (
  `c1` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MEMORY DEFAULT CHARSET=utf8
'/

This looks all expected to me. 

-- Peter
-- not a MySQL/Oracle person

Valerii, there is an small error in your output.

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
ERROR 1067 (42000): Invalid default value for 'c2'
mysql> SHOW CREATE TABLE t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql> set session explicit_defaults_for_timestamp=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.03 sec)  # You get a "table exists" error here, which cannot be valid unless a t1 table was previously there.

Yes, there was some table named t1. So, after setting that variable, I've proceeded with creating table t2 with the same columns:

mysql> CREATE TABLE t2 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.04 sec)

and it was created as expected. So, I think I was still able to make my point:

In 5.7 the result depends on explicit_defaults_for_timestamp setting, and timestamp columns behavior of 5.7 with regard to default values is documented here:

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html

to some extent.

Probably the manual is not clear enough, does not highlight this as an incompatible change in behavior, especially by default etc. But it is NOT a DDL bug then, I think.

Hello Roel,

Thank you for the report.
Let me quote Valeriy's note - In 5.7 the result depends on explicit_defaults_for_timestamp setting, and timestamp columns behavior of 5.7 with regard to default values is documented here:

http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
http://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaul...

mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| 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> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
ERROR 1067 (42000): Invalid default value for 'c2'
mysql>
mysql>
mysql> set session explicit_defaults_for_timestamp=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` timestamp NULL DEFAULT NULL,
  `c2` timestamp NULL DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

The reason you didn't see this issue on 5.6 is due to the non strict mode, in 5.7, with strict mode the first TIMESTAMP column i.e c1 in table t1, since it was not declared with the NULL attribute or an explicit DEFAULT or ON UPDATE clause, is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. Second TIMESTAMP column c2, again not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp) which was violating the strict mode.

With the non strict mode on 5.7:

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> set session explicit_defaults_for_timestamp=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

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

mysql> CREATE TABLE t1 (c1 TIMESTAMP,c2 TIMESTAMP) ENGINE=MEMORY;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=MEMORY DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thanks,
Umesh

Umesh, thanks! Got it.

Peter, does that clear it for you also?

Not as I understand immediately. Let me try to get time to check one more.

Sorry for forgetting this for months.

Yes, it is clear.