Bug #46876 Mix of transactional and non-transactional tables causes failure in replication
Submitted: 24 Aug 2009 4:59 Modified: 27 Aug 2009 18:07
Reporter: Nidhi Shrotriya Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Zhenxing He CPU Architecture:Any

[24 Aug 2009 4:59] Nidhi Shrotriya
This is a variation of bug #40116 for 5.0 and the below scenario is also mentioned there (See the entry - [26 Oct 2008 12:32] Elena Stepanova). Opening a separate bug for 5.0 as #40116 has been closed after fixing the required scenarios mentioned for 5.1 & 6.0.

The below scenario makes slave SQL thread to abort both in 5.0.

How to repeat:
Connection #1:
use test;
create table t ( i int, primary key(i), f char(8) ) engine = innodb;
create table l ( d datetime, f char(32) ) engine = myisam;

delimiter |
create trigger tr after update on t for each row begin insert into l values ( now(), concat( 'updated t: ', old.f, ' -> ', new.f ) ); 
delimiter ;

insert into t values (4,'black'), (2,'red'), (3,'yelow'), (1,'cyan');


set autocommit = 0;

update t set f = 'yellow' where i = 3;

Connection #2:

update t set f = 'magenta' where f = 'red';

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

if # Note: if the 2nd connection also has autocommit=0, 
# the problem happens after an explicit commit or rollback of the transaction.

After wait timeout is exceeded on connection #2, the following records appear in the
slave.err log:
090702 12:44:22 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Lock wait timeout exceeded; try restarting transaction' (1205), Error on slave: 'no error' (0). Default database: 'test'. Query: 'update t set f = 'magenta' where f = 'red'', Error_code: 0
090702 12:44:22 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 3737
[24 Aug 2009 6:55] Sveta Smirnova
Thank you for the report.

Verified as described as "fix this for 5.0 too"
[27 Aug 2009 18:09] Omer Barnir
Problem is addressed in 5.1 and cannot be back ported to 5.0