Bug #1326 TIMESTAMP Field Bug

• Submitted: 18 Sep 2003 0:27
• Modified: 18 Sep 2003 4:03
• Reporter: Mohammad Masumi
• Status: Not a Bug
• Category: MySQL Server: MyISAM storage engine
• Severity: S2 (Serious)
• Version: 4.0.15
• OS: Linux (RedHat 7.3)
• CPU Architecture: Any

[18 Sep 2003 0:27] Mohammad Masumi

Description:
Hi,

I create a table with 2 timestamp type filed and when i update one field other field updated automatically.
Below I show up complete sequence.

How to repeat:
mysql> create table test ( id char(20), t1 timestamp, t2 timestamp );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values( 'a', now(), now() );
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values( 'b', now(), now() ); 
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values( 'c', now(), now() ); 
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+----------------+----------------+
| id   | t1             | t2             |
+------+----------------+----------------+
| a    | 20030918113247 | 20030918113247 |
| b    | 20030918113250 | 20030918113250 |
| c    | 20030918113253 | 20030918113253 |
+------+----------------+----------------+
3 rows in set (0.00 sec)

mysql> update test set t2=now();
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from test;      
+------+----------------+----------------+
| id   | t1             | t2             |
+------+----------------+----------------+
| a    | 20030918113320 | 20030918113320 |
| b    | 20030918113320 | 20030918113320 |
| c    | 20030918113320 | 20030918113320 |
+------+----------------+----------------+
3 rows in set (0.00 sec)

[18 Sep 2003 4:03] Indrek Siitan

This is documented behaviour:
http://www.mysql.com/doc/en/DATETIME.html

Quote from the page above:
"The TIMESTAMP column type provides a type that you can use to 
automatically mark INSERT or UPDATE operations with the current 
date and time.  If you have multiple TIMESTAMP columns, only the 
first one is updated automatically."

To update the other timestamps, you need to to specify it in the
update statement and assign a NULL value to it.