MySQL v4.1 and Timestamp Updates

July 17, 2005 2-minute read

While developing this page I struggled over a (at least for me) strange behavior from MySQL. Every time a row is updated the first timestamp field gets updated too. This even happens when the timestamp field doesn’t occur in the query.

So I googled for that and first I thought it was a bug but that is actually not true it’s a feature.

MySQL prior to version 4.1 does this that way.

mysql> show fields from demo;
+----------+---------------+------+-----+----------------+----------------+
| Field    | Type          | Null | Key | Default        | Extra          |
+----------+---------------+------+-----+----------------+----------------+
| id       | int(11)       |      | PRI | NULL           | auto_increment |
| text     | varchar(255)  |      |     |                |                |
| created  | timestamp(14) | YES  |     | NULL           |                |
| modified | timestamp(14) | YES  |     | 00000000000000 |                |
+----------+---------------+------+-----+----------------+----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM demo;
+----+------+----------------+----------------+
| id | text | created        | modified       |
+----+------+----------------+----------------+
|  1 | demo | 20050724222326 | 00000000000000 |
+----+------+----------------+----------------+
1 row in set (0.00 sec)

mysql> UPDATE demo SET modified = NOW() WHERE id = 1;


mysql> SELECT * FROM demo;
+----+------+----------------+----------------+
| id | text | created        | modified       |
+----+------+----------------+----------------+
|  1 | demo | 20050724222533 | 20050724222533 |
+----+------+----------------+----------------+
1 row in set (0.00 sec)

As you can see the first timestamp field (created) gets updated too and so they both have the same value.