Bug #10104 Timestamp col inherits new 4.1 behavior if col is defined as a NULL column
Submitted: 22 Apr 2005 20:34 Modified: 22 Jun 2005 10:32
Reporter: Greg Fortune Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.10a, 4.1.11 OS:Linux (Debian sarge)
Assigned to: Jim Winstead CPU Architecture:Any

[22 Apr 2005 20:34] Greg Fortune
Description:
If a timestamp column is defined as

colname timestamp NULL,

the timestamp does not update on an insert.  Although I didn't really mean to specify the column as a NULL column, it seems odd that it would turn off the auto-update feature of a timestamp column.  Note that the show create table below shows the column getting a DEFAULT NULL which was not in the original create table, but makes sense.  I must be the addition of DEFAULT NULL that triggers the 4.1 behavior.

Switching column definition to

dt TIMESTAMP NOT NULL,

fixes the behavior.

Most likely, this is simply a documentation bug.  Docs at http://dev.mysql.com/doc/mysql/en/date-and-time-type-overview.html say 
"""
The first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.
"""
And docs at http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html say
"""
The first TIMESTAMP column in table row automatically is set to the current timestamp when the record is created if the column is set to NULL or is not specified at all. 

The first TIMESTAMP column in table row automatically is updated to the current timestamp when the value of any other column in the row is changed, unless the TIMESTAMP column explicitly is assigned a value other than NULL. 

If a DEFAULT value is specified for the first TIMESTAMP column when the table is created, it is silently ignored.

...

DEFAULT NULL is the same as DEFAULT CURRENT_TIMESTAMP for the first TIMESTAMP column. For any other TIMESTAMP column, DEFAULT NULL is treated as DEFAULT 0.
"""

Note that this is the first column and was not specified in one case and was specified as NULL in the second.  Also note that the DEFAULT added implicitly should be ignored anyway.

How to repeat:
mysql> drop table Log_Data;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Log_Data (
    ->         ID                              INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
    ->         host                            VARCHAR(255) NULL,
    ->         versions                        VARCHAR(255) NULL,
    ->         val                             TEXT NULL,
    ->         dt                              TIMESTAMP NULL,
    ->
    ->         INDEX                   (dt),
    ->         INDEX                   (val(10))
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE Log_Data;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                         |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_Data | CREATE TABLE `Log_Data` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `host` varchar(255) default NULL,
  `versions` varchar(255) default NULL,
  `val` text,
  `dt` timestamp NULL default NULL,
  PRIMARY KEY  (`ID`),
  KEY `dt` (`dt`),
  KEY `val` (`val`(10))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO Log_Data(val) VALUES ('testing');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Log_Data(val, dt) VALUES ('testing', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from Log_Data;
+----+------+----------+---------+------+
| ID | host | versions | val     | dt   |
+----+------+----------+---------+------+
|  1 | NULL | NULL     | testing | NULL |
|  2 | NULL | NULL     | testing | NULL |
+----+------+----------+---------+------+
2 rows in set (0.00 sec)

Suggested fix:
Either note the difference with a column defined as NULL or change the behavior (or smuck me if I missed something in the docs) :)

Also, it would be very helpful date time types overview docs linked to the page describing the gotchas relating to 4.1...  Under the timestamp section of http://dev.mysql.com/doc/mysql/en/date-and-time-type-overview.html, something to the effect of "Timestamp behavior has changed in 4.1.  Seehttp://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html and http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html for more details"
[24 Apr 2005 18:13] Jorge del Conde
Thanks for your bug report
[28 May 2005 0:48] Jim Winstead
This is solely a documentation issue. If a column is defined as 'column TIMESTAMP NULL', it is allowed to have NULL values and therefore does not default to getting the CURRENT_TIMESTAMP when assigned a NULL.
[22 Jun 2005 10:32] Jon Stephens
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated Reference Manual as suggested by bug reporter. Closed bug.