Debian Codegraph @

mysql-test/suite/rpl/t/rpl_slave_grp_exec.test

raw
1 #############################################################
2 # Author: Serge Kozlov <[email protected]>
3 # Date: 03/21/2008
4 # Purpose:
5 # WL#3734 Test: slave group execution
6 # Testing slave group execution: stop in middle of the group
7 # (of events) should be immpossible on slave.
8 # Group of events means set of statements between BEGIN/COMMIT
9 # for transactional engines or a statement for
10 # non-transactional engines that fires triggers and
11 # affects to another tables.
12 # Implementation of slave stop in middle of the group:
13 # Delete the table on slave before start of the group
14 # on master where this table is participant of the group.
15 # So sql thread will failed inside of the group.
16 # Expected results:
17 # 1. For non-transactional engines w/o PK slave should trying
18 # to complete executing of the group.
19 # 2. For non-transactional engines slave should stop on
20 # start position of the group.
21 #############################################################
22 --source include/not_group_replication_plugin.inc
23 --source include/force_myisam_default.inc
24 --source include/have_myisam.inc
25 --source include/set_privilege_checks_user_as_system_user.inc
26 --source include/master-slave.inc
27 --echo
28
29 # Create tables and data
30 --echo *** Preparing data ***
31 --connection master
32 CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10)) ENGINE=MyISAM;
33 CREATE TABLE t2 LIKE t1;
34 CREATE TABLE t3 LIKE t1;
35
36 DELIMITER |;
37 CREATE TRIGGER tr1 BEFORE UPDATE ON t1
38 FOR EACH ROW BEGIN
39 UPDATE t2 SET b='YY' WHERE a=NEW.a;
40 END|
41 CREATE TRIGGER tr2 AFTER UPDATE ON t1
42 FOR EACH ROW BEGIN
43 UPDATE t3 SET b='ZZ' WHERE a=NEW.a;
44 END|
45 DELIMITER ;|
46 --echo
47
48 # Test non-transactional group with MyISAM tables w/o PK.
49 # Data for t1,t2 should be replicated for SBR even t3
50 # doesn't exist on slave
51 --echo *** Test non-transactional group w/o PK ***
52
53 --connection master
54 INSERT INTO t3 VALUES(1, 'AA');
55 INSERT INTO t2 VALUES(1, 'AA');
56 INSERT INTO t1 VALUES(1, 'AA');
57 --source include/sync_slave_sql_with_master.inc
58
59 RENAME TABLE t3 TO t3_bak;
60
61 --connection master
62 UPDATE t1 SET b = 'XX' WHERE a = 1;
63 SELECT * FROM t1 ORDER BY a;
64 SELECT * FROM t2 ORDER BY a;
65 SELECT * FROM t3 ORDER BY a;
66
67 --connection slave
68 call mtr.add_suppression("Replica SQL.*Table .test.t3. doesn.t exist.*, Error_code: MY-001146");
69 call mtr.add_suppression("The replica coordinator and worker threads are stopped, possibly leaving data in inconsistent state");
70
71 # in MTS case error is either of two:
72 --let $slave_sql_errno= 1146,1593
73 # whereas in the single-threaded case:
74 # 1146 = ER_NO_SUCH_TABLE
75 #--let $slave_sql_errno= 1146
76 --source include/wait_for_slave_sql_to_stop.inc
77 let $slave_sql_errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
78 if (`select $slave_sql_errno != 1146 and $slave_sql_errno != 1593`)
79 {
80 --echo Unexpected error: $slave_sql_errno
81 --die
82 }
83
84 SHOW TABLES LIKE 't%';
85 if (`SELECT @@BINLOG_FORMAT = 'ROW'`) {
86 --replace_regex /AA/AA_for_row_or_XX_for_stmt_mixed/
87 SELECT * FROM t1 ORDER BY a;
88 --replace_regex /AA/AA_for_row_or_YY_for_stmt_mixed/
89 SELECT * FROM t2 ORDER BY a;
90 }
91 if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) {
92 --replace_regex /XX/AA_for_row_or_XX_for_stmt_mixed/
93 SELECT * FROM t1 ORDER BY a;
94 --replace_regex /YY/AA_for_row_or_YY_for_stmt_mixed/
95 SELECT * FROM t2 ORDER BY a;
96 }
97
98 --source include/stop_slave_io.inc
99 RENAME TABLE t3_bak TO t3;
100 --source include/start_slave.inc
101
102 --connection master
103 TRUNCATE t1;
104 TRUNCATE t2;
105 TRUNCATE t3;
106 --source include/sync_slave_sql_with_master.inc
107 --echo
108
109
110 # Test non-transactional group with MyISAM tables and PK.
111 # No data replicated because update based on PK
112 --echo *** Test non-transactional group w/ PK ***
113
114 --connection master
115 ALTER TABLE t1 ADD PRIMARY KEY (a);
116 ALTER TABLE t2 ADD PRIMARY KEY (a);
117 ALTER TABLE t3 ADD PRIMARY KEY (a);
118 --source include/sync_slave_sql_with_master.inc
119 RENAME TABLE t3 TO t3_bak;
120
121 if (`select @@GLOBAL.binlog_transaction_dependency_tracking = 'WRITESET'`)
122 {
123 --disable_query_log
124 SET @saved_replica_preserve_commit_order= @@GLOBAL.replica_preserve_commit_order;
125 STOP REPLICA SQL_THREAD;
126 # Set replica_preserve_commit_order to ON to make the following
127 # INSERT statements execute in commit order to make the following
128 # SELECT statements output the consistent result even when
129 # binlog_transaction_dependency_tracking is set to WRITESET.
130 SET GLOBAL replica_preserve_commit_order= ON;
131 START REPLICA SQL_THREAD;
132 --enable_query_log
133 }
134
135 --connection master
136 INSERT INTO t3 VALUES(2, 'B');
137 INSERT INTO t2 VALUES(2, 'B');
138 INSERT INTO t1 VALUES(2, 'B');
139 UPDATE t1 SET b = 'X' WHERE a = 2;
140
141 --connection slave
142 # 1146 = ER_NO_SUCH_TABLE
143 # in MTS case error is either of two:
144 #--let $slave_sql_errno= 1146,1593
145 # whereas in the single-threaded case:
146 #--let $slave_sql_errno= 1146
147
148 --source include/wait_for_slave_sql_to_stop.inc
149 let $slave_sql_errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
150 if (`select $slave_sql_errno != 1146 and $slave_sql_errno != 1593`)
151 {
152 --echo Unexpected error: $slave_sql_errno
153 --die
154 }
155
156 --connection master
157 SELECT * FROM t1 ORDER BY a;
158 SELECT * FROM t2 ORDER BY a;
159 SELECT * FROM t3 ORDER BY a;
160
161 --connection slave
162 SHOW TABLES LIKE 't%';
163 SELECT * FROM t1 ORDER BY a;
164 SELECT * FROM t2 ORDER BY a;
165
166 if (`select @@GLOBAL.binlog_transaction_dependency_tracking = 'WRITESET'`)
167 {
168 --disable_query_log
169 SET GLOBAL replica_preserve_commit_order= @saved_replica_preserve_commit_order;
170 --enable_query_log
171 }
172
173 --source include/stop_slave_io.inc
174 RENAME TABLE t3_bak TO t3;
175
176 # TODO: recovery. Alfranio it fails to recover here.
177 --source include/start_slave.inc
178
179 --connection master
180 TRUNCATE t1;
181 TRUNCATE t2;
182 TRUNCATE t3;
183 --source include/sync_slave_sql_with_master.inc
184 --echo
185
186
187 # Test transactional group with InnoDB tables with PK
188 # No data replicated if errors happens inside transaction
189 --echo *** Test transactional group w/ PK ***
190
191 --connection master
192 ALTER TABLE t1 ENGINE=InnoDB;
193 ALTER TABLE t2 ENGINE=InnoDB;
194 ALTER TABLE t3 ENGINE=InnoDB;
195 --source include/sync_slave_sql_with_master.inc
196
197 --connection slave
198 RENAME TABLE t3 TO t3_bak;
199
200 --connection master
201 BEGIN;
202 INSERT INTO t1 VALUES (3, 'C'), (4, 'D');
203 INSERT INTO t2 VALUES (3, 'C'), (4, 'D');
204 INSERT INTO t3 VALUES (3, 'C'), (4, 'D');
205 COMMIT;
206
207 --connection slave
208 # 1146 = ER_NO_SUCH_TABLE
209 # in MTS case error is either of two:
210 #--let $slave_sql_errno= 1146,1593
211 # whereas in the single-threaded case:
212 #--let $slave_sql_errno= 1146
213
214 --source include/wait_for_slave_sql_to_stop.inc
215 let $slave_sql_errno= query_get_value(SHOW SLAVE STATUS, Last_SQL_Errno, 1);
216 if (`select $slave_sql_errno != 1146 and $slave_sql_errno != 1593`)
217 {
218 --echo Unexpected error: $slave_sql_errno
219 --die
220 }
221
222
223 --connection master
224 SELECT * FROM t1 ORDER BY a;
225 SELECT * FROM t2 ORDER BY a;
226 SELECT * FROM t3 ORDER BY a;
227
228 --connection slave
229 SHOW TABLES LIKE 't%';
230 SELECT * FROM t1 ORDER BY a;
231 SELECT * FROM t2 ORDER BY a;
232
233 source include/stop_slave_io.inc;
234 RENAME TABLE t3_bak TO t3;
235 source include/start_slave.inc;
236
237 # Clean up
238 --echo *** Clean up ***
239 --connection master
240 DROP TABLE t1,t2,t3;
241 --source include/sync_slave_sql_with_master.inc
242
243 # End of 5.1 test
244 --source include/rpl_end.inc