1include/master-slave.inc 2[connection master] 3connection slave; 4STOP SLAVE; 5include/wait_for_slave_to_stop.inc 6connection master; 7SET SESSION BINLOG_FORMAT=ROW; 8CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM; 9CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM; 10INSERT INTO t1 VALUES (1,1),(2,4),(3,9); 11INSERT INTO t2 VALUES (1,1),(2,8),(3,27); 12UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c; 13include/show_binlog_events.inc 14Log_name Pos Event_type Server_id End_log_pos Info 15master-bin.000001 # Gtid # # GTID #-#-# 16master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM 17master-bin.000001 # Gtid # # GTID #-#-# 18master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM 19master-bin.000001 # Gtid # # BEGIN GTID #-#-# 20master-bin.000001 # Annotate_rows # # INSERT INTO t1 VALUES (1,1),(2,4),(3,9) 21master-bin.000001 # Table_map # # table_id: # (test.t1) 22master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 23master-bin.000001 # Query # # COMMIT 24master-bin.000001 # Gtid # # BEGIN GTID #-#-# 25master-bin.000001 # Annotate_rows # # INSERT INTO t2 VALUES (1,1),(2,8),(3,27) 26master-bin.000001 # Table_map # # table_id: # (test.t2) 27master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F 28master-bin.000001 # Query # # COMMIT 29master-bin.000001 # Gtid # # BEGIN GTID #-#-# 30master-bin.000001 # Annotate_rows # # UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c 31master-bin.000001 # Table_map # # table_id: # (test.t1) 32master-bin.000001 # Table_map # # table_id: # (test.t2) 33master-bin.000001 # Update_rows_v1 # # table_id: # 34master-bin.000001 # Update_rows_v1 # # table_id: # flags: STMT_END_F 35master-bin.000001 # Query # # COMMIT 36SELECT * FROM t1; 37a b 381 1 392 8 403 27 41SELECT * FROM t2; 42c d 431 2 442 8 453 18 46connection slave; 47START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=MASTER_LOG_POS; 48include/wait_for_slave_sql_to_stop.inc 49include/check_slave_param.inc [Exec_Master_Log_Pos] 50include/check_slave_no_error.inc 51SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 52START SLAVE; 53include/wait_for_slave_to_start.inc 54SELECT * FROM t1; 55a b 561 1 572 4 583 9 59SELECT * FROM t2; 60c d 611 1 622 8 633 27 64STOP SLAVE; 65include/wait_for_slave_to_stop.inc 66RESET SLAVE; 67connection master; 68RESET MASTER; 69SET SESSION BINLOG_FORMAT=STATEMENT; 70SET @foo = 12; 71INSERT INTO t1 VALUES(@foo, 2*@foo); 72include/show_binlog_events.inc 73Log_name Pos Event_type Server_id End_log_pos Info 74master-bin.000001 # Gtid # # BEGIN GTID #-#-# 75master-bin.000001 # User var # # @`foo`=12 76master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(@foo, 2*@foo) 77master-bin.000001 # Query # # COMMIT 78connection slave; 79START SLAVE UNTIL MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=MASTER_LOG_POS; 80include/wait_for_slave_sql_to_stop.inc 81SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 82START SLAVE; 83include/wait_for_slave_to_start.inc 84connection master; 85DROP TABLE t1, t2; 86connection slave; 87connection master; 88SET SESSION BINLOG_FORMAT=ROW; 89SET AUTOCOMMIT=0; 90CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam; 91CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam; 92CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam; 93INSERT INTO t1 VALUES (1,'master/slave'); 94INSERT INTO t2 VALUES (1,'master/slave'); 95INSERT INTO t3 VALUES (1,'master/slave'); 96CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW 97BEGIN 98INSERT INTO t2 VALUES (NEW.a,NEW.b); 99DELETE FROM t2 WHERE a < NEW.a; 100END| 101CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW 102BEGIN 103UPDATE t3 SET a =2, b = 'master only'; 104END| 105connection slave; 106include/stop_slave.inc 107connection master; 108UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1; 109DROP TRIGGER tr1; 110DROP TRIGGER tr2; 111INSERT INTO t1 VALUES (3,'master/slave'); 112INSERT INTO t2 VALUES (3,'master/slave'); 113INSERT INTO t3 VALUES (3,'master/slave'); 114COMMIT; 115SELECT * FROM t1 ORDER BY a; 116a b 1172 master only 1183 master/slave 119SELECT * FROM t2 ORDER BY a; 120a b 1212 master only 1223 master/slave 123SELECT * FROM t3 ORDER BY a; 124a b 1252 master only 1263 master/slave 127connection slave; 128SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 129include/start_slave.inc 130SELECT * FROM t1 ORDER BY a; 131a b 1321 master/slave 1333 master/slave 134SELECT * FROM t2 ORDER BY a; 135a b 1361 master/slave 1373 master/slave 138SELECT * FROM t3 ORDER BY a; 139a b 1401 master/slave 1413 master/slave 142connection master; 143DROP TABLE t1, t2, t3; 144connection slave; 145**** Case 2: Row binlog format and transactional tables **** 146connection master; 147CREATE TABLE t4 (a INT, b VARCHAR(20)) ENGINE=innodb; 148CREATE TABLE t5 (a INT, b VARCHAR(20)) ENGINE=innodb; 149CREATE TABLE t6 (a INT, b VARCHAR(20)) ENGINE=innodb; 150connection slave; 151include/stop_slave.inc 152connection master; 153BEGIN; 154INSERT INTO t4 VALUES (2, 'master only'); 155INSERT INTO t5 VALUES (2, 'master only'); 156INSERT INTO t6 VALUES (2, 'master only'); 157COMMIT; 158BEGIN; 159INSERT INTO t4 VALUES (3, 'master/slave'); 160INSERT INTO t5 VALUES (3, 'master/slave'); 161INSERT INTO t6 VALUES (3, 'master/slave'); 162COMMIT; 163SELECT * FROM t4 ORDER BY a; 164a b 1652 master only 1663 master/slave 167SELECT * FROM t5 ORDER BY a; 168a b 1692 master only 1703 master/slave 171SELECT * FROM t6 ORDER BY a; 172a b 1732 master only 1743 master/slave 175connection slave; 176SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 177include/start_slave.inc 178SELECT * FROM t4 ORDER BY a; 179a b 1803 master/slave 181SELECT * FROM t5 ORDER BY a; 182a b 1833 master/slave 184SELECT * FROM t6 ORDER BY a; 185a b 1863 master/slave 187connection slave; 188include/stop_slave.inc 189connection master; 190BEGIN; 191INSERT INTO t4 VALUES (6, 'master only'); 192INSERT INTO t5 VALUES (6, 'master only'); 193INSERT INTO t6 VALUES (6, 'master only'); 194COMMIT; 195BEGIN; 196INSERT INTO t4 VALUES (7, 'master only'); 197INSERT INTO t5 VALUES (7, 'master only'); 198INSERT INTO t6 VALUES (7, 'master only'); 199COMMIT; 200SELECT * FROM t4 ORDER BY a; 201a b 2022 master only 2033 master/slave 2046 master only 2057 master only 206SELECT * FROM t5 ORDER BY a; 207a b 2082 master only 2093 master/slave 2106 master only 2117 master only 212SELECT * FROM t6 ORDER BY a; 213a b 2142 master only 2153 master/slave 2166 master only 2177 master only 218connection slave; 219SET GLOBAL SQL_SLAVE_SKIP_COUNTER=12; 220include/start_slave.inc 221SELECT * FROM t4 ORDER BY a; 222a b 2233 master/slave 224SELECT * FROM t5 ORDER BY a; 225a b 2263 master/slave 227SELECT * FROM t6 ORDER BY a; 228a b 2293 master/slave 230connection slave; 231include/stop_slave.inc 232connection master; 233SET AUTOCOMMIT=0; 234INSERT INTO t4 VALUES (4, 'master only'); 235INSERT INTO t5 VALUES (4, 'master only'); 236INSERT INTO t6 VALUES (4, 'master only'); 237COMMIT; 238INSERT INTO t4 VALUES (5, 'master/slave'); 239INSERT INTO t5 VALUES (5, 'master/slave'); 240INSERT INTO t6 VALUES (5, 'master/slave'); 241COMMIT; 242SELECT * FROM t4 ORDER BY a; 243a b 2442 master only 2453 master/slave 2464 master only 2475 master/slave 2486 master only 2497 master only 250SELECT * FROM t5 ORDER BY a; 251a b 2522 master only 2533 master/slave 2544 master only 2555 master/slave 2566 master only 2577 master only 258SELECT * FROM t6 ORDER BY a; 259a b 2602 master only 2613 master/slave 2624 master only 2635 master/slave 2646 master only 2657 master only 266connection slave; 267SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 268include/start_slave.inc 269SELECT * FROM t4 ORDER BY a; 270a b 2713 master/slave 2725 master/slave 273SELECT * FROM t5 ORDER BY a; 274a b 2753 master/slave 2765 master/slave 277SELECT * FROM t6 ORDER BY a; 278a b 2793 master/slave 2805 master/slave 281connection master; 282DROP TABLE t4, t5, t6; 283connection slave; 284**** Case 3: Statement logging format and LOAD DATA with non-transactional table **** 285connection master; 286CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam; 287connection slave; 288include/stop_slave.inc 289connection master; 290SET SESSION BINLOG_FORMAT=STATEMENT; 291LOAD DATA INFILE 'MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|'; 292SELECT * FROM t10 ORDER BY a; 293a b 2941 master only 2952 master only 2963 master only 297connection slave; 298SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 299include/start_slave.inc 300SELECT * FROM t10 ORDER BY a; 301a b 302connection master; 303DROP TABLE t10; 304connection slave; 305include/rpl_end.inc 306