1# ==== Purpose ==== 2# 3# Verify that --slave-skip-errors works correctly. The error messages 4# specified by --slave-skip-errors on slave should be ignored. If 5# such errors occur, they should not be reported and not cause the 6# slave to stop. 7# 8# ==== Method ==== 9# 10# We run the slave with --slave-skip-errors=1062 (the code for 11# duplicate key). Then we have two set of tests. In the first 12# set, we insert value 1 in a table on the slave, and then, on 13# master, we insert value 1 in the table. In the second set, we 14# insert several values on the master, disable the binlog and 15# delete one of the values and re-enable the binlog. Right after, 16# we perform an update on the set of values in order to generate 17# a duplicate key on the slave. The errors should be ignored on 18# the slave. 19# 20# ==== Related bugs ==== 21# 22# BUG#28839: Errors in strict mode silently stop SQL thread if --slave-skip-errors exists 23# bug in this test: BUG#30594: rpl.rpl_skip_error is nondeterministic: 24# BUG#39393: slave-skip-errors does not work when using ROW based replication 25 26source include/have_innodb.inc; 27source include/master-slave.inc; 28 29--connection slave 30let $initial_skipped_error= query_get_value(show global status like "Slave_skipped_errors", Value, 1); 31 32connection master; 33--echo ==== Test Without sql_mode=strict_trans_tables ==== 34 35create table t1 (n int not null primary key); 36 37sync_slave_with_master; 38insert into t1 values (1); 39 40connection master; 41# Here we expect (ignored) error, since 1 is already in slave table 42insert into t1 values (1); 43# These should work fine 44insert into t1 values (2),(3); 45 46sync_slave_with_master; 47select * from t1 order by n; 48 49--echo ==== Test With sql_mode=strict_trans_tables ==== 50insert into t1 values (7),(8); 51connection master; 52set sql_mode=strict_trans_tables; 53insert into t1 values (7), (8), (9); 54sync_slave_with_master; 55select * from t1 order by n; 56source include/check_slave_is_running.inc; 57 58--echo ==== Clean Up ==== 59connection master; 60drop table t1; 61sync_slave_with_master; 62# End of 4.1 tests 63 64# 65# #28839 Errors in strict mode silently stop SQL thread if --slave-skip-errors exists 66# 67connection master; 68create table t1(a int primary key); 69insert into t1 values (1),(2); 70SET SQL_LOG_BIN=0; 71delete from t1; 72SET SQL_LOG_BIN=1; 73set sql_mode=strict_trans_tables; 74insert into t1 values (1), (2), (3); 75 76sync_slave_with_master; 77select * from t1; 78source include/check_slave_is_running.inc; 79 80 81--echo ==== Clean Up ==== 82 83connection master; 84drop table t1; 85sync_slave_with_master; 86# End of 5.0 tests 87 88# 89# BUG#39393: slave-skip-errors does not work when using ROW based replication 90# 91--echo ==== Using Innodb ==== 92 93connection master; 94 95SET SQL_LOG_BIN=0; 96CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB; 97SHOW CREATE TABLE t1; 98SET SQL_LOG_BIN=1; 99 100connection slave; 101 102call mtr.add_suppression("Slave SQL.*Could not execute .*te_rows event on table test.t.; Duplicate entry.* error.* 1062"); 103 104CREATE TABLE t1(id INT NOT NULL PRIMARY KEY, data INT) Engine=InnoDB; 105SHOW CREATE TABLE t1; 106 107connection master; 108 109INSERT INTO t1 VALUES(1, 1); 110INSERT INTO t1 VALUES(2, 1); 111INSERT INTO t1 VALUES(3, 1); 112INSERT INTO t1 VALUES(4, 1); 113 114SET SQL_LOG_BIN=0; 115DELETE FROM t1 WHERE id = 4; 116SET SQL_LOG_BIN=1; 117UPDATE t1 SET id= id + 3, data = 2; 118 119sync_slave_with_master; 120 121let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); 122echo $error; 123 124--echo **** We cannot execute a select as there are differences in the 125--echo **** behavior between STMT and RBR. 126 127--echo ==== Using MyIsam ==== 128 129connection master; 130 131SET SQL_LOG_BIN=0; 132CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam; 133SHOW CREATE TABLE t2; 134SET SQL_LOG_BIN=1; 135 136connection slave; 137 138CREATE TABLE t2(id INT NOT NULL PRIMARY KEY, data INT) Engine=MyIsam; 139SHOW CREATE TABLE t2; 140let $current_skipped_error= query_get_value(show global status like "Slave_skipped_errors", Value, 1); 141--let $delta_skipped_error= `select $current_skipped_error - $initial_skipped_error from dual` 142--echo # Slave_skipped_errros = $delta_skipped_error 143connection master; 144 145INSERT INTO t2 VALUES(1, 1); 146INSERT INTO t2 VALUES(2, 1); 147INSERT INTO t2 VALUES(3, 1); 148INSERT INTO t2 VALUES(5, 1); 149 150SET SQL_LOG_BIN=0; 151DELETE FROM t2 WHERE id = 5; 152SET SQL_LOG_BIN=1; 153UPDATE t2 SET id= id + 3, data = 2; 154 155sync_slave_with_master; 156 157let $error= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1); 158echo $error; 159 160let $current_skipped_error= query_get_value(show global status like "Slave_skipped_errors", Value, 1); 161--let $delta_skipped_error= `select $current_skipped_error - $initial_skipped_error from dual` 162--echo # Slave_skipped_errros = $delta_skipped_error 163 164--echo **** We cannot execute a select as there are differences in the 165--echo **** behavior between STMT and RBR. 166 167--echo ==== Clean Up ==== 168 169connection master; 170 171DROP TABLE t1; 172DROP TABLE t2; 173 174sync_slave_with_master; 175--source include/rpl_end.inc 176