Bug #22415 UPDATE updates fields that it shouldn't

• Submitted: 16 Sep 2006 16:15
• Modified: 16 Sep 2006 16:20
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.24-Debian_1~bpo.1-log
• OS: Linux (Debian/AMD64 3.1)
• CPU Architecture: Any

[16 Sep 2006 16:15] [ name withheld ]

Description:
I created a table which stores user names and login data. I played around with TIMESTAMPs a bit and now the table looks like:

mysql> explain benutzer;
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| status      | int(11)      | NO   |     | 0                 |                |
| dispname    | varchar(30)  | NO   |     |                   |                |
| pw_md5      | char(32)     | YES  |     | NULL              |                |
| registriert | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
| online      | timestamp    | YES  |     | NULL              |                |
| realname    | varchar(50)  | YES  |     | NULL              |                |
| email       | varchar(100) | YES  |     | NULL              |                |
| homepage    | varchar(100) | YES  |     | NULL              |                |
| jid         | varchar(100) | YES  |     | NULL              |                |
+-------------+--------------+------+-----+-------------------+----------------+
10 rows in set (0.00 sec)

Now, when I do an UPDATE statement which updates the "online" timestamp, it updates the "registriert" timestamp, too!

How to repeat:
1) Create a table with above layout
2) Do an update statement:

mysql> select registriert,online from benutzer;
+---------------------+---------------------+
| registriert         | online              |
+---------------------+---------------------+
| 2006-09-01 00:00:00 |                NULL |
| 2006-09-16 15:54:43 | 2006-09-16 15:54:43 |
| 2006-09-11 00:00:00 |                NULL |
+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql> update benutzer set online=now() where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select registriert,online from benutzer;
+---------------------+---------------------+
| registriert         | online              |
+---------------------+---------------------+
| 2006-09-01 00:00:00 |                NULL |
| 2006-09-16 15:55:40 | 2006-09-16 15:55:40 |
| 2006-09-11 00:00:00 |                NULL |
+---------------------+---------------------+
3 rows in set (0.00 sec)

Note what happens to the value of "registriert"!

I managed to reproduce this on another AMD64 system with Ubuntu 6.06 and 5.0.22-Debian_0ubuntu6.06.2-log.

Suggested fix:
update benutzer set online=now() where id=2;

should only update "online" and not "registriert"

[16 Sep 2006 16:17] [ name withheld ]

Here the CREATE TABLE so that you can create the table on your test system:

mysql> show create table benutzer\G
*************************** 1. row ***************************
       Table: benutzer
Create Table: CREATE TABLE `benutzer` (
  `id` int(11) NOT NULL auto_increment,
  `status` int(11) NOT NULL default '0',
  `dispname` varchar(30) NOT NULL,
  `pw_md5` char(32) default NULL,
  `registriert` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `online` timestamp NULL default NULL,
  `realname` varchar(50) default NULL,
  `email` varchar(100) default NULL,
  `homepage` varchar(100) default NULL,
  `jid` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[16 Sep 2006 16:20] [ name withheld ]

oh please close this

I didn't see this "on update CURRENT_TIMESTAMP" ... I never set this, though