MySQL Bugs: #27417: thd->no_trans_update.stmt lost value inside of SF-exec-stack

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	CPU Architecture:	Any

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.

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)

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.

preliminary version of the patch, apparently does not cover multi- and load data

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

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.

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.

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.

Pushed into 5.0.46

Pushed into 5.1.20-beta

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

Approved by email with cosmetic comments.

there can be dependent of this bugs like bug#29769. Todo: to notify them when this one will be pushed.

Related Bug #29480 will need verification after this 27417 bug's fix is pushed.

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

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

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

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

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

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

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

Bug #29769 marked as a duplicate of this one

Pushed into 5.1.21-beta

Pushed into 5.0.48

An error that happened inside INSERT/UPDATE/DELETE statement performed from a stored function or trigger could cause inconsistency between master and slave.

Noted in 5.0.48, 5.1.21 changelogs.

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.

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.

Pushed into 5.0.54

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha