List:General Discussion« Previous MessageNext Message »
From:Hugo van der Sanden Date:August 18 1999 2:43pm
Subject:Re: Rematch of modified record in update
View as plain text  
In <14266.43884.388068.912082@stripped>, sinisa@stripped writes:
:hugo@stripped writes:
: > >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 | Exe
:cutable |
: > +---------------------+----------------+--------+----------+----------+----
:--------+
: > | 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 | Executabl
:e |
: > +---------------------+----------------+--------+--------------------------
:--------------------------------------------------------+----------+----------
:--+
: > | 1999-08-17 15:44:25 | 19990818132132 |  34600 | .qmail.old.old.old.old.ol
:d.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.
[...]
:
:Hi!
:
:This is OK, this is not a bug. You have simply created recursive
:update algorigthm .........

Thanks for the quick reply.

Were that the case, I would have expected the same to happen in my
test table with one record. However, it is not how I understand the SQL
should work: regardless of implementation, I would expect the effect to
be the same as if the where clause were first applied to find the set
of matching records, and then the update clause applied (once!) to each
member of the matching set.

I suspect the fact that I am updating one field of a multi-field key is
part of the reason for the problem.

Hugo
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