Debian Codegraph @

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

raw
1 #############################################################
2 # Author: Serge Kozlov <[email protected]>
3 # Date: 03/21/2008
4 # Purpose:
5 # WL#3734 Test: slave group execution
6 # Testing slave group execution: stop in middle of the group
7 # (of events) should be immpossible on slave.
8 # Group of events means set of statements between BEGIN/COMMIT
9 # for transactional engines or a statement for
10 # non-transactional engines that fires triggers and
11 # affects to another tables.
12 # Implementation of slave stop in middle of the group:
13 # Delete the table on slave before start of the group
14 # on master where this table is participant of the group.
15 # So sql thread will failed inside of the group.
16 # Expected results:
17 # 1. For non-transactional engines w/o PK slave should trying
18 # to complete executing of the group.
19 # 2. For non-transactional engines slave should stop on
20 # start position of the group.
21 #############################################################
22 --source include/have_innodb.inc
23 --source include/master-slave.inc
24 --echo
25
26 # Create tables and data
27 --echo *** Preparing data ***
28 --connection master
29 CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10)) ENGINE=MyISAM;
30 CREATE TABLE t2 LIKE t1;
31 CREATE TABLE t3 LIKE t1;
32
33 DELIMITER |;
34 CREATE TRIGGER tr1 BEFORE UPDATE ON t1
35 FOR EACH ROW BEGIN
36 UPDATE t2 SET b='YY' WHERE a=NEW.a;
37 END|
38 CREATE TRIGGER tr2 AFTER UPDATE ON t1
39 FOR EACH ROW BEGIN
40 UPDATE t3 SET b='ZZ' WHERE a=NEW.a;
41 END|
42 DELIMITER ;|
43 --echo
44
45 # Test non-transactional group with MyISAM tables w/o PK.
46 # Data for t1,t2 should be replicated for SBR even t3
47 # doesn't exist on slave
48 --echo *** Test non-transactional group w/o PK ***
49
50 --connection master
51 INSERT INTO t3 VALUES(1, 'AA');
52 INSERT INTO t2 VALUES(1, 'AA');
53 INSERT INTO t1 VALUES(1, 'AA');
54 --sync_slave_with_master
55
56 RENAME TABLE t3 TO t3_bak;
57
58 --connection master
59 UPDATE t1 SET b = 'XX' WHERE a = 1;
60 SELECT * FROM t1 ORDER BY a;
61 SELECT * FROM t2 ORDER BY a;
62 SELECT * FROM t3 ORDER BY a;
63
64 --connection slave
65 # 1146 = ER_NO_SUCH_TABLE
66 call mtr.add_suppression("Slave SQL.*Table .test.t3. doesn.t exist.* error.* 1146");
67 call mtr.add_suppression("At line *");
68 --let $slave_sql_errno= 1146
69 --source include/wait_for_slave_sql_error.inc
70 SHOW TABLES LIKE 't%';
71 if (`SELECT @@BINLOG_FORMAT = 'ROW'`) {
72 --replace_regex /AA/AA_for_row_or_XX_for_stmt_mixed/
73 SELECT * FROM t1 ORDER BY a;
74 --replace_regex /AA/AA_for_row_or_YY_for_stmt_mixed/
75 SELECT * FROM t2 ORDER BY a;
76 }
77 if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) {
78 --replace_regex /XX/AA_for_row_or_XX_for_stmt_mixed/
79 SELECT * FROM t1 ORDER BY a;
80 --replace_regex /YY/AA_for_row_or_YY_for_stmt_mixed/
81 SELECT * FROM t2 ORDER BY a;
82 }
83
84 --source include/stop_slave_io.inc
85 RENAME TABLE t3_bak TO t3;
86 --source include/start_slave.inc
87
88 --connection master
89 TRUNCATE t1;
90 TRUNCATE t2;
91 TRUNCATE t3;
92 --sync_slave_with_master
93 --echo
94
95
96 # Test non-transactional group with MyISAM tables and PK.
97 # No data replicated because update based on PK
98 --echo *** Test non-transactional group w/ PK ***
99
100 --connection master
101 ALTER TABLE t1 ADD PRIMARY KEY (a);
102 ALTER TABLE t2 ADD PRIMARY KEY (a);
103 ALTER TABLE t3 ADD PRIMARY KEY (a);
104
105 --sync_slave_with_master
106 RENAME TABLE t3 TO t3_bak;
107
108 --connection master
109 INSERT INTO t3 VALUES(2, 'B');
110 INSERT INTO t2 VALUES(2, 'B');
111 INSERT INTO t1 VALUES(2, 'B');
112 UPDATE t1 SET b = 'X' WHERE a = 2;
113
114 --connection slave
115 # 1146 = ER_NO_SUCH_TABLE
116 --let $slave_sql_errno= 1146
117 --source include/wait_for_slave_sql_error.inc
118
119 --connection master
120 SELECT * FROM t1 ORDER BY a;
121 SELECT * FROM t2 ORDER BY a;
122 SELECT * FROM t3 ORDER BY a;
123
124 --connection slave
125 SHOW TABLES LIKE 't%';
126 SELECT * FROM t1 ORDER BY a;
127 SELECT * FROM t2 ORDER BY a;
128
129 --source include/stop_slave_io.inc
130 RENAME TABLE t3_bak TO t3;
131 --source include/start_slave.inc
132
133 --connection master
134 TRUNCATE t1;
135 TRUNCATE t2;
136 TRUNCATE t3;
137 --sync_slave_with_master
138 --echo
139
140
141 # Test transactional group with InnoDB tables with PK
142 # No data replicated if errors happens inside transaction
143 --echo *** Test transactional group w/ PK ***
144
145 --connection master
146 ALTER TABLE t1 ENGINE=InnoDB;
147 ALTER TABLE t2 ENGINE=InnoDB;
148 ALTER TABLE t3 ENGINE=InnoDB;
149
150 --sync_slave_with_master
151 RENAME TABLE t3 TO t3_bak;
152
153 --connection master
154 BEGIN;
155 INSERT INTO t1 VALUES (3, 'C'), (4, 'D');
156 INSERT INTO t2 VALUES (3, 'C'), (4, 'D');
157 INSERT INTO t3 VALUES (3, 'C'), (4, 'D');
158 COMMIT;
159
160 --connection slave
161 # 1146 = ER_NO_SUCH_TABLE
162 --let $slave_sql_errno= 1146
163 --source include/wait_for_slave_sql_error.inc
164
165 --connection master
166 SELECT * FROM t1 ORDER BY a;
167 SELECT * FROM t2 ORDER BY a;
168 SELECT * FROM t3 ORDER BY a;
169
170 --connection slave
171 SHOW TABLES LIKE 't%';
172 SELECT * FROM t1 ORDER BY a;
173 SELECT * FROM t2 ORDER BY a;
174
175 source include/stop_slave_io.inc;
176 RENAME TABLE t3_bak TO t3;
177 source include/start_slave.inc;
178
179 # Clean up
180 --echo *** Clean up ***
181 --connection master
182 DROP TABLE t1,t2,t3;
183 --sync_slave_with_master
184
185 # End of 5.1 test
186 --source include/rpl_end.inc