Debian Codegraph @

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

raw
1 # PURPOSE. Test that blackhole works with replication in all three
2 # modes: STATEMENT, MIXED, and ROW.
3 #
4 # METHOD. We start by creating a table on the master and then change
5 # the engine to use blackhole on the slave.
6 #
7 # After insert/update/delete of one or more rows, the test the
8 # proceeds to check that replication is running after replicating an
9 # change, that the blackhole engine does not contain anything (which
10 # is just a check that the correct engine is used), and that something
11 # is written to the binary log.
12 #
13 # Whe check INSERT, UPDATE, and DELETE statement for tables with no
14 # key (forcing a range search on the slave), primary keys (using a
15 # primary key lookup), and index/key with multiple matches (forcing an
16 # index search).
17
18 --source include/not_group_replication_plugin.inc
19 source include/have_blackhole.inc;
20 source include/master-slave.inc;
21
22 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
23
24 # We start with no primary key
25 CREATE TABLE t1 (a INT, b INT, c INT);
26 CREATE TABLE t2 (a INT, b INT, c INT);
27
28 --source include/sync_slave_sql_with_master.inc
29 ALTER TABLE t1 ENGINE=BLACKHOLE;
30
31 connection master;
32 INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
33 --source include/sync_slave_sql_with_master.inc
34
35 # Test insert, no primary key
36 let $statement = INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
37 source extra/rpl_tests/rpl_blackhole.test;
38
39 # Test update, no primary key
40 let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
41 source extra/rpl_tests/rpl_blackhole.test;
42
43 # Test delete, no primary key
44 let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
45 source extra/rpl_tests/rpl_blackhole.test;
46
47 # Test INSERT-SELECT into Blackhole, no primary key
48 let $statement = INSERT INTO t1 SELECT * FROM t2;
49 source extra/rpl_tests/rpl_blackhole.test;
50
51 #
52 # The MASTER has MyISAM as the engine for both tables. The SLAVE has Blackhole
53 # on t1 (transactional engine) and MyISAM on t2 (non-transactional engine).
54 #
55 # In MIXED mode, the command "INSERT INTO t2 SELECT * FROM t1" is logged as
56 # statement on the master. On the slave, it is tagged as unsafe because the
57 # statement mixes both transactional and non-transactional engines and as such
58 # its changes are logged as rows. However, due to the nature of the blackhole
59 # engine, no rows are returned and thus any chain replication would make the
60 # next master on the chain diverge.
61 #
62 # Fo this reason, we have disabled the statement.
63 #
64 # Test INSERT-SELECT from Blackhole, no primary key
65 # let $statement = INSERT INTO t2 SELECT * FROM t1;
66 # source extra/rpl_tests/rpl_blackhole.test;
67 #
68
69 connection master;
70 ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
71
72 # Test insert, primary key
73 let $statement = INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
74 source extra/rpl_tests/rpl_blackhole.test;
75
76 # Test update, primary key
77 let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
78 source extra/rpl_tests/rpl_blackhole.test;
79
80 # Test delete, primary key
81 let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
82 source extra/rpl_tests/rpl_blackhole.test;
83
84 connection master;
85 ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
86
87 # Test insert, key
88 let $statement = INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
89 source extra/rpl_tests/rpl_blackhole.test;
90
91 # Test update, key
92 let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
93 source extra/rpl_tests/rpl_blackhole.test;
94
95 # Test delete, key
96 let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
97 source extra/rpl_tests/rpl_blackhole.test;
98
99
100 connection master;
101 DROP TABLE t1,t2;
102 --source include/rpl_end.inc