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

[7 Jun 2007 21:05] Andrei Elkin
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
[8 Jun 2007 8:10] Andrei Elkin
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 */;
[12 Jun 2007 16:58] Andrei Elkin
Correction to the comments of the test:
instead of /* one affected and error */ in send_eof() branch there must be
/* two affected rows */
[12 Jun 2007 17:28] Andrei Elkin
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.
[17 Sep 2007 10:31] 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/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
[27 Nov 2007 10:50] Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:53] Bugs System
Pushed into 6.0.4-alpha