Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_slave_skip_myisam.test

raw
1 # Every statement in this test is either executing under ROW or
2 # STATEMENT format, which requires the slave thread to be able to apply
3 # both statement and row events. Hence, we only need to execute this
4 # test for MIXED mode.
5 # Skip in valgrind because of bug#20952953
6 --source include/not_valgrind.inc
7 source include/have_binlog_format_mixed.inc;
8
9 --source include/set_privilege_checks_user_as_system_user.inc
10 source include/master-slave.inc;
11 source include/force_myisam_default.inc;
12 source include/have_myisam.inc;
13
14
15 --echo **** On Slave ****
16 connection slave;
17
18 STOP SLAVE;
19 --source include/wait_for_slave_to_stop.inc
20
21 --echo **** On Master ****
22 connection master;
23 SET SESSION BINLOG_FORMAT='ROW';
24
25 CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
26 CREATE TABLE t2 (c INT, d INT) ENGINE=MyISAM;
27 INSERT INTO t1 VALUES (1,1),(2,4),(3,9);
28 INSERT INTO t2 VALUES (1,1),(2,8),(3,27);
29 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
30 UPDATE t1,t2 SET b = d, d = b * 2 WHERE a = c;
31 source include/show_binlog_events.inc;
32
33 # These tables should be changed
34 SELECT * FROM t1;
35 SELECT * FROM t2;
36 save_master_pos;
37 --echo **** On Slave ****
38 connection slave;
39
40 # Stop when reaching the the first table map event.
41 --replace_result $master_log_pos MASTER_LOG_POS
42 eval START SLAVE UNTIL SOURCE_LOG_FILE='master-bin.000001', SOURCE_LOG_POS=$master_log_pos;
43 source include/wait_for_slave_sql_to_stop.inc;
44 let $slave_param= Exec_Master_Log_Pos;
45 let $slave_param_value= $master_log_pos;
46 source include/check_slave_param.inc;
47 source include/check_slave_no_error.inc;
48
49 # Now we skip *one* table map event. If the execution starts right
50 # after that table map event, *one* of the involved tables will be
51 # changed.
52 SET GLOBAL SQL_REPLICA_SKIP_COUNTER=2;
53 START SLAVE;
54 --source include/wait_for_slave_to_start.inc
55 sync_with_master;
56
57 # These values should be what was inserted, not what was
58 # updated. Since we are skipping the first table map of the group
59 # representing the UPDATE statement above, we should skip the entire
60 # group and not start executing at the first table map.
61 SELECT * FROM t1;
62 SELECT * FROM t2;
63
64 STOP SLAVE;
65 --source include/wait_for_slave_to_stop.inc
66 RESET SLAVE;
67 connection master;
68 RESET MASTER;
69
70 SET SESSION BINLOG_FORMAT=STATEMENT;
71 SET @foo = 12;
72 let $master_log_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
73 INSERT INTO t1 VALUES(@foo, 2*@foo);
74 save_master_pos;
75 source include/show_binlog_events.inc;
76
77 connection slave;
78 --replace_result $master_log_pos MASTER_LOG_POS
79 eval START SLAVE UNTIL SOURCE_LOG_FILE='master-bin.000001', SOURCE_LOG_POS=$master_log_pos;
80 source include/wait_for_slave_sql_to_stop.inc;
81 SET GLOBAL SQL_REPLICA_SKIP_COUNTER=2;
82 START SLAVE;
83 --source include/wait_for_slave_to_start.inc
84 sync_with_master;
85
86 --echo **** On Master ****
87 connection master;
88 DROP TABLE t1, t2;
89 --source include/sync_slave_sql_with_master.inc
90
91 #
92 # More tests for BUG#28618
93 #
94 # Case 1.
95 # ROW binlog format and non-transactional tables.
96 # Create the group of events via triggers and try to skip
97 # some items of that group.
98 #
99
100 connection master;
101 SET SESSION BINLOG_FORMAT='ROW';
102 SET AUTOCOMMIT=0;
103
104 CREATE TABLE t1 (a INT, b VARCHAR(20)) ENGINE=myisam;
105 CREATE TABLE t2 (a INT, b VARCHAR(20)) ENGINE=myisam;
106 CREATE TABLE t3 (a INT, b VARCHAR(20)) ENGINE=myisam;
107
108 INSERT INTO t1 VALUES (1,'master/slave');
109 INSERT INTO t2 VALUES (1,'master/slave');
110 INSERT INTO t3 VALUES (1,'master/slave');
111
112 DELIMITER |;
113
114 CREATE TRIGGER tr1 AFTER UPDATE on t1 FOR EACH ROW
115 BEGIN
116 INSERT INTO t2 VALUES (NEW.a,NEW.b);
117 DELETE FROM t2 WHERE a < NEW.a;
118 END|
119
120 CREATE TRIGGER tr2 AFTER INSERT on t2 FOR EACH ROW
121 BEGIN
122 UPDATE t3 SET a =2, b = 'master only';
123 END|
124
125 DELIMITER ;|
126
127 --echo **** On Slave ****
128 --source include/sync_slave_sql_with_master.inc
129 source include/stop_slave.inc;
130
131 --echo **** On Master ****
132 connection master;
133 UPDATE t1 SET a = 2, b = 'master only' WHERE a = 1;
134 DROP TRIGGER tr1;
135 DROP TRIGGER tr2;
136 INSERT INTO t1 VALUES (3,'master/slave');
137 INSERT INTO t2 VALUES (3,'master/slave');
138 INSERT INTO t3 VALUES (3,'master/slave');
139 COMMIT;
140
141 SELECT * FROM t1 ORDER BY a;
142 SELECT * FROM t2 ORDER BY a;
143 SELECT * FROM t3 ORDER BY a;
144
145 save_master_pos;
146
147 --echo *** On Slave ***
148 connection slave;
149 SET GLOBAL SQL_REPLICA_SKIP_COUNTER=2;
150 source include/start_slave.inc;
151 sync_with_master;
152
153 SELECT * FROM t1 ORDER BY a;
154 SELECT * FROM t2 ORDER BY a;
155 SELECT * FROM t3 ORDER BY a;
156
157 connection master;
158 DROP TABLE t1, t2, t3;
159 --source include/sync_slave_sql_with_master.inc
160
161 --echo **** Case 3: Statement logging format and LOAD DATA with non-transactional table ****
162
163 # LOAD DATA creates two events in binary log for statement binlog format.
164 # Try to skip the first.
165
166 --echo *** On Master ***
167 connection master;
168 CREATE TABLE t10 (a INT, b VARCHAR(20)) ENGINE=myisam;
169
170 --echo *** On Slave ***
171 --source include/sync_slave_sql_with_master.inc
172 source include/stop_slave.inc;
173
174 --echo *** On Master ***
175 connection master;
176 SET SESSION BINLOG_FORMAT=STATEMENT;
177
178 --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
179 eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/std_data/rpl_bug28618.dat' INTO TABLE t10 FIELDS TERMINATED BY '|';
180
181 SELECT * FROM t10 ORDER BY a;
182
183 save_master_pos;
184
185 --echo *** On Slave ***
186 connection slave;
187 SET GLOBAL SQL_REPLICA_SKIP_COUNTER=2;
188 source include/start_slave.inc;
189 sync_with_master;
190
191 SELECT * FROM t10 ORDER BY a;
192
193 connection master;
194 DROP TABLE t10;
195 --source include/sync_slave_sql_with_master.inc
196
197 --source include/rpl_end.inc