include/master-slave.inc
[connection master]
SET @@session.binlog_direct_non_transactional_updates= FALSE;
connection master;
call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
create table tm (a int auto_increment primary key) engine=myisam;
create table ti (a int auto_increment primary key) engine=innodb;
connection slave;
SET @saved_dbug = @@GLOBAL.debug_dbug;
set @@global.debug_dbug="d,stop_slave_middle_group";
connection master;
begin;
insert into ti set a=null;
insert into tm set a=null;
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
commit;
connection slave;
call mtr.add_suppression("Slave SQL.*Request to stop slave SQL Thread received while applying a group that has non-transactional changes; waiting for completion of the group");
call mtr.add_suppression("Slave SQL.*Slave SQL Thread stopped with incomplete event group having non-transactional changes");
include/wait_for_slave_sql_to_stop.inc
SELECT "NO" AS Last_SQL_Error, @check as `true`;
Last_SQL_Error	true
NO	1
select count(*) as one from tm;
one
1
select count(*) as one from ti;
one
1
set @@global.debug_dbug=@saved_dbug;
include/start_slave.inc
connection master;
truncate table tm;
truncate table ti;
connection slave;
set @@global.debug_dbug="d,stop_slave_middle_group,incomplete_group_in_relay_log";
connection master;
begin;
insert into ti set a=null;
insert into tm set a=null;
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
commit;
connection slave;
include/wait_for_slave_sql_to_stop.inc
SELECT "Fatal error: ... Slave SQL Thread stopped with incomplete event group having non-transactional changes. If the group consists solely of row-based events, you can try to restart the slave with --slave-exec-mode=IDEMPOTENT, which ignores duplicate key, key not found, and similar errors (see documentation for details)." AS Last_SQL_Error, @check as `true`;
Last_SQL_Error	true
Fatal error: ... Slave SQL Thread stopped with incomplete event group having non-transactional changes. If the group consists solely of row-based events, you can try to restart the slave with --slave-exec-mode=IDEMPOTENT, which ignores duplicate key, key not found, and similar errors (see documentation for details).	1
select count(*) as one  from tm;
one
1
select count(*) as zero from ti;
zero
0
set @@global.debug_dbug=@saved_dbug;
stop slave;
truncate table tm;
include/start_slave.inc
connection master;
connection slave;
set @@global.debug_dbug="d,stop_slave_middle_group,incomplete_group_in_relay_log";
connection master;
update tm as t1, ti as t2 set t1.a=t1.a * 2, t2.a=t2.a * 2;
connection slave;
include/wait_for_slave_sql_to_stop.inc
SELECT "Fatal error: ... Slave SQL Thread stopped with incomplete event group having non-transactional changes. If the group consists solely of row-based events, you can try to restart the slave with --slave-exec-mode=IDEMPOTENT, which ignores duplicate key, key not found, and similar errors (see documentation for details)." AS Last_SQL_Error, @check as `true`;
Last_SQL_Error	true
Fatal error: ... Slave SQL Thread stopped with incomplete event group having non-transactional changes. If the group consists solely of row-based events, you can try to restart the slave with --slave-exec-mode=IDEMPOTENT, which ignores duplicate key, key not found, and similar errors (see documentation for details).	1
select max(a) as two from tm;
two
2
select max(a) as one from ti;
one
1
SET @@GLOBAL.debug_dbug = @saved_dbug;
include/rpl_reset.inc
connection master;
drop table tm, ti;
connection slave;
include/rpl_end.inc