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

[23 Mar 2007 11:25] Andrei Elkin
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.
[23 Mar 2007 15:15] 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/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.
[24 Mar 2007 12:29] 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/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.
[24 Mar 2007 12:42] 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/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
[24 Mar 2007 13: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/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
[24 Mar 2007 13:30] 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/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
[24 Mar 2007 17:20] 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/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
[2 Apr 2007 7:43] Andrei Elkin
pushed to 5.0-marvel
[16 Apr 2007 14:55] 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/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.
[16 Apr 2007 17:20] 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/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
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[18 Apr 2007 15:47] Bugs System
Pushed into 5.0.42
[24 Apr 2007 17:10] Paul Dubois
Can we have a sentence for the changelog? (How does
this affect users?)
[1 May 2007 11:04] Andrei Elkin
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.
[1 May 2007 14:22] Paul Dubois
No changelog entry needed.
[8 May 2007 15:51] Paul Dubois
Further discussion between Andrei and Serg yielded the
conclusion that, indeed, no changelog entry was needed.