MySQL Bugs: #28960: non-trans temp table changes with insert .. select not binlogged after rollback

Bug #28960	non-trans temp table changes with insert .. select not binlogged after rollback
Submitted:	7 Jun 2007 21:05	Modified:	12 Jun 2007 17:28
Reporter:	Andrei Elkin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	<= 5.0.42, 5.1.x	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Changes in non-trans temp table are not binlogged when transaction rolls back.

How to repeat:
begin;
insert into temp_table select * from some_table;
rollback;

Suggested fix:
select_insert::send_error(), send_eof() should be refined to set
thd->no_trans_update.all to TRUE correctly and regardless on s->tmp_table flag.

That was done for other DML queries in the patch serg@serg.mylan|ChangeSet|20050127213856|44651

A regression test for the bug.

# send_eof() branch
create temporary table tt (a int unique);
create table ti (a int) engine=innodb;
show master status;

begin; 
insert into ti values (1); 
insert into ti values (2) ; 
insert into tt select * from ti /* one affected and error */;
--enable_info
rollback;
--disable_info

show master status;
select count(*) from ti /* zero */;
insert into ti select * from tt;
select * from tt /* that is what slave would not get */;

# send_error() branch
delete from ti;
delete from tt where a=1;
reset master;
show master status;

begin; 
insert into ti values (1); 
insert into ti values (2) /* to make the dup error in the following */; 
--error ER_DUP_ENTRY
insert into tt select * from ti /* one affected and error */;
--enable_info
rollback;
--disable_info

show master status;
select count(*) from ti /* zero */;
insert into ti select * from tt;
select * from tt /* that is what slave would not get */;

Correction to the comments of the test:
instead of /* one affected and error */ in send_eof() branch there must be
/* two affected rows */

calling it as a dup for bug#27417 because some refactoring of setting the value
to thd->no_trans_update.all made for that bug fixes the case.

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

ChangeSet@1.2575, 2007-09-17 12:31:10+02:00, aelkin@dl145j.mysql.com +5 -0
  Merge: bug@27417,23333 and bug#28960 tests with 5.1

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha