MySQL Bugs: #39111: replicate not correct when rollback of a tx contain MyISAM table

Bug #39111	replicate not correct when rollback of a tx contain MyISAM table
Submitted:	29 Aug 2008 9:20	Modified:	15 Sep 2008 20:01
Reporter:	David lu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	replication transaction InnoDB MyISAM rollback

Description:
There are two tables t1, t2. t1 is InnoDB, t2 is MyISAM in master. both is MyISAM in slave.
When a transaction contains modifications of t1 and t2, and rollbak at last. t1 will keep not changed and t2 changed in master, as desired. But both t2 and t1 are changed in slave. I hope t1 keep not changed in slave too.

How to repeat:
In master:
create table t1 (c1 int not null) engine=InnoDB;
create table t2 (c1 int not null) engine=MyISAM;

In slave:
alter table t1 engine=MyISAM;

In master:
start transaction;
insert into t1 values(1);
insert into t2 values(2);
rollback;

Now check data of t1,t2 in master and slave, and you will see t2 contain one row in both server, t1 contain one row in slave and nothing in master.

Suggested fix:
When rollback and write binlog to slave, ingore the statements of InnoDB type table.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67, on both master and slave, and inform about the results.

I hava upgrade to version 5.1.26-rc. The problem is same with the old version 5.0.45.
Should I try with 5.0.67 ?

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/replication-features-transactions.html about problems which you can meet if mix transactional and not transactional tables in single transaction. Workaround: use both InnoDB tables on master.