1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
|
# Test evaluation of replication table filter rules
#
# ==== Purpose ====
#
# Test if dynamic replication table filter rules are properly evaluated
# when some of the tables referenced by the multiple-table update do not
# exist on slave.
#
# ==== Method ====
#
# Master creates tables t1, t2, t3, t4, t5, t6, t7, t8, t9 and the
# slave is started with the following replication table filter rules:
#
# SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3";
#
# and
#
# SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6";
#
# So the slave only replicate changes to tables t1, t2 and t3 and only
# these tables exist on slave.
#
# From now on, tables t1, t2, and t3 are referenced as do tables,
# tables t4, t5, t6 are referenced as ignore tables, and tables t7,
# t8, t9 are referenced as other tables.
#
# All multi-table update tests reference tables that are not do
# tables, which do not exist on slave. And the following situations
# of multi-table update will be tested:
#
# 1. Do tables are not referenced at all
# 2. Do tables are not referenced for update
# 3. Ignore tables are referenced for update before do tables
# 4. Only do tables are referenced for update
# 5. Do tables and other tables are referenced for update
# 6. Do tables are referenced for update before ignore tables
#
# For 1, 2 and 3, the statement should be ignored by slave, for 4, 5
# and 6 the statement should be accepted by slave and cause an error
# because of non-exist tables.
#
source include/have_binlog_format_statement.inc;
source include/master-slave.inc;
connection slave;
--error ER_SLAVE_MUST_STOP
SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3";
--error ER_SLAVE_MUST_STOP
SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6";
connection slave;
source include/stop_slave.inc;
SET @@GLOBAL.replicate_do_table="";
SET @@GLOBAL.replicate_ignore_table="";
SET @@GLOBAL.replicate_do_table="test.t1,test.t2,test.t3";
SET @@GLOBAL.replicate_ignore_table="test.t4,test.t5,test.t6";
source include/start_slave.inc;
connection master;
# These tables are mentioned in do-table rules
CREATE TABLE t1 (id int, a int);
CREATE TABLE t2 (id int, b int);
CREATE TABLE t3 (id int, c int);
# These tables are mentioned in ignore-table rules
CREATE TABLE t4 (id int, d int);
CREATE TABLE t5 (id int, e int);
CREATE TABLE t6 (id int, f int);
# These tables are not mentioned in do-table or ignore-table rules
CREATE TABLE t7 (id int, g int);
CREATE TABLE t8 (id int, h int);
CREATE TABLE t9 (id int, i int);
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t3 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t5 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t6 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t7 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t8 VALUES (1, 1), (2, 2), (3, 3);
INSERT INTO t9 VALUES (1, 1), (2, 2), (3, 3);
# Only t1, t2, t3 should be replicated to slave
sync_slave_with_master;
SHOW TABLES LIKE 't%';
connection master;
#
# Do tables are not referenced, these statements should be ignored by
# slave.
#
UPDATE t7 LEFT JOIN t4 ON (t4.id=t7.id) SET d=0, g=0 where t7.id=1;
UPDATE t7 LEFT JOIN (t4, t5, t6) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t6.id) SET d=0, e=0, f=0, g=0 where t7.id=1;
UPDATE t4 LEFT JOIN (t7, t8, t9) ON (t4.id=t7.id and t4.id=t8.id and t4.id=t9.id) SET d=0, g=0, h=0, i=0 where t4.id=1;
UPDATE t7 LEFT JOIN (t8, t9) ON (t7.id=t8.id and t7.id=t9.id) SET g=0, h=0, i=0 where t7.id=1;
#
# Do tables are not referenced for update, these statements should be
# ignored by slave.
#
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET d=0 where t1.id=1;
UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET g=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET d=0, e=0, f=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t4, t5, t8) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t8.id) SET d=0, e=0, h=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t7, t8, t5) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t5.id) SET g=0, h=0, e=0 where t1.id=1;
UPDATE t1 LEFT JOIN (t2, t3, t5) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t5.id) SET e=0 where t1.id=1;
#
# Ignore tables are referenced for update before do tables, these
# statements should be ignore by slave.
#
UPDATE t4 LEFT JOIN t1 ON (t1.id=t4.id) SET a=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t7) ON (t4.id=t1.id and t7.id=t4.id) SET a = 0, d=0, g=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t2, t3) ON (t1.id=t4.id and t2.id=t4.id and t3.id=t4.id) SET a=0, b=0, c=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t2, t5) ON (t1.id=t4.id and t2.id=t4.id and t5.id=t4.id) SET a=0, b=0, e=0, d=0 where t4.id=1;
UPDATE t4 LEFT JOIN (t1, t6, t7) ON (t4.id=t1.id and t4.id=t6.id and t4.id=t7.id) SET a=0, d=0, f=0, g=0 where t4.id=1;
UPDATE t7 LEFT JOIN (t4, t1, t2) ON (t7.id=t4.id and t7.id=t1.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
UPDATE t7 LEFT JOIN (t8, t4, t1) ON (t7.id=t8.id and t7.id=t4.id and t7.id=t1.id) SET a=0, d=0, g=0, h=0 where t7.id=1;
# Sync slave to make sure all above statements are correctly ignored,
# if any of the above statement are not ignored, it would cause error
# and stop slave sql thread.
sync_slave_with_master;
connection slave;
call mtr.add_suppression("Slave SQL.*Error .Table .test.t[47]. doesn.t exist. on query.* error.* 1146");
connection master;
# Parameters for include/wait_for_slave_sql_error_and_skip.inc:
# Ask it to show SQL error message.
let $show_slave_sql_error= 1;
# The expected error will always be 1146 (ER_NO_SUCH_TABLE).
let $slave_sql_errno= 1146;
#
# Only do tables are referenced for update, these statements should
# cause error on slave
#
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t4, t7) ON (t1.id=t4.id and t1.id=t7.id) SET a=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t2, t4, t7) ON (t1.id=t2.id and t1.id=t4.id and t1.id=t7.id) SET a=0, b=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t2, t3, t7) ON (t1.id=t2.id and t1.id=t3.id and t1.id=t7.id) SET a=0, b=0, c=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
#
# Do tables and other tables are referenced for update, these
# statements should cause error on slave
#
UPDATE t1 LEFT JOIN t7 ON (t1.id=t7.id) SET a=0, g=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t7 LEFT JOIN t1 ON (t1.id=t7.id) SET a=0, g=0 where t7.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t4, t5, t7) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t7.id) SET a=0, g=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t4, t7, t8) ON (t1.id=t4.id and t1.id=t7.id and t1.id=t8.id) SET a=0, g=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t7, t8, t9) ON (t1.id=t7.id and t1.id=t8.id and t1.id=t9.id) SET a=0, g=0, h=0, i=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t7 LEFT JOIN (t1, t2, t3) ON (t7.id=t1.id and t7.id=t2.id and t7.id=t3.id) SET g=0, a=0, b=0, c=0 where t7.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t7 LEFT JOIN (t4, t5, t3) ON (t7.id=t4.id and t7.id=t5.id and t7.id=t3.id) SET g=0, c=0 where t7.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t7 LEFT JOIN (t8, t9, t3) ON (t7.id=t8.id and t7.id=t9.id and t7.id=t3.id) SET g=0, h=0, i=0, c=0 where t7.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
#
# Do tables are referenced for update before ignore tables
#
UPDATE t1 LEFT JOIN t4 ON (t1.id=t4.id) SET a=0, d=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t1 LEFT JOIN (t4, t5, t6) ON (t1.id=t4.id and t1.id=t5.id and t1.id=t6.id) SET a=0, d=0, e=0, f=0 where t1.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t4 LEFT JOIN (t1, t5, t6) ON (t4.id=t1.id and t4.id=t5.id and t4.id=t6.id) SET a=0, e=0, f=0 where t4.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
UPDATE t7 LEFT JOIN (t1, t4, t2) ON (t7.id=t1.id and t7.id=t4.id and t7.id=t2.id) SET a=0, b=0, d=0, g=0 where t7.id=1;
source include/wait_for_slave_sql_error_and_skip.inc;
sync_slave_with_master;
echo [on slave];
# We should only have tables t1, t2, t3 on slave
show tables like 't%';
# The rows in these tables should remain untouched
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
# Clean up
connection master;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
--source include/rpl_end.inc
connection slave;
SET @@GLOBAL.replicate_do_table="";
SET @@GLOBAL.replicate_ignore_table="";
|
