3
# Test replication of transactions on tables which have different
4
# engines on master and slave. This tests all combinations of innodb,
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,
13
# - committed and rollback'ed transactions, with autocommit on and
15
# - non-transactions with autocommit on
16
# - non-transactions with autocommit off, where the master table is
19
# Note: we are running the slave with
20
# --replicate-ignore-table=mysql.ndb_apply_status . See BUG#34557 for
23
# ==== Related bugs ====
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
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.
43
--echo ==== Initialization ====
45
--source include/have_ndb.inc
46
--source include/have_innodb.inc
47
--source include/ndb_master-slave.inc
49
--echo ---- setup master ----
51
CREATE TABLE myisam_innodb (a INT) ENGINE=MYISAM;
52
CREATE TABLE innodb_myisam (a INT) ENGINE=INNODB;
53
CREATE TABLE myisam_ndb (a INT) ENGINE=MYISAM;
54
CREATE TABLE ndb_myisam (a INT) ENGINE=NDB;
55
CREATE TABLE innodb_ndb (a INT) ENGINE=INNODB;
56
CREATE TABLE ndb_innodb (a INT) ENGINE=NDB;
58
SHOW CREATE TABLE myisam_innodb;
59
SHOW CREATE TABLE innodb_myisam;
60
SHOW CREATE TABLE myisam_ndb;
61
SHOW CREATE TABLE ndb_myisam;
62
SHOW CREATE TABLE innodb_ndb;
63
SHOW CREATE TABLE ndb_innodb;
65
--echo ---- setup slave with different engines ----
67
sync_slave_with_master;
69
DROP TABLE myisam_innodb, innodb_myisam;
70
DROP TABLE myisam_ndb, ndb_myisam;
71
DROP TABLE innodb_ndb, ndb_innodb;
73
CREATE TABLE myisam_innodb (a INT) ENGINE=INNODB;
74
CREATE TABLE innodb_myisam (a INT) ENGINE=MYISAM;
75
CREATE TABLE myisam_ndb (a INT) ENGINE=NDB;
76
CREATE TABLE ndb_myisam (a INT) ENGINE=MYISAM;
77
CREATE TABLE innodb_ndb (a INT) ENGINE=NDB;
78
CREATE TABLE ndb_innodb (a INT) ENGINE=INNODB;
80
SHOW CREATE TABLE myisam_innodb;
81
SHOW CREATE TABLE innodb_myisam;
82
SHOW CREATE TABLE myisam_ndb;
83
SHOW CREATE TABLE ndb_myisam;
84
SHOW CREATE TABLE innodb_ndb;
85
SHOW CREATE TABLE ndb_innodb;
90
--echo ==== AUTOCOMMIT=0, transactions ====
92
--echo ---- COMMIT ----
97
INSERT INTO myisam_innodb VALUES (1);
98
INSERT INTO myisam_innodb VALUES (2);
100
sync_slave_with_master;
103
INSERT INTO innodb_myisam VALUES (3);
104
INSERT INTO innodb_myisam VALUES (4);
106
sync_slave_with_master;
110
INSERT INTO myisam_ndb VALUES (5);
111
INSERT INTO myisam_ndb VALUES (6);
113
sync_slave_with_master;
116
INSERT INTO ndb_myisam VALUES (7);
117
INSERT INTO ndb_myisam VALUES (8);
119
sync_slave_with_master;
123
INSERT INTO ndb_innodb VALUES (9);
124
INSERT INTO ndb_innodb VALUES (10);
126
sync_slave_with_master;
129
INSERT INTO innodb_ndb VALUES (11);
130
INSERT INTO innodb_ndb VALUES (12);
132
sync_slave_with_master;
135
--echo ---- ROLLBACK ----
138
INSERT INTO myisam_innodb VALUES (13);
139
INSERT INTO myisam_innodb VALUES (14);
141
sync_slave_with_master;
144
INSERT INTO innodb_myisam VALUES (15);
145
INSERT INTO innodb_myisam VALUES (16);
147
sync_slave_with_master;
151
INSERT INTO myisam_ndb VALUES (17);
152
INSERT INTO myisam_ndb VALUES (18);
154
sync_slave_with_master;
157
INSERT INTO ndb_myisam VALUES (19);
158
INSERT INTO ndb_myisam VALUES (20);
160
sync_slave_with_master;
164
INSERT INTO ndb_innodb VALUES (21);
165
INSERT INTO ndb_innodb VALUES (22);
167
sync_slave_with_master;
170
INSERT INTO innodb_ndb VALUES (23);
171
INSERT INTO innodb_ndb VALUES (24);
173
sync_slave_with_master;
177
--echo ==== AUTOCOMMIT=1, transactions ====
179
--echo ---- COMMIT ----
184
INSERT INTO myisam_innodb VALUES (25);
185
INSERT INTO myisam_innodb VALUES (26);
187
sync_slave_with_master;
190
INSERT INTO innodb_myisam VALUES (27);
191
INSERT INTO innodb_myisam VALUES (28);
193
sync_slave_with_master;
197
INSERT INTO myisam_ndb VALUES (29);
198
INSERT INTO myisam_ndb VALUES (30);
200
sync_slave_with_master;
203
INSERT INTO ndb_myisam VALUES (31);
204
INSERT INTO ndb_myisam VALUES (32);
206
sync_slave_with_master;
210
INSERT INTO ndb_innodb VALUES (33);
211
INSERT INTO ndb_innodb VALUES (34);
213
sync_slave_with_master;
216
INSERT INTO innodb_ndb VALUES (35);
217
INSERT INTO innodb_ndb VALUES (36);
219
sync_slave_with_master;
222
--echo ---- ROLLBACK ----
225
INSERT INTO myisam_innodb VALUES (37);
226
INSERT INTO myisam_innodb VALUES (38);
228
sync_slave_with_master;
231
INSERT INTO innodb_myisam VALUES (39);
232
INSERT INTO innodb_myisam VALUES (40);
234
sync_slave_with_master;
238
INSERT INTO myisam_ndb VALUES (41);
239
INSERT INTO myisam_ndb VALUES (42);
241
sync_slave_with_master;
244
INSERT INTO ndb_myisam VALUES (43);
245
INSERT INTO ndb_myisam VALUES (44);
247
sync_slave_with_master;
251
INSERT INTO ndb_innodb VALUES (45);
252
INSERT INTO ndb_innodb VALUES (46);
254
sync_slave_with_master;
257
INSERT INTO innodb_ndb VALUES (47);
258
INSERT INTO innodb_ndb VALUES (48);
260
sync_slave_with_master;
264
--echo ==== AUTOCOMMIT=1, single statements ====
266
INSERT INTO myisam_innodb VALUES (49);
267
INSERT INTO myisam_innodb VALUES (50);
268
sync_slave_with_master;
270
INSERT INTO innodb_myisam VALUES (51);
271
INSERT INTO innodb_myisam VALUES (52);
272
sync_slave_with_master;
275
INSERT INTO myisam_ndb VALUES (53);
276
INSERT INTO myisam_ndb VALUES (54);
277
sync_slave_with_master;
279
INSERT INTO ndb_myisam VALUES (55);
280
INSERT INTO ndb_myisam VALUES (56);
281
sync_slave_with_master;
284
INSERT INTO ndb_innodb VALUES (57);
285
INSERT INTO ndb_innodb VALUES (58);
286
sync_slave_with_master;
288
INSERT INTO innodb_ndb VALUES (59);
289
INSERT INTO innodb_ndb VALUES (60);
290
sync_slave_with_master;
294
--echo ==== AUTOCOMMIT=0, single statements, myisam on master ====
298
# This tests BUG#29288.
299
INSERT INTO myisam_innodb VALUES (61);
300
INSERT INTO myisam_innodb VALUES (62);
301
sync_slave_with_master;
304
INSERT INTO myisam_ndb VALUES (63);
305
INSERT INTO myisam_ndb VALUES (64);
306
sync_slave_with_master;
310
--echo ==== Show results ====
312
SELECT * FROM myisam_innodb ORDER BY a;
313
SELECT * FROM innodb_myisam ORDER BY a;
314
SELECT * FROM myisam_ndb ORDER BY a;
315
SELECT * FROM ndb_myisam ORDER BY a;
316
SELECT * FROM innodb_ndb ORDER BY a;
317
SELECT * FROM ndb_innodb ORDER BY a;
319
let $diff_table_1=master:test.myisam_innodb;
320
let $diff_table_2=slave:test.myisam_innodb;
321
source include/diff_tables.inc;
323
let $diff_table_1=master:test.innodb_myisam;
324
let $diff_table_2=slave:test.innodb_myisam;
325
source include/diff_tables.inc;
327
let $diff_table_1=master:test.myisam_ndb;
328
let $diff_table_2=slave:test.myisam_ndb;
329
source include/diff_tables.inc;
331
let $diff_table_1=master:test.ndb_myisam;
332
let $diff_table_2=slave:test.ndb_myisam;
333
source include/diff_tables.inc;
335
let $diff_table_1=master:test.innodb_ndb;
336
let $diff_table_2=slave:test.innodb_ndb;
337
source include/diff_tables.inc;
339
let $diff_table_1=master:test.ndb_innodb;
340
let $diff_table_2=slave:test.ndb_innodb;
341
source include/diff_tables.inc;
344
--echo ==== Clean up ====
346
drop table myisam_innodb, innodb_myisam;
347
drop table myisam_ndb, ndb_myisam;
348
drop table innodb_ndb, ndb_innodb;
349
sync_slave_with_master;