rpl_skip_error.test 4.12 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
# ==== Purpose ====
#
# Verify that --slave-skip-errors works correctly.  The error messages
# specified by --slave-skip-errors on slave should be ignored.  If
# such errors occur, they should not be reported and not cause the
# slave to stop.
#
# ==== Method ====
#
# We run the slave with --slave-skip-errors=1062 (the code for
11 12 13 14 15 16 17 18 19
# duplicate key).  Then we have two set of tests. In the first 
# set, we insert value 1 in a table on the slave, and then, on 
# master, we insert value 1 in the table. In the second set, we 
# insert several values on the master, disable the binlog and
# delete one of the values and re-enable the binlog. Right after,
# we perform an update on the set of values in order to generate
# a duplicate key on the slave. The errors should be ignored on
# the slave.
# 
20 21 22
# ==== Related bugs ====
#
# BUG#28839: Errors in strict mode silently stop SQL thread if --slave-skip-errors exists
23 24
# bug in this test: BUG#30594: rpl.rpl_skip_error is nondeterministic:
# BUG#39393: slave-skip-errors does not work when using ROW based replication
25

sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
26
source include/master-slave.inc;
27
source include/have_innodb.inc;
28

29 30 31
--echo ==== Test Without sql_mode=strict_trans_tables ====

--echo [on master]
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
32
create table t1 (n int not null primary key);
33 34 35

--echo [on slave]
sync_slave_with_master;
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
36
insert into t1 values (1);
37 38

--echo [on master]
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
39
connection master;
40
# Here we expect (ignored) error, since 1 is already in slave table 
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
41
insert into t1 values (1);
42
# These should work fine
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
43
insert into t1 values (2),(3);
44

45 46 47 48 49 50 51 52 53 54 55 56 57
sync_slave_with_master;
--echo [on slave]
select * from t1 order by n;

--echo ==== Test With sql_mode=strict_trans_tables ====
insert into t1 values (7),(8);
--echo [on master]
connection master;
set sql_mode=strict_trans_tables;
insert into t1 values (7), (8), (9);
--echo [on slave]
sync_slave_with_master;
select * from t1 order by n;
58
source include/check_slave_is_running.inc;
59

60
--echo ==== Clean Up ====
61 62 63 64
connection master;
drop table t1;
sync_slave_with_master;
# End of 4.1 tests
65

66 67 68
#
# #28839 Errors in strict mode silently stop SQL thread if --slave-skip-errors exists
#
69
connection master;
70 71
create table t1(a int primary key);
insert into t1 values (1),(2);
72 73 74
SET SQL_LOG_BIN=0;
delete from t1;
SET SQL_LOG_BIN=1;
75
set sql_mode=strict_trans_tables;
76
insert into t1 values (1), (2), (3);
77 78

--echo [on slave]
79 80
sync_slave_with_master;
select * from t1;
81
source include/check_slave_is_running.inc;
82 83 84 85


--echo ==== Clean Up ====

86 87
connection master;
drop table t1;
88
sync_slave_with_master;
89
# End of 5.0 tests
90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124

#
# BUG#39393: slave-skip-errors does not work when using ROW based replication
#
--echo ==== Using Innodb ====

connection master;

SET SQL_LOG_BIN=0;
CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB;
SHOW CREATE TABLE t1;
SET SQL_LOG_BIN=1;

connection slave;

CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB;
SHOW CREATE TABLE t1;

connection master;

INSERT INTO t1 VALUES(1, 1);
INSERT INTO t1 VALUES(2, 1);
INSERT INTO t1 VALUES(3, 1);
INSERT INTO t1 VALUES(4, 1);

SET SQL_LOG_BIN=0;
DELETE FROM t1 WHERE id = 4;
SET SQL_LOG_BIN=1;
UPDATE t1 SET id= id + 3, data = 2;

sync_slave_with_master;

let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
echo $error;

125 126
--echo **** We cannot execute a select as there are differences in the 
--echo **** behavior between STMT and RBR.
127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158

--echo ==== Using MyIsam ====

connection master;

SET SQL_LOG_BIN=0;
CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam;
SHOW CREATE TABLE t2;
SET SQL_LOG_BIN=1;

connection slave;

CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam;
SHOW CREATE TABLE t2;

connection master;

INSERT INTO t2 VALUES(1, 1);
INSERT INTO t2 VALUES(2, 1);
INSERT INTO t2 VALUES(3, 1);
INSERT INTO t2 VALUES(5, 1);

SET SQL_LOG_BIN=0;
DELETE FROM t2 WHERE id = 5;
SET SQL_LOG_BIN=1;
UPDATE t2 SET id= id + 3, data = 2;

sync_slave_with_master;

let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
echo $error;

159 160
--echo **** We cannot execute a select as there are differences in the 
--echo **** behavior between STMT and RBR.
161 162 163 164 165 166 167 168 169

--echo ==== Clean Up ====

connection master;

DROP TABLE t1;
DROP TABLE t2;

sync_slave_with_master;