MySQL Bugs: #7023: UPDATE queries change value of TIMESTAMP field even when not instructed to

Bug #7023	UPDATE queries change value of TIMESTAMP field even when not instructed to
Submitted:	5 Dec 2004 18:19	Modified:	5 Dec 2004 21:41
Reporter:	Steve Opilo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S3 (Non-critical)
Version:	4.0.21	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
I've been using MySQL to run a prototype forum I'm developing, and I noticed that when I ran an UPDATE query such as:

UPDATE `threads` SET `status`='locked' WHERE `threadid`='1205041326'

The threads table contains a field formatted as TIMESTAMP(14) and the value of that field is fed to the table using the NOW() function in the INSERT query. I use the TIMESTAMP field to store the date and time the thread was created, however when I run an UPDATE query, it seems to be running the NOW() function and updating the TIMESTAMP to the time the query was run, even though my query doesn't tell it to do so.

I've noticed this issue on several of my tables, so I'm pretty sure that it's not just a coding error on my part.

How to repeat:
Create a MyISAM table with at least 3 columns: a key, a TIMESTAMP(14), and a VARCHAR(6). Feed data into the table with an INSERT statement, being sure to use the NOW() function to provide the value to the TIMESTAMP field. Run an update query that does not include the TIMESTAMP field.

Suggested fix:
UPDATE queries should only update the values of the columns called on in the query.

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:

TIMESTAMP columns are meant to track the time of the last change
applied to a column. If you do not want this automatic behaviour you
should use DATETIME instead.

See also http://dev.mysql.com/doc/mysql/en/TIMESTAMP_pre-4.1.html

"The TIMESTAMP column type provides a type that you can use 
to automatically mark INSERT or UPDATE operations with the current 
date and time."