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"