>Description:
An update that should match one row updated the same row many times.
Here is the sequence of events:
mysql> describe MailConfiguration;
+-------------+---------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------------------+-------+
| Created | datetime | | | 0000-00-00 00:00:00 | |
| LastUpdated | timestamp(14) | YES | | NULL | |
| UserID | int(11) | | PRI | 0 | |
| FileName | varchar(80) | | PRI | | |
| Contents | text | YES | | NULL | |
| Executable | enum('N','Y') | | | N | |
+-------------+---------------+------+-----+---------------------+-------+
6 rows in set (0.00 sec)
mysql> select * from MailConfiguration where UserID = 34600;
+---------------------+----------------+--------+----------+----------+------------+
| Created | LastUpdated | UserID | FileName | Contents | Executable |
+---------------------+----------------+--------+----------+----------+------------+
| 1999-08-17 15:44:25 | 19990818132115 | 34600 | .qmail | ./Mail/ | N |
+---------------------+----------------+--------+----------+----------+------------+
1 row in set (0.01 sec)
mysql> update MailConfiguration set FileName=concat(Filename,".old") where UserID =
> 34600;
Query OK, 19 rows affected (0.12 sec)
Rows matched: 20 Changed: 19 Warnings: 2
mysql> select * from MailConfiguration where UserID = 34600
+---------------------+----------------+--------+----------------------------------------------------------------------------------+----------+------------+
| Created | LastUpdated | UserID | FileName
| Contents | Executable |
+---------------------+----------------+--------+----------------------------------------------------------------------------------+----------+------------+
| 1999-08-17 15:44:25 | 19990818132132 | 34600 |
.qmail.old.old.old.old.old.old.old.old.old.old.old.old.old.old.old.old.old.old.o | ./Mail/
| N |
+---------------------+----------------+--------+----------------------------------------------------------------------------------+----------+------------+
1 row in set (0.01 sec)
mysql>
>How-To-Repeat:
Not sure: I tried a test database with an identical table into which I
inserted just that row, and the problem did not occur. I have not yet had
an opportunity to check whether I can reproduce the problem with fewer
than the 32162 rows of the real table.
>Fix:
Well, using 'FileName = ".qmail.old"' would at least give the correct
data, though it reports 'matched 2, changed 1'.
>Submitter-Id: <submitter ID>
>Originator: Hugo van der Sanden
>Organization:
Orbix Ltd, acting for Netcom Internet Ltd
>MySQL support: none
>Synopsis: update may rematch the same line many times
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.22.22 (Source distribution)
>Environment:
System: SunOS dev4.nixdev.netcom.net.uk 5.7 Generic sun4d sparc SUNW,SPARCserver-1000
Architecture: sun4
Some paths: /usr/bin/perl /usr/local/bin/make /usr/local/bin/gcc
GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.7/2.7.2.3/specs
gcc version 2.7.2.3
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --prefix=/NIX/mysql --localstatedir=/NIX/cdb
Perl: This is perl, version 5.005_03 built for sun4-solaris