List:General Discussion« Previous MessageNext Message »
From:hugo Date:August 18 1999 12:30pm
Subject:Rematch of modified record in update
View as plain text  
>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
Thread
Rematch of modified record in updatehugo18 Aug
  • Rematch of modified record in updatesinisa18 Aug
    • Re: Rematch of modified record in updateHugo van der Sanden18 Aug
      • Re: Rematch of modified record in updateMichael Widenius23 Aug
        • Re: Rematch of modified record in updateHugo van der Sanden23 Aug
        • Re: Rematch of modified record in updateHugo van der Sanden2 Sep
          • Re: Rematch of modified record in updateMichael Widenius2 Sep
            • Re: Rematch of modified record in updateHugo van der Sanden3 Sep
              • Re: Rematch of modified record in updateMichael Widenius7 Sep
  • Rematch of modified record in updateMichael Widenius23 Aug