MySQL Update Statement Causing Side Effects

Question

I have a MySQL table in the format shown below:

mysql> select event_time, count, result, pcm_id from Events_CallMeBack_2011_08_05 WHERE pcm_id=1566;
+---------------------+-------+--------+--------+
| event_time          | count | result | pcm_id |
+---------------------+-------+--------+--------+
| 2011-08-05 23:45:04 |     0 | NULL   |   1566 |
+---------------------+-------+--------+--------+
1 row in set (0.00 sec)

The problem is that when I run an UPDATE query modifying the result column, the event_time is also being updated as a side effect. I have no clue why this is happening: I certainly don't want the event_time field to be updated when I try to update the result column.

Here's the query:

UPDATE Events_CallMeBack_2011_08_05 SET result ='D' WHERE pcm_id = '1566'

Here's the resulting column after running the UPDATE statement.

mysql> select event_time, count, result, pcm_id from Events_CallMeBack_2011_08_05 WHERE pcm_id=1566;
+---------------------+-------+--------+--------+
| event_time          | count | result | pcm_id |
+---------------------+-------+--------+--------+
| 2011-08-20 19:40:21 |     0 | D      |   1566 |
+---------------------+-------+--------+--------+
1 row in set (0.00 sec)

The event_time column was update to the current local time of the mysql server.

My initial analysis is that mysql is automatically updating event_time (TIMESTAMP) column on Update statements. The CREATE TABLE statement doesn't specify this so I'm clueless why this is happening.

Create Statement:

strSql = "CREATE TABLE " + strTableName +
"(id INT NOT NULL, event_time TIMESTAMP,....

Table is described below.

mysql> describe Events_CallMeBack_2011_08_05;


+------------+-------------+------+-----+-------------------+-----------------------------+
| Field      | Type        | Null | Key | Default           | Extra                       |
   +------------+-------------+------+-----+-------------------+-----------------------------+
| id         | int(11)     | NO   |     | NULL                  |                             |
| event_time | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| event_code | varchar(30) | YES  |     | NULL                         |                             |
| count      | int(11)     | YES  |     | 0                 |                             |
| result     | varchar(1)  | YES  |     | NULL              |                             |
| pcm_id     | int(11)     | NO   | PRI | NULL              | auto_increment              |
 +------------+-------------+------+-----+-------------------+-----------------------------+

Answer 1

Yes, that is the documented behavior of the TIMESTAMP data type. It updates to the current time every time you update a row.

With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

If you define your TIMESTAMP column with a constant default value (e.g. 0), you can override this behavior. Read the manual page for more details.

Answer 2

The event_time column is probably declared with the type TIMESTAMP. Columns of this type are set to the current time when the row is inserted or updated.

Consider using DATETIME instead.