Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_mixed_tables.test

raw
1 # ==== Purpose ====
2 #
3 # Test replication of transactions on tables which have different
4 # engines on master and slave. This tests all combinations of innodb,
5 # myisam, and ndb.
6 #
7 # ==== Method ====
8 #
9 # Set up six tables, each being innodb, myisam, or innodb on master,
10 # and another of innodb, myisam, or innodb on slave. For each table,
11 # do the following:
12 #
13 # - committed and rollback'ed transactions, with autocommit on and
14 # off
15 # - non-transactions with autocommit on
16 # - non-transactions with autocommit off, where the master table is
17 # myisam.
18 #
19 # Note: we are running the slave with
20 # --replicate-ignore-table=mysql.ndb_apply_status . See BUG#34557 for
21 # explanation.
22 #
23 # ==== Related bugs ====
24 #
25 # BUG#26395: if crash during autocommit update to transactional table on master, slave fails
26 # BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable
27 # BUG#34557: Row-based replication from ndb to non-ndb gives error on slave
28 # BUG#34600: Rolled-back punch transactions not replicated correctly
29 #
30 # ==== Todo ====
31 #
32 # We should eventually try transactions touching two tables which are
33 # of different engines on the same server (so that we try, e.g. punch
34 # transactions; cf BUG#34600). However, that will make the test much
35 # bigger (9 master-slave engine combinations [myisam->myisam,
36 # myisam->ndb, etc]. To try all combinations of one or more such
37 # tables means 2^9-1=511 transactions. We need to multiplied by 5
38 # since we want to test committed/rollback'ed transactions
39 # with/without AUTOCOMMIT, as well as non-transactions with
40 # autocommit). We'd have to write a script to produce the test case.
41
42
43 --echo ==== Initialization ====
44
45 --source include/have_ndb.inc
46 --source suite/ndb_rpl/ndb_master-slave.inc
47
48 --echo ---- setup master ----
49
50 CREATE TABLE myisam_innodb (a INT) ENGINE=MYISAM;
51 CREATE TABLE innodb_myisam (a INT) ENGINE=INNODB;
52 CREATE TABLE myisam_ndb (a INT) ENGINE=MYISAM;
53 CREATE TABLE ndb_myisam (a INT) ENGINE=NDB;
54 CREATE TABLE innodb_ndb (a INT) ENGINE=INNODB;
55 CREATE TABLE ndb_innodb (a INT) ENGINE=NDB;
56
57 SHOW CREATE TABLE myisam_innodb;
58 SHOW CREATE TABLE innodb_myisam;
59 SHOW CREATE TABLE myisam_ndb;
60 --disable_warnings
61 SHOW CREATE TABLE ndb_myisam;
62 --enable_warnings
63 SHOW CREATE TABLE innodb_ndb;
64 --disable_warnings
65 SHOW CREATE TABLE ndb_innodb;
66 --enable_warnings
67
68 --echo ---- setup slave with different engines ----
69
70 sync_slave_with_master;
71
72 DROP TABLE myisam_innodb, innodb_myisam;
73 DROP TABLE myisam_ndb, ndb_myisam;
74 DROP TABLE innodb_ndb, ndb_innodb;
75
76 CREATE TABLE myisam_innodb (a INT) ENGINE=INNODB;
77 CREATE TABLE innodb_myisam (a INT) ENGINE=MYISAM;
78 CREATE TABLE myisam_ndb (a INT) ENGINE=NDB;
79 CREATE TABLE ndb_myisam (a INT) ENGINE=MYISAM;
80 CREATE TABLE innodb_ndb (a INT) ENGINE=NDB;
81 CREATE TABLE ndb_innodb (a INT) ENGINE=INNODB;
82
83 SHOW CREATE TABLE myisam_innodb;
84 SHOW CREATE TABLE innodb_myisam;
85 --disable_warnings
86 SHOW CREATE TABLE myisam_ndb;
87 --enable_warnings
88 SHOW CREATE TABLE ndb_myisam;
89 --disable_warnings
90 SHOW CREATE TABLE innodb_ndb;
91 --enable_warnings
92 SHOW CREATE TABLE ndb_innodb;
93
94 connection master;
95
96
97 --echo ==== AUTOCOMMIT=0, transactions ====
98
99 --echo ---- COMMIT ----
100
101 SET AUTOCOMMIT = 0;
102
103 BEGIN;
104 INSERT INTO myisam_innodb VALUES (1);
105 INSERT INTO myisam_innodb VALUES (2);
106 COMMIT;
107 sync_slave_with_master;
108 connection master;
109 BEGIN;
110 INSERT INTO innodb_myisam VALUES (3);
111 INSERT INTO innodb_myisam VALUES (4);
112 COMMIT;
113 sync_slave_with_master;
114 connection master;
115
116 BEGIN;
117 INSERT INTO myisam_ndb VALUES (5);
118 INSERT INTO myisam_ndb VALUES (6);
119 COMMIT;
120 sync_slave_with_master;
121 connection master;
122 BEGIN;
123 INSERT INTO ndb_myisam VALUES (7);
124 INSERT INTO ndb_myisam VALUES (8);
125 COMMIT;
126 sync_slave_with_master;
127 connection master;
128
129 BEGIN;
130 INSERT INTO ndb_innodb VALUES (9);
131 INSERT INTO ndb_innodb VALUES (10);
132 COMMIT;
133 sync_slave_with_master;
134 connection master;
135 BEGIN;
136 INSERT INTO innodb_ndb VALUES (11);
137 INSERT INTO innodb_ndb VALUES (12);
138 COMMIT;
139 sync_slave_with_master;
140 connection master;
141
142 --echo ---- ROLLBACK ----
143
144 BEGIN;
145 INSERT INTO myisam_innodb VALUES (13);
146 INSERT INTO myisam_innodb VALUES (14);
147 ROLLBACK;
148 sync_slave_with_master;
149 connection master;
150 BEGIN;
151 INSERT INTO innodb_myisam VALUES (15);
152 INSERT INTO innodb_myisam VALUES (16);
153 ROLLBACK;
154 sync_slave_with_master;
155 connection master;
156
157 BEGIN;
158 INSERT INTO myisam_ndb VALUES (17);
159 INSERT INTO myisam_ndb VALUES (18);
160 ROLLBACK;
161 sync_slave_with_master;
162 connection master;
163 BEGIN;
164 INSERT INTO ndb_myisam VALUES (19);
165 INSERT INTO ndb_myisam VALUES (20);
166 ROLLBACK;
167 sync_slave_with_master;
168 connection master;
169
170 BEGIN;
171 INSERT INTO ndb_innodb VALUES (21);
172 INSERT INTO ndb_innodb VALUES (22);
173 ROLLBACK;
174 sync_slave_with_master;
175 connection master;
176 BEGIN;
177 INSERT INTO innodb_ndb VALUES (23);
178 INSERT INTO innodb_ndb VALUES (24);
179 ROLLBACK;
180 sync_slave_with_master;
181 connection master;
182
183
184 --echo ==== AUTOCOMMIT=1, transactions ====
185
186 --echo ---- COMMIT ----
187
188 SET AUTOCOMMIT = 1;
189
190 BEGIN;
191 INSERT INTO myisam_innodb VALUES (25);
192 INSERT INTO myisam_innodb VALUES (26);
193 COMMIT;
194 sync_slave_with_master;
195 connection master;
196 BEGIN;
197 INSERT INTO innodb_myisam VALUES (27);
198 INSERT INTO innodb_myisam VALUES (28);
199 COMMIT;
200 sync_slave_with_master;
201 connection master;
202
203 BEGIN;
204 INSERT INTO myisam_ndb VALUES (29);
205 INSERT INTO myisam_ndb VALUES (30);
206 COMMIT;
207 sync_slave_with_master;
208 connection master;
209 BEGIN;
210 INSERT INTO ndb_myisam VALUES (31);
211 INSERT INTO ndb_myisam VALUES (32);
212 COMMIT;
213 sync_slave_with_master;
214 connection master;
215
216 BEGIN;
217 INSERT INTO ndb_innodb VALUES (33);
218 INSERT INTO ndb_innodb VALUES (34);
219 COMMIT;
220 sync_slave_with_master;
221 connection master;
222 BEGIN;
223 INSERT INTO innodb_ndb VALUES (35);
224 INSERT INTO innodb_ndb VALUES (36);
225 COMMIT;
226 sync_slave_with_master;
227 connection master;
228
229 --echo ---- ROLLBACK ----
230
231 BEGIN;
232 INSERT INTO myisam_innodb VALUES (37);
233 INSERT INTO myisam_innodb VALUES (38);
234 ROLLBACK;
235 sync_slave_with_master;
236 connection master;
237 BEGIN;
238 INSERT INTO innodb_myisam VALUES (39);
239 INSERT INTO innodb_myisam VALUES (40);
240 ROLLBACK;
241 sync_slave_with_master;
242 connection master;
243
244 BEGIN;
245 INSERT INTO myisam_ndb VALUES (41);
246 INSERT INTO myisam_ndb VALUES (42);
247 ROLLBACK;
248 sync_slave_with_master;
249 connection master;
250 BEGIN;
251 INSERT INTO ndb_myisam VALUES (43);
252 INSERT INTO ndb_myisam VALUES (44);
253 ROLLBACK;
254 sync_slave_with_master;
255 connection master;
256
257 BEGIN;
258 INSERT INTO ndb_innodb VALUES (45);
259 INSERT INTO ndb_innodb VALUES (46);
260 ROLLBACK;
261 sync_slave_with_master;
262 connection master;
263 BEGIN;
264 INSERT INTO innodb_ndb VALUES (47);
265 INSERT INTO innodb_ndb VALUES (48);
266 ROLLBACK;
267 sync_slave_with_master;
268 connection master;
269
270
271 --echo ==== AUTOCOMMIT=1, single statements ====
272
273 INSERT INTO myisam_innodb VALUES (49);
274 INSERT INTO myisam_innodb VALUES (50);
275 sync_slave_with_master;
276 connection master;
277 INSERT INTO innodb_myisam VALUES (51);
278 INSERT INTO innodb_myisam VALUES (52);
279 sync_slave_with_master;
280 connection master;
281
282 INSERT INTO myisam_ndb VALUES (53);
283 INSERT INTO myisam_ndb VALUES (54);
284 sync_slave_with_master;
285 connection master;
286 INSERT INTO ndb_myisam VALUES (55);
287 INSERT INTO ndb_myisam VALUES (56);
288 sync_slave_with_master;
289 connection master;
290
291 INSERT INTO ndb_innodb VALUES (57);
292 INSERT INTO ndb_innodb VALUES (58);
293 sync_slave_with_master;
294 connection master;
295 INSERT INTO innodb_ndb VALUES (59);
296 INSERT INTO innodb_ndb VALUES (60);
297 sync_slave_with_master;
298 connection master;
299
300
301 --echo ==== AUTOCOMMIT=0, single statements, myisam on master ====
302
303 SET AUTOCOMMIT = 0;
304
305 # This tests BUG#29288.
306 INSERT INTO myisam_innodb VALUES (61);
307 INSERT INTO myisam_innodb VALUES (62);
308 sync_slave_with_master;
309 connection master;
310
311 INSERT INTO myisam_ndb VALUES (63);
312 INSERT INTO myisam_ndb VALUES (64);
313 sync_slave_with_master;
314 connection master;
315
316
317 --echo ==== Show results ====
318
319 SELECT * FROM myisam_innodb ORDER BY a;
320 SELECT * FROM innodb_myisam ORDER BY a;
321 SELECT * FROM myisam_ndb ORDER BY a;
322 SELECT * FROM ndb_myisam ORDER BY a;
323 SELECT * FROM innodb_ndb ORDER BY a;
324 SELECT * FROM ndb_innodb ORDER BY a;
325
326 let $diff_tables= master:myisam_innodb, slave:myisam_innodb;
327 source include/diff_tables.inc;
328
329 let $diff_tables= master:innodb_myisam, slave:innodb_myisam;
330 source include/diff_tables.inc;
331
332 let $diff_tables= master:myisam_ndb, slave:myisam_ndb;
333 source include/diff_tables.inc;
334
335 let $diff_tables= master:ndb_myisam, slave:ndb_myisam;
336 source include/diff_tables.inc;
337
338 let $diff_tables= master:innodb_ndb, slave:innodb_ndb;
339 source include/diff_tables.inc;
340
341 let $diff_tables= master:ndb_innodb, slave:ndb_innodb;
342 source include/diff_tables.inc;
343
344
345 --echo ==== Clean up ====
346
347 drop table myisam_innodb, innodb_myisam;
348 drop table myisam_ndb, ndb_myisam;
349 drop table innodb_ndb, ndb_innodb;
350 sync_slave_with_master;
351 --source include/rpl_end.inc