MySQL Bugs: #4752: Corruption of Timestamp columns on record updates

Bug #4752	Corruption of Timestamp columns on record updates
Submitted:	26 Jul 2004 15:23	Modified:	26 Jul 2004 17:49
Reporter:	Fabrice SEGURA	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.18	OS:	Any (all)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
There is a severe problem with the handling of updates of records including timestamp(14) columns, who gets modified, and set to the current date (now) when it is not requested to.
The modification doesn't even appear in the binary logs.

I've seen the bug in Mysql 4.0.18, under Windows and Linux (Debian), and no matter that the table is MyIsam or InnoDB.

How to repeat:
Create a table with the following :

CREATE TABLE Test (
  Username char(32) NOT NULL,  
  DateCreated timestamp(14) NOT NULL,
  Status integer NOT NULL default '0',
  PRIMARY KEY  (Username));

Insert a record with

Insert into Test values ("12345", now(), 0);

now select * from test, and look at the timestamp field value.
Wait a few seconds, and update the record with:

update test set Status=1 where Username="12345";

select * from test again, and you notice that the DateCreated value has changed !!!!!

Suggested fix:
Just fix it ! The value of the timestamp column should modify only if updated.

Sorry, didn't read §12.3.1, that didn't exist in the 4.0.18 doc set....

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

That is the default behavior for TIMESTAMP columns.  You probably want DATETIME.