2
# Bug #38205 Row-based Replication (RBR) causes inconsistencies: HA_ERR_FOUND_DUPP_KEY
3
# Bug#319 if while a non-transactional slave is replicating a transaction possible problem
5
# Verifying the fact that STOP SLAVE in the middle of a group execution waits
6
# for the end of the group before the slave sql thread will stop.
7
# The patch refines STOP SLAVE to not interrupt a transaction or other type of
8
# the replication events group (the part I).
9
# Killing the sql thread continues to provide a "hard" stop (the part II).
11
# Non-deterministic tests
14
source include/master-slave.inc;
15
source include/have_innodb.inc;
19
# Part II, killed sql slave leaves instantly
22
# A. multi-statement transaction as the replication group
26
create table t1i(n int primary key) engine=innodb;
27
create table t2m(n int primary key) engine=myisam;
29
sync_slave_with_master;
34
insert into t1i values (1);
35
insert into t1i values (2);
36
insert into t1i values (3);
39
sync_slave_with_master;
42
# todo: first challenge is to find out the SQL thread id
43
# the following is not fully reliable
46
let $id=`SELECT id from information_schema.processlist where user like 'system user' and state like '%Has read all relay log%' or user like 'system user' and state like '%Reading event from the relay log%'`;
49
insert into t1i values (5);
52
let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1);
54
insert into t1i values (4);
55
insert into t2m values (1); # non-ta update
56
update t1i set n = 5 where n = 4; # to block at. can't be played with killed
58
let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1);
61
# slave sql thread must be locked out by the conn `slave' explicit lock
62
let $pos0_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
64
eval select $pos0_master - $pos0_slave as zero;
71
source include/wait_until_rows_count.inc;
73
# todo: may fail as said above
75
--echo *** kill sql thread ***
77
eval kill connection $id;
81
rollback; # release the sql thread
85
source include/wait_for_slave_sql_to_stop.inc;
86
let $sql_status= query_get_value(SHOW SLAVE STATUS, Slave_SQL_Running, 1);
87
--echo *** sql thread is *not* running: $sql_status ***
88
let $pos1_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
91
--echo *** the prove: the killed slave has not finished the current transaction ***
94
select count(*) as three from t1i;
95
eval select $pos1_master > $pos1_slave as one;
96
eval select $pos1_slave - $pos0_slave as zero;
99
delete from t2m; # remove the row to be able to replay
100
start slave sql_thread;
103
# Part I: B The homogenous transaction remains interuptable in between
110
sync_slave_with_master;
112
insert into t1i values (5);
115
let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1);
117
insert into t1i values (4);
118
update t1i set n = 5 where n = 4; # to block at. not to be played
120
let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1);
124
# slave sql can't advance as must be locked by the conn `slave' trans
125
let $pos0_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
127
eval select $pos0_master - $pos0_slave as zero;
131
# the replicated trans is blocked by the slave's local.
132
# However, it's not easy to catch the exact moment when it happens.
133
# The test issues sleep which makes the test either non-deterministic or
134
# wasting too much time.
138
send stop slave sql_thread;
141
rollback; # release the sql thread
145
source include/wait_for_slave_sql_to_stop.inc;
146
let $sql_status= query_get_value(SHOW SLAVE STATUS, Slave_SQL_Running, 1);
147
--echo *** sql thread is *not* running: $sql_status ***
149
let $pos1_slave= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
151
--echo *** the prove: the stopped slave has rolled back the current transaction ***
154
select count(*) as zero from t1i;
155
eval select $pos0_master - $pos0_slave as zero;
156
eval select $pos1_master > $pos0_slave as one;
159
start slave sql_thread;
166
sync_slave_with_master;