Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
Submitted: 24 Mar 2007 14:18 Modified: 3 Aug 2007 16:03
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.38 OS:Any
Assigned to: Andrei Elkin

[24 Mar 2007 14:18] Andrei Elkin
Description:
During the exectution time of a query calling a stored function SF()
the top-level query handling code expects to get thd->no_trans_update.stmt as TRUE
on return from SF if SF modified non-ta table.

This does not hold always. 

As the result, the flag is useless for binlogging purposes e.g fix for bug#23333
could exploit its property.

How to repeat:
CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE t2 (a INT NOT NULL PRIMARY KEY) ENGINE=INNODB;
CREATE TABLE t10 (a INT NOT NULL) ENGINE=MYISAM;

DELIMITER |;
CREATE FUNCTION f1(i INT,j INT) 
  RETURNS INT deterministic
BEGIN
  INSERT INTO t10 VALUES(i);
  update t2 set a=a+10 where a=1;
  RETURN(j);
END|  
DELIMITER ;|

INSERT INTO t1 VALUES(1),(2);
INSERT INTO t2 VALUES(1),(2);

(gdb) br sql_update.cc:536   # the line corresponding to start of binlogging:   # if ((error < 0) || (updated && !transactional_table))

UPDATE t1 SET a=a+f1(100,0);

check the value - must be FALSE, despite f1() modified t10.
[26 Mar 2007 8:52] Sveta Smirnova
Thank you for the report.

Verified as described:

537       if ((error < 0) || (updated && !transactional_table))
(gdb) 
(gdb) print thd->no_trans_update
$3 = false
(gdb)
[27 Mar 2007 9:20] Sergei Golubchik
The way I understand it, we need another bit in no_trans_upodate: .sub_stmt
to be used where no_trans_upodate.stmt is used now.
[9 Apr 2007 16:00] Andrei Elkin
preliminary version of the patch, apparently does not cover multi- and load data

Attachment: patch_0.diff (text/x-patch), 7.95 KiB.

[15 May 2007 6:40] Bugs System
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/26662

ChangeSet@1.2439, 2007-05-15 09:40:26+03:00, aelkin@dsl-hkibras-fe31f900-164.dhcp.inet.fi +4 -0
  Bug #27417  	thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries.
  
  Fixed with checking thd->in_sub_stmt to know precisely about the new statement.
  Couple of minor issues refined along the fix, see per-file comments.
  
  Test is delayed till related bug#13270, bug#23333.
[6 Jun 2007 13:56] Bugs System
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/28205

ChangeSet@1.2439, 2007-06-06 16:55:00+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +9 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
  bug#27716 fix.
  
  Fixed with introducing Sub_statement_state::no_trans_update_stmt flag durable within substatement life time.
  At the end of substatement the gained value contributes to thd->no_trans_update.stmt so that on the bottom
  line are:
  
  1. Eventually thd->no_trans_update.stmt will be propagated out of the
     last substatement as the union of values worked out in all
     sub-statements                  -  fixed bug#27417
  
  2. Inside of any sub-statement thd->no_trans_update.stmt corresponds
     to the value yielded at the sub-statement to satisfy
     THD::really_abort_on_warnin() concern to get the status of the current
     substatement.
  
  The supplied test verifies limitely, mostly asserts. The ultimate testing is defered
  for bug_13270, bug_23333.
[12 Jun 2007 17:34] Bugs System
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/28589

ChangeSet@1.2439, 2007-06-12 20:32:28+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +12 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
  bug#27716 fix.
  
  Fixed with introducing Sub_statement_state::no_trans_update_stmt flag durable within substatement life time
  and the new member to thd->no_trans_update.
  At the end of a substatement the gained value contributes to the new thd->no_trans_update.top
  so that the following properties are held:
  
  1. Eventually thd->no_trans_update.top will be computed as
     the union of values of .stmt gained in all invoked sub-statements.
     That fixes this bug#27417;
  
  2. Inside of any sub-statement thd->no_trans_update.stmt corresponds
     to the value yielded at the sub-statement to satisfy
     THD::really_abort_on_warnin() concern to get the status of the current
     substatement.
  
  The supplied test verifies limitely, mostly asserts. The ultimate testing is defered
  for bug_13270, bug_23333.
  
  Computing of thd->no_trans_update.all is refined to base to .stmt value for all the case including
  insert .. select statement which before the patch had an extra issue bug#28960.
[21 Jun 2007 20:12] Bugs System
Pushed into 5.0.46
[21 Jun 2007 20:15] Bugs System
Pushed into 5.1.20-beta
[26 Jun 2007 15:22] Bugs System
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/29620

ChangeSet@1.2439, 2007-06-26 18:21:52+03:00, aelkin@dsl-hkibras1-ff5dc300-70.dhcp.inet.fi +18 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[26 Jun 2007 18:36] Konstantin Osipov
Approved by email with cosmetic comments.
[13 Jul 2007 10:42] Andrei Elkin
there can be dependent of this bugs like bug#29769. Todo: to notify them when this one will be pushed.
[20 Jul 2007 12:03] Andrei Elkin
Related Bug #29480 will need verification after this 27417 bug's fix is pushed.
[20 Jul 2007 15:33] Bugs System
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/31249

ChangeSet@1.2439, 2007-07-20 18:30:53+03:00, aelkin@koti.dsl.inet.fi +18 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[20 Jul 2007 15:49] Bugs System
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/31255

ChangeSet@1.2439, 2007-07-20 18:48:58+03:00, aelkin@koti.dsl.inet.fi +20 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[20 Jul 2007 15:52] Bugs System
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/31256

ChangeSet@1.2439, 2007-07-20 18:52:03+03:00, aelkin@koti.dsl.inet.fi +16 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[20 Jul 2007 15:54] Bugs System
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/31259

ChangeSet@1.2439, 2007-07-20 18:54:08+03:00, aelkin@koti.dsl.inet.fi +20 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[20 Jul 2007 16:01] Bugs System
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/31262

ChangeSet@1.2439, 2007-07-20 19:00:54+03:00, aelkin@koti.dsl.inet.fi +16 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[20 Jul 2007 16:48] Bugs System
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/31271

ChangeSet@1.2439, 2007-07-20 19:48:10+03:00, aelkin@koti.dsl.inet.fi +16 -0
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
  
  Once had been set the flag might later got reset inside of a stored routine execution stack.
  The reason was in that there was no check if a new statement started at time of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update is wrapped up within
[30 Jul 2007 15:27] Bugs System
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/31827

ChangeSet@1.2491, 2007-07-30 18:27:36+03:00, gkodinov@magare.gmz +16 -0
  (pushing for Andrei)
  Bug #27417 thd->no_trans_update.stmt lost value inside of SF-exec-stack
    
  Once had been set the flag might later got reset inside of a stored routine 
  execution stack.
  The reason was in that there was no check if a new statement started at time 
  of resetting.
  The artifact affects most of binlogable DML queries. Notice, that multi-update 
  is wrapped up within
[31 Jul 2007 15:33] Georgi Kodinov
Bug #29769 marked as a duplicate of this one
[2 Aug 2007 19:12] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:15] Bugs System
Pushed into 5.0.48
[3 Aug 2007 16:00] Konstantin Osipov
An error that happened inside INSERT/UPDATE/DELETE statement performed from a stored function or trigger could cause inconsistency between master and slave.
[3 Aug 2007 16:03] Paul Dubois
Noted in 5.0.48, 5.1.21 changelogs.
[22 Aug 2007 7:40] Bugs System
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/32855

ChangeSet@1.2497, 2007-08-22 10:40:38+03:00, aelkin@dsl-hkibras-fe38f900-157.dhcp.inet.fi +2 -0
  bug#27417
  
  refining of cleanup of the tests.
[16 Sep 2007 16:16] Bugs System
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/34324

ChangeSet@1.2574, 2007-09-16 18:16:40+02:00, aelkin@dl145j.mysql.com +3 -0
  Merge: bug#27417,23333 manual work for fixing tests and a source code.
[27 Nov 2007 10:48] Bugs System
Pushed into 5.0.54
[27 Nov 2007 10:50] Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:52] Bugs System
Pushed into 6.0.4-alpha