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] 6 7*** Preparing data *** 8CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10)) ENGINE=MyISAM; 9CREATE TABLE t2 LIKE t1; 10CREATE TABLE t3 LIKE t1; 11CREATE TRIGGER tr1 BEFORE UPDATE ON t1 12FOR EACH ROW BEGIN 13UPDATE t2 SET b='YY' WHERE a=NEW.a; 14END| 15CREATE TRIGGER tr2 AFTER UPDATE ON t1 16FOR EACH ROW BEGIN 17UPDATE t3 SET b='ZZ' WHERE a=NEW.a; 18END| 19 20*** Test non-transactional group w/o PK *** 21INSERT INTO t3 VALUES(1, 'AA'); 22INSERT INTO t2 VALUES(1, 'AA'); 23INSERT INTO t1 VALUES(1, 'AA'); 24include/sync_slave_sql_with_master.inc 25RENAME TABLE t3 TO t3_bak; 26UPDATE t1 SET b = 'XX' WHERE a = 1; 27SELECT * FROM t1 ORDER BY a; 28a b 291 XX 30SELECT * FROM t2 ORDER BY a; 31a b 321 YY 33SELECT * FROM t3 ORDER BY a; 34a b 351 ZZ 36call mtr.add_suppression("Slave SQL.*Table .test.t3. doesn.t exist.* Error_code: 1146"); 37call mtr.add_suppression("The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state"); 38include/wait_for_slave_sql_to_stop.inc 39SHOW TABLES LIKE 't%'; 40Tables_in_test (t%) 41t1 42t2 43t3_bak 44SELECT * FROM t1 ORDER BY a; 45a b 461 AA_for_row_or_XX_for_stmt_mixed 47SELECT * FROM t2 ORDER BY a; 48a b 491 AA_for_row_or_YY_for_stmt_mixed 50include/stop_slave_io.inc 51RENAME TABLE t3_bak TO t3; 52include/start_slave.inc 53TRUNCATE t1; 54TRUNCATE t2; 55TRUNCATE t3; 56include/sync_slave_sql_with_master.inc 57 58*** Test non-transactional group w/ PK *** 59ALTER TABLE t1 ADD PRIMARY KEY (a); 60ALTER TABLE t2 ADD PRIMARY KEY (a); 61ALTER TABLE t3 ADD PRIMARY KEY (a); 62include/sync_slave_sql_with_master.inc 63RENAME TABLE t3 TO t3_bak; 64INSERT INTO t3 VALUES(2, 'B'); 65INSERT INTO t2 VALUES(2, 'B'); 66INSERT INTO t1 VALUES(2, 'B'); 67UPDATE t1 SET b = 'X' WHERE a = 2; 68include/wait_for_slave_sql_to_stop.inc 69SELECT * FROM t1 ORDER BY a; 70a b 712 X 72SELECT * FROM t2 ORDER BY a; 73a b 742 YY 75SELECT * FROM t3 ORDER BY a; 76a b 772 ZZ 78SHOW TABLES LIKE 't%'; 79Tables_in_test (t%) 80t1 81t2 82t3_bak 83SELECT * FROM t1 ORDER BY a; 84a b 85SELECT * FROM t2 ORDER BY a; 86a b 87include/stop_slave_io.inc 88RENAME TABLE t3_bak TO t3; 89include/start_slave.inc 90TRUNCATE t1; 91TRUNCATE t2; 92TRUNCATE t3; 93include/sync_slave_sql_with_master.inc 94 95*** Test transactional group w/ PK *** 96ALTER TABLE t1 ENGINE=InnoDB; 97ALTER TABLE t2 ENGINE=InnoDB; 98ALTER TABLE t3 ENGINE=InnoDB; 99include/sync_slave_sql_with_master.inc 100RENAME TABLE t3 TO t3_bak; 101BEGIN; 102INSERT INTO t1 VALUES (3, 'C'), (4, 'D'); 103INSERT INTO t2 VALUES (3, 'C'), (4, 'D'); 104INSERT INTO t3 VALUES (3, 'C'), (4, 'D'); 105COMMIT; 106include/wait_for_slave_sql_to_stop.inc 107SELECT * FROM t1 ORDER BY a; 108a b 1093 C 1104 D 111SELECT * FROM t2 ORDER BY a; 112a b 1133 C 1144 D 115SELECT * FROM t3 ORDER BY a; 116a b 1173 C 1184 D 119SHOW TABLES LIKE 't%'; 120Tables_in_test (t%) 121t1 122t2 123t3_bak 124SELECT * FROM t1 ORDER BY a; 125a b 126SELECT * FROM t2 ORDER BY a; 127a b 128include/stop_slave_io.inc 129RENAME TABLE t3_bak TO t3; 130include/start_slave.inc 131*** Clean up *** 132DROP TABLE t1,t2,t3; 133include/sync_slave_sql_with_master.inc 134include/rpl_end.inc 135