diff options
Diffstat (limited to 'mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_tables.test')
-rw-r--r-- | mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_tables.test | 373 |
1 files changed, 373 insertions, 0 deletions
diff --git a/mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_tables.test b/mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_tables.test new file mode 100644 index 00000000000..a20e42f1b24 --- /dev/null +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_mixed_tables.test @@ -0,0 +1,373 @@ +# ==== Purpose ==== +# +# Test replication of transactions on tables which have different +# engines on master and slave. This tests all combinations of innodb, +# myisam, and ndb. +# +# ==== Method ==== +# +# Set up six tables, each being innodb, myisam, or innodb on master, +# and another of innodb, myisam, or innodb on slave. For each table, +# do the following: +# +# - committed and rollback'ed transactions, with autocommit on and +# off +# - non-transactions with autocommit on +# - non-transactions with autocommit off, where the master table is +# myisam. +# +# Note: we are running the slave with +# --replicate-ignore-table=mysql.ndb_apply_status . See BUG#34557 for +# explanation. +# +# ==== Related bugs ==== +# +# BUG#26395: if crash during autocommit update to transactional table on master, slave fails +# BUG#29288: myisam transactions replicated to a transactional slave leaves slave unstable +# BUG#34557: Row-based replication from ndb to non-ndb gives error on slave +# BUG#34600: Rolled-back punch transactions not replicated correctly +# +# ==== Todo ==== +# +# We should eventually try transactions touching two tables which are +# of different engines on the same server (so that we try, e.g. punch +# transactions; cf BUG#34600). However, that will make the test much +# bigger (9 master-slave engine combinations [myisam->myisam, +# myisam->ndb, etc]. To try all combinations of one or more such +# tables means 2^9-1=511 transactions. We need to multiplied by 5 +# since we want to test committed/rollback'ed transactions +# with/without AUTOCOMMIT, as well as non-transactions with +# autocommit). We'd have to write a script to produce the test case. + + +--echo ==== Initialization ==== + +--source include/have_ndb.inc +--source include/have_innodb.inc +--source include/ndb_master-slave.inc + +--echo ---- setup master ---- + +CREATE TABLE myisam_innodb (a INT) ENGINE=MYISAM; +CREATE TABLE innodb_myisam (a INT) ENGINE=INNODB; +CREATE TABLE myisam_ndb (a INT) ENGINE=MYISAM; +CREATE TABLE ndb_myisam (a INT) ENGINE=NDB; +CREATE TABLE innodb_ndb (a INT) ENGINE=INNODB; +CREATE TABLE ndb_innodb (a INT) ENGINE=NDB; + +SHOW CREATE TABLE myisam_innodb; +SHOW CREATE TABLE innodb_myisam; +SHOW CREATE TABLE myisam_ndb; +SHOW CREATE TABLE ndb_myisam; +SHOW CREATE TABLE innodb_ndb; +SHOW CREATE TABLE ndb_innodb; + +--echo ---- setup slave with different engines ---- + +sync_slave_with_master; + +DROP TABLE myisam_innodb, innodb_myisam; +DROP TABLE myisam_ndb, ndb_myisam; +DROP TABLE innodb_ndb, ndb_innodb; + +CREATE TABLE myisam_innodb (a INT) ENGINE=INNODB; +CREATE TABLE innodb_myisam (a INT) ENGINE=MYISAM; +CREATE TABLE myisam_ndb (a INT) ENGINE=NDB; +CREATE TABLE ndb_myisam (a INT) ENGINE=MYISAM; +CREATE TABLE innodb_ndb (a INT) ENGINE=NDB; +CREATE TABLE ndb_innodb (a INT) ENGINE=INNODB; + +SHOW CREATE TABLE myisam_innodb; +SHOW CREATE TABLE innodb_myisam; +SHOW CREATE TABLE myisam_ndb; +SHOW CREATE TABLE ndb_myisam; +SHOW CREATE TABLE innodb_ndb; +SHOW CREATE TABLE ndb_innodb; + +connection master; + + +--echo ==== AUTOCOMMIT=0, transactions ==== + +--echo ---- COMMIT ---- + +SET AUTOCOMMIT = 0; + +BEGIN; +INSERT INTO myisam_innodb VALUES (1); +INSERT INTO myisam_innodb VALUES (2); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_myisam VALUES (3); +INSERT INTO innodb_myisam VALUES (4); +COMMIT; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO myisam_ndb VALUES (5); +INSERT INTO myisam_ndb VALUES (6); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO ndb_myisam VALUES (7); +INSERT INTO ndb_myisam VALUES (8); +COMMIT; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO ndb_innodb VALUES (9); +INSERT INTO ndb_innodb VALUES (10); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_ndb VALUES (11); +INSERT INTO innodb_ndb VALUES (12); +COMMIT; +sync_slave_with_master; +connection master; + +--echo ---- ROLLBACK ---- + +# This test does not work in ROW mode after the changes introduced in +# BUG#40116. After WL#2687 is pushed, Tests should be added again. +--disable_parsing +BEGIN; +INSERT INTO myisam_innodb VALUES (13); +INSERT INTO myisam_innodb VALUES (14); +ROLLBACK; +sync_slave_with_master; +--enable_parsing +connection master; +BEGIN; +INSERT INTO innodb_myisam VALUES (15); +INSERT INTO innodb_myisam VALUES (16); +ROLLBACK; +sync_slave_with_master; +connection master; + +# This test does not work in ROW mode after the changes introduced in +# BUG#40116. After WL#2687 is pushed, these tests should be enabled +# again. +--disable_parsing +BEGIN; +INSERT INTO myisam_ndb VALUES (17); +INSERT INTO myisam_ndb VALUES (18); +ROLLBACK; +sync_slave_with_master; +connection master; +--enable_parsing +BEGIN; +INSERT INTO ndb_myisam VALUES (19); +INSERT INTO ndb_myisam VALUES (20); +ROLLBACK; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO ndb_innodb VALUES (21); +INSERT INTO ndb_innodb VALUES (22); +ROLLBACK; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_ndb VALUES (23); +INSERT INTO innodb_ndb VALUES (24); +ROLLBACK; +sync_slave_with_master; +connection master; + + +--echo ==== AUTOCOMMIT=1, transactions ==== + +--echo ---- COMMIT ---- + +SET AUTOCOMMIT = 1; + +BEGIN; +INSERT INTO myisam_innodb VALUES (25); +INSERT INTO myisam_innodb VALUES (26); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_myisam VALUES (27); +INSERT INTO innodb_myisam VALUES (28); +COMMIT; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO myisam_ndb VALUES (29); +INSERT INTO myisam_ndb VALUES (30); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO ndb_myisam VALUES (31); +INSERT INTO ndb_myisam VALUES (32); +COMMIT; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO ndb_innodb VALUES (33); +INSERT INTO ndb_innodb VALUES (34); +COMMIT; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_ndb VALUES (35); +INSERT INTO innodb_ndb VALUES (36); +COMMIT; +sync_slave_with_master; +connection master; + +--echo ---- ROLLBACK ---- + +# This test does not work in ROW mode after the changes introduced in +# BUG#40116. After WL#2687 is pushed, these tests should be enabled +# again. +--disable_parsing +BEGIN; +INSERT INTO myisam_innodb VALUES (37); +INSERT INTO myisam_innodb VALUES (38); +ROLLBACK; +sync_slave_with_master; +connection master; +--enable_parsing +BEGIN; +INSERT INTO innodb_myisam VALUES (39); +INSERT INTO innodb_myisam VALUES (40); +ROLLBACK; +sync_slave_with_master; +connection master; + +# This test does not work in ROW mode after the changes introduced in +# BUG#40116. After WL#2687 is pushed, these tests should be enabled +# again. +--disable_parsing +BEGIN; +INSERT INTO myisam_ndb VALUES (41); +INSERT INTO myisam_ndb VALUES (42); +ROLLBACK; +sync_slave_with_master; +connection master; +--enable_parsing +BEGIN; +INSERT INTO ndb_myisam VALUES (43); +INSERT INTO ndb_myisam VALUES (44); +ROLLBACK; +sync_slave_with_master; +connection master; + +BEGIN; +INSERT INTO ndb_innodb VALUES (45); +INSERT INTO ndb_innodb VALUES (46); +ROLLBACK; +sync_slave_with_master; +connection master; +BEGIN; +INSERT INTO innodb_ndb VALUES (47); +INSERT INTO innodb_ndb VALUES (48); +ROLLBACK; +sync_slave_with_master; +connection master; + + +--echo ==== AUTOCOMMIT=1, single statements ==== + +INSERT INTO myisam_innodb VALUES (49); +INSERT INTO myisam_innodb VALUES (50); +sync_slave_with_master; +connection master; +INSERT INTO innodb_myisam VALUES (51); +INSERT INTO innodb_myisam VALUES (52); +sync_slave_with_master; +connection master; + +INSERT INTO myisam_ndb VALUES (53); +INSERT INTO myisam_ndb VALUES (54); +sync_slave_with_master; +connection master; +INSERT INTO ndb_myisam VALUES (55); +INSERT INTO ndb_myisam VALUES (56); +sync_slave_with_master; +connection master; + +INSERT INTO ndb_innodb VALUES (57); +INSERT INTO ndb_innodb VALUES (58); +sync_slave_with_master; +connection master; +INSERT INTO innodb_ndb VALUES (59); +INSERT INTO innodb_ndb VALUES (60); +sync_slave_with_master; +connection master; + + +--echo ==== AUTOCOMMIT=0, single statements, myisam on master ==== + +SET AUTOCOMMIT = 0; + +# These tests do not work in ROW mode after the changes introduced in +# BUG#40116. After WL#2687 is pushed, these tests should be enabled +# again. +--disable_parsing +# This tests BUG#29288. +INSERT INTO myisam_innodb VALUES (61); +INSERT INTO myisam_innodb VALUES (62); +sync_slave_with_master; +connection master; + +INSERT INTO myisam_ndb VALUES (63); +INSERT INTO myisam_ndb VALUES (64); +sync_slave_with_master; +connection master; +--enable_parsing + + +--echo ==== Show results ==== + +SELECT * FROM myisam_innodb ORDER BY a; +SELECT * FROM innodb_myisam ORDER BY a; +SELECT * FROM myisam_ndb ORDER BY a; +SELECT * FROM ndb_myisam ORDER BY a; +SELECT * FROM innodb_ndb ORDER BY a; +SELECT * FROM ndb_innodb ORDER BY a; + +let $diff_table_1=master:test.myisam_innodb; +let $diff_table_2=slave:test.myisam_innodb; +source include/diff_tables.inc; + +let $diff_table_1=master:test.innodb_myisam; +let $diff_table_2=slave:test.innodb_myisam; +source include/diff_tables.inc; + +let $diff_table_1=master:test.myisam_ndb; +let $diff_table_2=slave:test.myisam_ndb; +source include/diff_tables.inc; + +let $diff_table_1=master:test.ndb_myisam; +let $diff_table_2=slave:test.ndb_myisam; +source include/diff_tables.inc; + +let $diff_table_1=master:test.innodb_ndb; +let $diff_table_2=slave:test.innodb_ndb; +source include/diff_tables.inc; + +let $diff_table_1=master:test.ndb_innodb; +let $diff_table_2=slave:test.ndb_innodb; +source include/diff_tables.inc; + + +--echo ==== Clean up ==== + +drop table myisam_innodb, innodb_myisam; +drop table myisam_ndb, ndb_myisam; +drop table innodb_ndb, ndb_innodb; +sync_slave_with_master; |