1include/master-slave.inc 2Warnings: 3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 4Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 5[connection master] 6CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); 7CALL mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); 8==== Case 1: Killed query on master -> error on slave ==== 9include/assert.inc [Test only works if binlog_format is statement] 10[connection master] 11CREATE DATABASE db1; 12USE db1; 13CREATE TABLE t1 (a INT) ENGINE = MyISAM; 14CREATE TABLE trigger_table (a INT) ENGINE = MyISAM; 15SET SQL_LOG_BIN=0; 16CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW 17BEGIN 18IF NEW.a != 0 THEN 19SET @@GLOBAL.BINLOG_FORMAT = 'row'; 20DO SLEEP(1000000); 21END IF; 22INSERT INTO trigger_table VALUES (1); 23END| 24SET SQL_LOG_BIN=1; 25INSERT INTO t1 VALUES (0), (1); 26[connection master1] 27KILL QUERY <CONNECTION_ID>; 28[connection master] 29ERROR 70100: Query execution was interrupted 30SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT'; 31include/assert.inc [At most one row should be inserted in db1.t1] 32include/assert.inc [At least one row should be inserted in db1.trigger_table] 33SET SQL_LOG_BIN=0; 34DROP TRIGGER trig1; 35SET SQL_LOG_BIN=1; 36DROP TABLE trigger_table; 37DROP TABLE t1; 38DROP DATABASE db1; 39USE test; 40---- Verify there is an error on slave ---- 41include/sync_slave_io_with_master.inc 42include/wait_for_slave_sql_error.inc [errno=3001] 43---- Verify that nothing was inserted on slave ----- 44include/assert.inc [Nothing should be inserted on slave] 45---- Clean up ---- 46include/stop_slave_io.inc 47RESET SLAVE; 48RESET MASTER; 49DROP DATABASE db1; 50[connection master] 51RESET MASTER; 52[connection slave] 53include/start_slave.inc 54[connection master] 55==== Case 2: Master kills query -> no error if slave filters by db ==== 56include/assert.inc [Test only works if binlog_format is statement] 57[connection master] 58CREATE DATABASE ignored_db; 59USE ignored_db; 60CREATE TABLE t1 (a INT) ENGINE = MyISAM; 61CREATE TABLE trigger_table (a INT) ENGINE = MyISAM; 62SET SQL_LOG_BIN=0; 63CREATE TRIGGER trig1 BEFORE INSERT ON t1 FOR EACH ROW 64BEGIN 65IF NEW.a != 0 THEN 66SET @@GLOBAL.BINLOG_FORMAT = 'row'; 67DO SLEEP(1000000); 68END IF; 69INSERT INTO trigger_table VALUES (1); 70END| 71SET SQL_LOG_BIN=1; 72INSERT INTO t1 VALUES (0), (1); 73[connection master1] 74KILL QUERY <CONNECTION_ID>; 75[connection master] 76ERROR 70100: Query execution was interrupted 77SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT'; 78include/assert.inc [At most one row should be inserted in ignored_db.t1] 79include/assert.inc [At least one row should be inserted in ignored_db.trigger_table] 80SET SQL_LOG_BIN=0; 81DROP TRIGGER trig1; 82SET SQL_LOG_BIN=1; 83DROP TABLE trigger_table; 84DROP TABLE t1; 85DROP DATABASE ignored_db; 86USE test; 87---- Verify all was replicated ok ---- 88include/sync_slave_sql_with_master.inc 89[connection master] 90==== Case 3: Master kills query -> no error if slave filters by table ==== 91include/assert.inc [Test only works if binlog_format is statement] 92[connection master] 93CREATE DATABASE db1; 94USE db1; 95CREATE TABLE ignored_table (a INT) ENGINE = MyISAM; 96CREATE TABLE trigger_table (a INT) ENGINE = MyISAM; 97SET SQL_LOG_BIN=0; 98CREATE TRIGGER trig1 BEFORE INSERT ON ignored_table FOR EACH ROW 99BEGIN 100IF NEW.a != 0 THEN 101SET @@GLOBAL.BINLOG_FORMAT = 'row'; 102DO SLEEP(1000000); 103END IF; 104INSERT INTO trigger_table VALUES (1); 105END| 106SET SQL_LOG_BIN=1; 107INSERT INTO ignored_table VALUES (0), (1); 108[connection master1] 109KILL QUERY <CONNECTION_ID>; 110[connection master] 111ERROR 70100: Query execution was interrupted 112SET @@GLOBAL.BINLOG_FORMAT = 'STATEMENT'; 113include/assert.inc [At most one row should be inserted in db1.ignored_table] 114include/assert.inc [At least one row should be inserted in db1.trigger_table] 115SET SQL_LOG_BIN=0; 116DROP TRIGGER trig1; 117SET SQL_LOG_BIN=1; 118DROP TABLE trigger_table; 119DROP TABLE ignored_table; 120DROP DATABASE db1; 121USE test; 122---- Verify all was replicated ok ---- 123include/sync_slave_sql_with_master.inc 124include/rpl_end.inc 125