MySQL Bugs: #20670: UPDATE using key and invoking trigger that modifies this key does not stop

Bug #20670	UPDATE using key and invoking trigger that modifies this key does not stop
Submitted:	23 Jun 2006 21:26	Modified:	5 Oct 2006 16:15
Reporter:	Dmitry Lenev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.23-bk	OS:	Any (Any)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
UPDATE statement which WHERE clause uses key and which invokes trigger trigger that modifies this key works indefinetely. Whereas similar UPDATE statement which modifies this key explicitly works fine. See "How to repeat" section for more info.

How to repeat:
drop table if exists t1;
create table t1 (i int, j int key);
insert into t1 values (1,1), (2,2), (3,3);
# This works ok
update t1 set i= i+ 10, j= j+10 where j > 2;
select * from t1;
#+------+----+
#| i    | j  |
#+------+----+
#|    1 |  1 | 
#|    2 |  2 | 
#|   13 | 13 | 
#+------+----+
create trigger t1_bu before update on t1 for each row set new.j = new.j + 10;
# And this will work indefinetely
update t1 set i= i+ 10 where j > 2;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11612

ChangeSet@1.2250, 2006-09-08 14:28:59+04:00, dlenev@mockturtle.local +7 -0
  Proposed fix for bug#20670 "UPDATE using key and invoking trigger that
  modifies this key does not stop" (version for 5.0 only).
  
  UPDATE statement which WHERE clause used key and which invoked trigger
  that modified field in this key worked indefinetely.
  
  This problem occured because in cases when UPDATE statement was
  executed in update-on-the-fly mode (in which row is updated right
  during evaluation of select for WHERE clause) the new version of
  the row became visible to select representing WHERE clause and was
  updated again and again.
  We already solve this problem for UPDATE statements which does not
  invoke triggers by detecting the fact that we are going to update
  field in key used for scanning and performing update in two steps,
  during the first step we gather information about the rows to be
  updated and then doing actual updates. We also do this for
  MULTI-UPDATE and in its case we even detect situation when such
  fields are updated in triggers (actually we simply assume that
  we always update fields used in key if we have before update
  trigger).
  
  The fix simply extends this check which is done in check_if_key_used()/
  QUICK_SELECT_I::check_if_keys_used() routine/method in such way that
  it also detects cases when field used in key is updated in trigger.
  As nice side-effect we have more precise and thus more optimal
  perfomance-wise check for the MULTI-UPDATE.
  
  Note that this check will be implemented in much more elegant way in 5.1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11662

ChangeSet@1.2291, 2006-09-10 18:51:57+04:00, dlenev@mockturtle.local +5 -0
  Proposed fix for bug#20670 "UPDATE using key and invoking trigger that
  modifies this key does not stop" (5.1 version).
  
  UPDATE statement which WHERE clause used key and which invoked trigger
  that modified field in this key worked indefinetely.
  
  This problem occured because in cases when UPDATE statement was
  executed in update-on-the-fly mode (in which row is updated right
  during evaluation of select for WHERE clause) the new version of
  the row became visible to select representing WHERE clause and was
  updated again and again.
  We already solve this problem for UPDATE statements which does not
  invoke triggers by detecting the fact that we are going to update
  field in key used for scanning and performing update in two steps,
  during the first step we gather information about the rows to be
  updated and then doing actual updates. We also do this for
  MULTI-UPDATE and in its case we even detect situation when such
  fields are updated in triggers (actually we simply assume that
  we always update fields used in key if we have before update
  trigger).
  
  The fix simply extends this check which is done with help of
  check_if_key_used()/QUICK_SELECT_I::check_if_keys_used()
  routine/method in such way that it also detects cases when
  field used in key is updated in trigger. We do this by
  changing check_if_key_used() to take field bitmap instead
  field list as argument and passing TABLE::write_set
  to it (we also have to add info about fields used in
  triggers to this bitmap a bit earlier).
  As nice side-effect we have more precise and thus more optimal
  perfomance-wise check for the MULTI-UPDATE.

Approved my e-mail.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12316

ChangeSet@1.2250, 2006-09-21 11:35:38+04:00, dlenev@mockturtle.local +9 -0
  Fix for bug#20670 "UPDATE using key and invoking trigger that modifies
  this key does not stop" (version for 5.0 only).
  
  UPDATE statement which WHERE clause used key and which invoked trigger
  that modified field in this key worked indefinetely.
  
  This problem occured because in cases when UPDATE statement was
  executed in update-on-the-fly mode (in which row is updated right
  during evaluation of select for WHERE clause) the new version of
  the row became visible to select representing WHERE clause and was
  updated again and again.
  We already solve this problem for UPDATE statements which does not
  invoke triggers by detecting the fact that we are going to update
  field in key used for scanning and performing update in two steps,
  during the first step we gather information about the rows to be
  updated and then doing actual updates. We also do this for
  MULTI-UPDATE and in its case we even detect situation when such
  fields are updated in triggers (actually we simply assume that
  we always update fields used in key if we have before update
  trigger).
  
  The fix simply extends this check which is done in check_if_key_used()/
  QUICK_SELECT_I::check_if_keys_used() routine/method in such way that
  it also detects cases when field used in key is updated in trigger.
  As nice side-effect we have more precise and thus more optimal
  perfomance-wise check for the MULTI-UPDATE.
  Also check_if_key_used()/QUICK_SELECT_I::check_if_keys_used() were
  renamed to is_key_used()/QUICK_SELECT_I::is_keys_used() in order to
  better reflect that boolean predicate.
  
  Note that this check is implemented in much more elegant way in 5.1

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12324

ChangeSet@1.2291, 2006-09-21 13:39:29+04:00, dlenev@mockturtle.local +7 -0
  Fix for bug#20670 "UPDATE using key and invoking trigger that modifies
  this key does not stop" (5.1 version).
  
  UPDATE statement which WHERE clause used key and which invoked trigger
  that modified field in this key worked indefinetely.
  
  This problem occured because in cases when UPDATE statement was
  executed in update-on-the-fly mode (in which row is updated right
  during evaluation of select for WHERE clause) the new version of
  the row became visible to select representing WHERE clause and was
  updated again and again.
  We already solve this problem for UPDATE statements which does not
  invoke triggers by detecting the fact that we are going to update
  field in key used for scanning and performing update in two steps,
  during the first step we gather information about the rows to be
  updated and then doing actual updates. We also do this for
  MULTI-UPDATE and in its case we even detect situation when such
  fields are updated in triggers (actually we simply assume that
  we always update fields used in key if we have before update
  trigger).
  
  The fix simply extends this check which is done with help of
  check_if_key_used()/QUICK_SELECT_I::check_if_keys_used()
  routine/method in such way that it also detects cases when
  field used in key is updated in trigger. We do this by
  changing check_if_key_used() to take field bitmap instead
  field list as argument and passing TABLE::write_set
  to it (we also have to add info about fields used in
  triggers to this bitmap a bit earlier).
  As nice side-effect we have more precise and thus more optimal
  perfomance-wise check for the MULTI-UPDATE.
  Also check_if_key_used() routine and similar method were renamed
  to is_key_used()/is_keys_used() in order to better reflect that
  it is simple boolean predicate.
  Finally, partition_key_modified() routine now also takes field
  bitmap instead of field list as argument.

Fixed in 5.0.27 and 5.1.12

Noted in 5.0.27, 5.1.12 changelogs.

An UPDATE that referred to a key column in the WHERE clause and
activated a trigger that modified the column resulted in a loop.

The 5.0.x fix is in 5.0.30.