path: root/mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_engines_transactions.test

# ==== Purpose ====
#
# Tests that transactions containing multiple table types are
# replicated correctly to the slave.
#
# This test was previously part of rpl_ndb_transactions.
#
#
# ==== Method ====
#
# Try all combinations of the following:
#  - Committed/rollback transactions.
#  - Transactions started by AUTOCOMMIT = 0 or BEGIN.
#  - Transactions using myisam, innodb, or ndb tables, or combinations
#    of them. For combinations, we use the engines in all possible
#    orders.
# For single-engine transactions, we also try with AUTOCOMMIT = 1.
#
#
# ==== Related bugs ====
#
# BUG#26395: if crash during autocommit update to transactional table on master, slave fails


source include/have_ndb.inc;
# Since the master generates row-based events, the slave must be in
# ROW or MIXED mode to accept the events.
source include/have_binlog_format_mixed_or_row.inc;
source include/ndb_master-slave.inc;
source include/have_innodb.inc;

CREATE TABLE tmyisam (a int) ENGINE = MYISAM;
CREATE TABLE tinnodb (a int) ENGINE = INNODB;
CREATE TABLE tndb (a int) ENGINE = NDB;

SHOW CREATE TABLE tmyisam;
SHOW CREATE TABLE tinnodb;
SHOW CREATE TABLE tndb;


--echo [on master]


--echo ==== Single-engine transactions ====

--echo ---- autocommitted ----

SET AUTOCOMMIT = 1;

INSERT INTO tmyisam VALUES (0);
INSERT INTO tinnodb VALUES (1);
INSERT INTO tndb VALUES (2);

--echo ---- committed with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (3);
INSERT INTO tmyisam VALUES (4);
COMMIT;

BEGIN;
INSERT INTO tinnodb VALUES (5);
INSERT INTO tinnodb VALUES (6);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (7);
INSERT INTO tndb VALUES (8);
COMMIT;

--echo ---- rolled back with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (9);
INSERT INTO tmyisam VALUES (10);
ROLLBACK;

BEGIN;
INSERT INTO tinnodb VALUES (11);
INSERT INTO tinnodb VALUES (12);
ROLLBACK;

BEGIN;
INSERT INTO tndb VALUES (13);
INSERT INTO tndb VALUES (14);
ROLLBACK;


--echo ---- committed with AUTOCOMMIT = 0 ----

SET AUTOCOMMIT = 0;

INSERT INTO tmyisam VALUES (15);
INSERT INTO tmyisam VALUES (16);
COMMIT;

INSERT INTO tinnodb VALUES (17);
INSERT INTO tinnodb VALUES (18);
COMMIT;

INSERT INTO tndb VALUES (19);
INSERT INTO tndb VALUES (20);
COMMIT;

--echo ---- rolled back with AUTOCOMMIT = 0 ----

INSERT INTO tmyisam VALUES (21);
INSERT INTO tmyisam VALUES (22);
ROLLBACK;

INSERT INTO tinnodb VALUES (23);
INSERT INTO tinnodb VALUES (24);
ROLLBACK;

INSERT INTO tndb VALUES (25);
INSERT INTO tndb VALUES (26);
ROLLBACK;

SET AUTOCOMMIT = 1;


--echo ==== MyISAM + InnoDB ====

--echo ---- committed with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (27);
INSERT INTO tinnodb VALUES (28);
COMMIT;

BEGIN;
INSERT INTO tinnodb VALUES (29);
INSERT INTO tmyisam VALUES (30);
COMMIT;

--echo ---- rolled back with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (31);
INSERT INTO tinnodb VALUES (32);
ROLLBACK;

BEGIN;
INSERT INTO tinnodb VALUES (33);
INSERT INTO tmyisam VALUES (34);
ROLLBACK;

--echo ---- committed with AUTOCOMMIT = 0 ----

SET AUTOCOMMIT = 0;

INSERT INTO tmyisam VALUES (35);
INSERT INTO tinnodb VALUES (36);
COMMIT;

INSERT INTO tinnodb VALUES (37);
INSERT INTO tmyisam VALUES (38);
COMMIT;

--echo ---- rolled back with AUTOCOMMIT = 0 ----

INSERT INTO tmyisam VALUES (39);
INSERT INTO tinnodb VALUES (40);
ROLLBACK;

INSERT INTO tinnodb VALUES (41);
INSERT INTO tmyisam VALUES (42);
ROLLBACK;

SET AUTOCOMMIT = 1;


--echo ==== MyISAM + NDB ====

--echo ---- committed with BEGIN----

BEGIN;
INSERT INTO tmyisam VALUES (43);
INSERT INTO tndb VALUES (44);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (45);
INSERT INTO tmyisam VALUES (46);
COMMIT;

--echo ---- rolled back with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (47);
INSERT INTO tndb VALUES (48);
ROLLBACK;

BEGIN;
INSERT INTO tndb VALUES (49);
INSERT INTO tmyisam VALUES (50);
ROLLBACK;

--echo ---- committed with AUTOCOMMIT = 0 ----

SET AUTOCOMMIT = 0;

INSERT INTO tmyisam VALUES (51);
INSERT INTO tndb VALUES (52);
COMMIT;

INSERT INTO tndb VALUES (53);
INSERT INTO tmyisam VALUES (54);
COMMIT;

--echo ---- rolled back with AUTOCOMMIT = 0 ----

INSERT INTO tmyisam VALUES (55);
INSERT INTO tndb VALUES (56);
ROLLBACK;

INSERT INTO tndb VALUES (57);
INSERT INTO tmyisam VALUES (58);
ROLLBACK;

SET AUTOCOMMIT = 1;


--echo ==== InnoDB + NDB ====

--echo ---- committed with BEGIN ----

BEGIN;
INSERT INTO tinnodb VALUES (59);
INSERT INTO tndb VALUES (60);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (61);
INSERT INTO tinnodb VALUES (62);
COMMIT;

--echo ---- rolled back with BEGIN ----

BEGIN;
INSERT INTO tinnodb VALUES (63);
INSERT INTO tndb VALUES (64);
ROLLBACK;

BEGIN;
INSERT INTO tndb VALUES (65);
INSERT INTO tinnodb VALUES (66);
ROLLBACK;

--echo ---- committed with AUTOCOMMIT = 0 ----

SET AUTOCOMMIT = 0;

INSERT INTO tinnodb VALUES (67);
INSERT INTO tndb VALUES (68);
COMMIT;

INSERT INTO tndb VALUES (69);
INSERT INTO tinnodb VALUES (70);
COMMIT;

--echo ---- rolled back with AUTOCOMMIT = 0 ----

INSERT INTO tinnodb VALUES (71);
INSERT INTO tndb VALUES (72);
ROLLBACK;

INSERT INTO tndb VALUES (73);
INSERT INTO tinnodb VALUES (74);
ROLLBACK;

SET AUTOCOMMIT = 1;


--echo ==== MyISAM + InnoDB + NDB ====

--echo ---- committed with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (75);
INSERT INTO tinnodb VALUES (76);
INSERT INTO tndb VALUES (77);
COMMIT;

BEGIN;
INSERT INTO tmyisam VALUES (78);
INSERT INTO tndb VALUES (79);
INSERT INTO tinnodb VALUES (80);
COMMIT;

BEGIN;
INSERT INTO tinnodb VALUES (81);
INSERT INTO tmyisam VALUES (82);
INSERT INTO tndb VALUES (83);
COMMIT;

BEGIN;
INSERT INTO tinnodb VALUES (84);
INSERT INTO tndb VALUES (85);
INSERT INTO tmyisam VALUES (86);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (87);
INSERT INTO tmyisam VALUES (88);
INSERT INTO tinnodb VALUES (89);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (90);
INSERT INTO tinnodb VALUES (91);
INSERT INTO tmyisam VALUES (92);
COMMIT;

--echo ---- rolled back with BEGIN ----

BEGIN;
INSERT INTO tmyisam VALUES (93);
INSERT INTO tinnodb VALUES (94);
INSERT INTO tndb VALUES (95);
ROLLBACK;

BEGIN;
INSERT INTO tmyisam VALUES (96);
INSERT INTO tndb VALUES (97);
INSERT INTO tinnodb VALUES (98);
ROLLBACK;

BEGIN;
INSERT INTO tinnodb VALUES (99);
INSERT INTO tmyisam VALUES (100);
INSERT INTO tndb VALUES (101);
ROLLBACK;

BEGIN;
INSERT INTO tinnodb VALUES (102);
INSERT INTO tndb VALUES (103);
INSERT INTO tmyisam VALUES (104);
ROLLBACK;

BEGIN;
INSERT INTO tndb VALUES (105);
INSERT INTO tmyisam VALUES (106);
INSERT INTO tinnodb VALUES (107);
ROLLBACK;

BEGIN;
INSERT INTO tndb VALUES (108);
INSERT INTO tinnodb VALUES (109);
INSERT INTO tmyisam VALUES (110);
ROLLBACK;

--echo ---- committed with AUTOCOMMIT = 0 ----

SET AUTOCOMMIT = 0;

INSERT INTO tmyisam VALUES (111);
INSERT INTO tinnodb VALUES (112);
INSERT INTO tndb VALUES (113);
COMMIT;

INSERT INTO tmyisam VALUES (114);
INSERT INTO tndb VALUES (115);
INSERT INTO tinnodb VALUES (116);
COMMIT;

INSERT INTO tinnodb VALUES (117);
INSERT INTO tmyisam VALUES (118);
INSERT INTO tndb VALUES (119);
COMMIT;

INSERT INTO tinnodb VALUES (120);
INSERT INTO tndb VALUES (121);
INSERT INTO tmyisam VALUES (122);
COMMIT;

INSERT INTO tndb VALUES (123);
INSERT INTO tmyisam VALUES (124);
INSERT INTO tinnodb VALUES (125);
COMMIT;

INSERT INTO tndb VALUES (126);
INSERT INTO tinnodb VALUES (127);
INSERT INTO tmyisam VALUES (128);
COMMIT;

--echo ---- rolled back with AUTOCOMMIT = 0 ----

INSERT INTO tmyisam VALUES (129);
INSERT INTO tinnodb VALUES (130);
INSERT INTO tndb VALUES (131);
ROLLBACK;

INSERT INTO tmyisam VALUES (132);
INSERT INTO tndb VALUES (133);
INSERT INTO tinnodb VALUES (134);
ROLLBACK;

INSERT INTO tinnodb VALUES (135);
INSERT INTO tmyisam VALUES (136);
INSERT INTO tndb VALUES (137);
ROLLBACK;

INSERT INTO tinnodb VALUES (138);
INSERT INTO tndb VALUES (139);
INSERT INTO tmyisam VALUES (140);
ROLLBACK;

INSERT INTO tndb VALUES (141);
INSERT INTO tmyisam VALUES (142);
INSERT INTO tinnodb VALUES (143);
ROLLBACK;

INSERT INTO tndb VALUES (144);
INSERT INTO tinnodb VALUES (145);
INSERT INTO tmyisam VALUES (146);
ROLLBACK;

SET AUTOCOMMIT = 1;

--echo ---- Mixed statements Innodb ----

BEGIN;
INSERT INTO tndb VALUES (147);
INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
COMMIT;

INSERT INTO tndb VALUES (148);
BEGIN;
INSERT INTO tinnodb SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
INSERT INTO tndb VALUES (149);
COMMIT;

BEGIN;
INSERT INTO tndb VALUES (150);
INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
COMMIT;

INSERT INTO tndb VALUES (151);
BEGIN;
INSERT INTO tmyisam SELECT * FROM tndb ORDER BY a DESC LIMIT 1;
INSERT INTO tndb VALUES (152);
COMMIT;

--echo ==== Verify the result ====

SELECT * FROM tmyisam ORDER BY a;
SELECT * FROM tinnodb ORDER BY a;
SELECT * FROM tndb ORDER BY a;

--echo [on slave]
--sync_slave_with_master

let $diff_table_1=master:test.tmyisam;
let $diff_table_2=slave:test.tmyisam;
source include/diff_tables.inc;

let $diff_table_1=master:test.tinnodb;
let $diff_table_2=slave:test.tinnodb;
source include/diff_tables.inc;

let $diff_table_1=master:test.tndb;
let $diff_table_2=slave:test.tndb;
source include/diff_tables.inc;


--echo ==== Clean up ====

--echo [on master]
connection master;
DROP TABLE tmyisam, tinnodb, tndb;

--echo [on slave]
sync_slave_with_master;

source include/master-slave-end.inc;