MySQL Bugs: #27395: OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()

Bug #27395	OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
Submitted:	23 Mar 2007 11:25	Modified:	8 May 2007 8:55
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.38	OS:	Any
Assigned to:	Andrei Elkin	CPU Architecture:	Any

Description:
The option is to be reset at the end of transaction, particularly at the end of the top level query execution if the query invokes a stored function SF.
However, the bit is set ON and reset OFF inside of SF() execution stack.

Consequently, the fact that non-ta table was modified with SF() gets lost
and the query 

  UPDATE t set f1=... where f2=SF(t_non_ta),  where SF() modifies non-ta table

won't be binlogged if `t' the top-level table is not modified whereas `t_non_ta'
is done.

How to repeat:
delimiter |;
--disable_warnings
drop function if exists bug23333|
drop table if exists t1,t2|
--enable_warnings
 CREATE TABLE t1 (a int  NOT NULL auto_increment primary key) ENGINE=MyISAM|
 CREATE TABLE t2 (a int  NOT NULL auto_increment, b int, PRIMARY KEY (a)) ENGINE=InnoDB|

reset master|
insert into t2 values (1,1)|

create function bug23333() 
RETURNS int(11)
DETERMINISTIC
begin
  insert into t1 values (null);
  select count(*) from t1 into @a;
  return @a;
end|

--error ER_DUP_ENTRY
insert into t2 values (bug23333(),1)| 
show binlog events /* must show the insert */|

Suggested fix:
Utilize thd->no_trans_update flag to carry two flags: if the current query
modified non-ta table, and if the current ta does that.

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/22788

ChangeSet@1.2488, 2007-03-23 17:12:58+02:00, aelkin@andrepl.(none) +14 -0
  Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
  
  thd->options' OPTION_STATUS_NO_TRANS_UPDATE bit was not restored at the end of SF() invocation, where
  SF() modified non-ta table.
  As the result of this artifact it was not possible to detect whether there were any side-effects when
  top-level query ends. 
  If the top level query table was not modified and the bit is lost there would be no binlogging.
  
  Fixed with preserving the bit inside of thd->no_trans_update struct. The struct agregates two bool flags
  telling whether the current query and the current transaction modified any non-ta table.
  The flags stmt, all are dropped at the end of the query and the transaction.

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/22866

ChangeSet@1.2492, 2007-03-24 14:27:07+02:00, aelkin@andrepl.(none) +1 -0
  Bug #27395  OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() 
  
  eliminating the unnecessary option.

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/22867

ChangeSet@1.2492, 2007-03-24 14:40:38+02:00, aelkin@andrepl.(none) +3 -0
  Bug #27395  OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() 
  
  eliminating the unnecessary option; and replacing site-dependant stuff in the test

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/22868

ChangeSet@1.2492, 2007-03-24 15:21:45+02:00, aelkin@dsl-hkibras1-ff1dc300-249.dhcp.inet.fi +4 -0
  Bug #27395  OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() 
  
  eliminating the unnecessary option; and replacing site-dependant stuff in the test

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/22869

ChangeSet@1.2493, 2007-03-24 15:30:32+02:00, aelkin@dsl-hkibras1-ff1dc300-249.dhcp.inet.fi +1 -0
  Bug #27395  OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF() 
  
  fixing wrong written assignment

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/22878

ChangeSet@1.2494, 2007-03-24 19:20:00+02:00, aelkin@dsl-hkibras1-ff1dc300-249.dhcp.inet.fi +2 -0
  Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
  
  removing test host sensitive stuff for pushbuild

pushed to 5.0-marvel

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/24596

ChangeSet@1.2581, 2007-04-16 16:55:37+02:00, aelkin@dl145j.mysql.com +4 -0
  Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
  
  tests for the bug and associated is moved into another file because
  they appeared to be binlog-format sensitive.

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/24606

ChangeSet@1.2582, 2007-04-16 19:20:46+02:00, aelkin@dl145j.mysql.com +2 -0
  Bug #27395 OPTION_STATUS_NO_TRANS_UPDATE is not preserved at the end of SF()
  
  refining the test with active innodb

Pushed into 5.1.18-beta

Pushed into 5.0.42

Can we have a sentence for the changelog? (How does
this affect users?)

Paul, there is nothing for the user as I see it. OPTION_STATUS_NO_TRANS_UPDATE is an internal feature to facilitate some operations including binlogging. The fix refines the way the option is set ON and drops OFF.

No changelog entry needed.

Further discussion between Andrei and Serg yielded the
conclusion that, indeed, no changelog entry was needed.