Bug #29284 | TIMESTAMP columns do not behave as described | ||
---|---|---|---|
Submitted: | 21 Jun 2007 20:02 | Modified: | 11 Jul 2007 3:42 |
Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | 5.0.38 | OS: | Any |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | qc, timestamp |
[21 Jun 2007 20:02]
Baron Schwartz
[22 Jun 2007 3:53]
Valeriy Kravchuk
Thank you for a reasonable documentation request.
[7 Jul 2007 22:28]
Paul DuBois
Regarding DEFAULT NULL, this is a version dependent thing. The change occurs at MySQL 4.1.6. Test file: drop table if exists t; create table t (t timestamp null default null); show create table t\G Resulting table: MySQL 4.1.5: *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `t` timestamp NULL default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MySQL 4.1.6: *************************** 1. row *************************** Table: t Create Table: CREATE TABLE `t` ( `t` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 This needs to be corrected for the 5.0 and higher manuals.
[7 Jul 2007 22:32]
Paul DuBois
Regarding: "In a CREATE TABLE statement, the first TIMESTAMP column can be declared in any of the following ways... In other words, you can use the current timestamp for both the initial value and the auto-update value, or either one, or neither." But according to the documentation, there is no way to achieve the "neither" case: per the docs, you can't have the first timestamp column default to NULL, because if you say DEFAULT NULL it should be the same as DEFAULT CURRENT_TIMESTAMP. I'll add an example to the manual, but according to the documentation, there _is_ a way to achieve the neither case. The relevant statement is: "With a constant DEFAULT value, the column has the given default. If the column has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated, otherwise not." So, with a constant default and no ON UPDATE CURRENT_TIMESTAMP clause, you get no automatic initialization and no automatic updating. Example of such a column: create table t (t timestamp default 0); Nevertheless, I think this isn't so clear in the manual; I'll try to clarify it.
[11 Jul 2007 3:42]
Paul DuBois
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 products. The TIMESTAMP behavior regarding NULL and DEFAULT was not well described because it did not tie the actual behavior to the changes that were made in versions 4.1.2 and 4.1.6. http://dev.mysql.com/doc/refman/4.1/en/timestamp.html now details the changes that were made, and when. The current behavior is as described for 4.1.6.