Bug #4977 update timestamp with now() affects all timestamps
Submitted: 10 Aug 2004 15:38 Modified: 10 Aug 2004 17:00
Reporter: Rudolph van Graan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.20 OS:Linux (Gentoo Linux)
Assigned to: Dean Ellis CPU Architecture:Any

[10 Aug 2004 15:38] Rudolph van Graan
Description:
davinci unixODBC # mysql --version
mysql  Ver 12.22 Distrib 4.0.20, for pc-linux-gnu (i686)

If a table contains more than one timestamp field and one of them is updated using the now() 
function, the other one's value is also changed.

If you do the following, the other timestamp is unchanged:

SQL> update abc set ts1=ts1,ts2=now();
SQLRowCount returns 1
SQL> select * from abc;
+--------------------+--------------------+
| ts1                | ts2                |
+--------------------+--------------------+
| 2004-08-10 15:34:11| 2004-08-10 15:38:04|
+--------------------+--------------------+
SQLRowCount returns 1
1 rows fetched

How to repeat:
SQL> create table abc (ts1 timestamp, ts2 timestamp);
SQLRowCount returns 0
SQL> insert into abc (ts1,ts2) values (now(),now());
SQLRowCount returns 1
SQL> select * from abc;
+--------------------+--------------------+
| ts1                | ts2                |
+--------------------+--------------------+
| 2004-08-10 15:33:45| 2004-08-10 15:33:45|
+--------------------+--------------------+
SQLRowCount returns 1
1 rows fetched
SQL> update abc set ts2=now();
SQLRowCount returns 1
SQL> select * from abc;
+--------------------+--------------------+
| ts1                | ts2                |
+--------------------+--------------------+
| 2004-08-10 15:34:11| 2004-08-10 15:34:11|
+--------------------+--------------------+
SQLRowCount returns 1
1 rows fetched
SQL> 

Suggested fix:
Don't update the other timestamp.
[10 Aug 2004 17:00] Dean Ellis
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:

This is the default/intended behavior for TIMESTAMP columns.  See:

http://dev.mysql.com/doc/mysql/en/Date_and_time_type_overview.html
[30 Apr 2009 7:44] Bogdan Gusiev
Here is a little solution how to build an update and create time stamp in the same table:
http://gusiev.com/#Update%20and%20create%20timestamps%20with%20MySQL