MySQL Bugs: #75098: first timestamp column defaults to automatic initialization

Bug #75098	first timestamp column defaults to automatic initialization
Submitted:	3 Dec 2014 17:26	Modified:	23 Dec 2014 15:49
Reporter:	philippe weltz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	CURRENT_TIMESTAMP timestamp automatic

Description:
The first timestamp defined in a 'create table' is automatically set to "on update CURRENT_TIMESTAMP"

How to repeat:
create table t (n int, t timestamp, t2 timestamp);

desc t;
+-----+----------+----+---+-------------------+---------------------------+
|Field| Type     |Null|Key|Default            |Extra                      |
+-----+----------+----+---+-------------------+---------------------------+
|n    | int(11)  |YES |   |NULL               |                           |
|t    | timestamp|NO  |   |CURRENT_TIMESTAMP  |on update CURRENT_TIMESTAMP|
|t2   | timestamp|NO  |   |0000-00-00 00:00:00|                           |
+-----+----------+----+---+-------------------+---------------------------+

The documentation this is optional, not the default?
http://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html

"As of MySQL 5.6.5, TIMESTAMP and DATETIME columns _can_ be automatically initializated and updated 
[...]
To specify automatic properties, use the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses."

IMHO it is not nice to have this by default anyway. You may want to store timestamps from the physical world, and not have one of them magically updated when you touch the row (ex: start_time, stop_time... )

Suggested fix:
Timestamp should not be in 'automatic update' unless specified

Workaround: add a 'default' e.g.
create table t (n int, t timestamp default 0, t2 timestamp);

Hello Philippe Weltz,

Thank you for report.
Imho this is an expected behavior and not a bug.

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

- TIMESTAMP columns not explicitly declared with the NULL attribute are assigned the NOT NULL attribute. (Columns of other data types, if not explicitly declared as NOT NULL, permit NULL values.) Setting such a column to NULL sets it to the current timestamp.

The first TIMESTAMP column in a table, if 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 attributes.

- TIMESTAMP columns following the first one, if not declared with the NULL attribute or an explicit DEFAULT clause, are automatically assigned DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.

Please see manual for more details for non-standard, and standard behavior - http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

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

mysql> drop table if exists t;create table t (n int, t timestamp, t2 timestamp);
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.11 sec)

mysql> desc t;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| n     | int(11)   | YES  |     | NULL                |                             |
| t     | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| t2    | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+
3 rows in set (0.01 sec)

But, with mysqld started with --explicit_defaults_for_timestamp

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.23                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.23-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

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

mysql> drop table if exists t;create table t (n int, t timestamp, t2 timestamp);
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> desc t;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| n     | int(11)   | YES  |     | NULL    |       |
| t     | timestamp | YES  |     | NULL    |       |
| t2    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

//

mysql> drop table if exists t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (n int, t timestamp default 0, t2 timestamp);
Query OK, 0 rows affected (0.06 sec)

mysql> desc t;
+-------+-----------+------+-----+---------------------+-------+
| Field | Type      | Null | Key | Default             | Extra |
+-------+-----------+------+-----+---------------------+-------+
| n     | int(11)   | YES  |     | NULL                |       |
| t     | timestamp | YES  |     | 0000-00-00 00:00:00 |       |
| t2    | timestamp | YES  |     | NULL                |       |
+-------+-----------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

Thanks,
Umesh

Hello,

If this is normal behavior, could this be clarified a bit in the documentation?

Maybe my English is poor, but when I read the DataType chapter, and then http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

I read "can be automatically  initialized"  
Then I think OK, this is an optional feature and as I don't need it I skip reading on.

Couldn't it be stated more clearly, maybe something like:
"As of MySQL 5.6.5, TIMESTAMP and DATETIME columns _are by default_ automatically initializated and updated to the current date and time [...]"

I think newcomers to MySQL will be caught off-guard by this, as it is quite different from other databases.