MySQL Bugs: #6534: Timestamp fields in the same table seem to be linked or in error

Bug #6534	Timestamp fields in the same table seem to be linked or in error
Submitted:	9 Nov 2004 23:23	Modified:	10 Nov 2004 16:59
Reporter:	Paul Servant	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.22	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any

Description:
After upgrading my mysql server with a backport of 4.0.22 for my Debian 3.0 system I noticed the following problem when creating a table with two timestamp fields.  I've also noticed the problem in 4.0.21.

Both timestamps update when updating a table row and updating one of the two timestamps.  Is this a known problem?  I'm using the timestamps for both creation and modification timestamps.  I can switch over to a unix timestamp integer based system but I already have software that uses timestamp.  The system ran fine on mysql 3.6.

How to repeat:
mysql> create table test (
    -> test_id int not null auto_increment,
    -> test_current int not null default 0,
    -> test_ctime timestamp,
    -> test_mtime timestamp,
    -> primary key (test_id));

mysql> insert into test set test_current=87, test_ctime=now(), test_mtime=now();

mysql> select * from test;
+---------+--------------+----------------+----------------+
| test_id | test_current | test_ctime     | test_mtime     |
+---------+--------------+----------------+----------------+
|       1 |           87 | 20041109151357 | 20041109151357 |
+---------+--------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> update test set test_current=88, test_mtime=now() where test_id=1;

mysql> select * from test;
+---------+--------------+----------------+----------------+
| test_id | test_current | test_ctime     | test_mtime     |
+---------+--------------+----------------+----------------+
|       1 |           88 | 20041109151435 | 20041109151435 |
+---------+--------------+----------------+----------------+
1 row in set (0.00 sec)

mysql> describe test;
+--------------+---------------+------+-----+----------------+----------------+
| Field        | Type          | Null | Key | Default        | Extra          |
+--------------+---------------+------+-----+----------------+----------------+
| test_id      | int(11)       |      | PRI | NULL           | auto_increment |
| test_current | int(11)       |      |     | 0              |                |
| test_ctime   | timestamp(14) | YES  |     | NULL           |                |
| test_mtime   | timestamp(14) | YES  |     | 00000000000000 |                |
+--------------+---------------+------+-----+----------------+----------------+
4 rows in set (0.00 sec)

Verified with 4.0.23-debug-log

Sorry, this is not a bug. 
You set the second timestamp column to NOW(). First timestamp column is updated automatically.

Thanks for the quick response.  Why does MySQL function this way?  I had version 3.6 and I was updating the second timestamp with now() and the first one wasn't affected.

I was wrong about the timestamp field in version 3.6.  I was using the datetime type.  I would still like to understand why the two timestamps are linked.  Is there an article I can read.  Thanks.

They are not linked.
First timestamp column are always set to the current date and time value during UPDATE, the second column was set to the current date and time value by you.

For more info, please, read MySQL manual.

Sure, you can read the part of the MySQL Reference
Manual that discusses these data types:

http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html

Thanks for the help.