Debian Codegraph @

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

raw
1 let collation=utf8mb3_unicode_ci;
2 source include/have_collation.inc;
3 source include/master-slave.inc;
4
5 call mtr.add_suppression("Can't find record in 't.'");
6 call mtr.add_suppression("Can't find record in 'global_priv'");
7 call mtr.add_suppression("Can't find record in 'tables_priv'");
8
9 #
10 # BUG#16487
11 #
12 # Requirement:
13 # Multi-updates on ignored tables should not fail even if the slave does
14 # not have the ignored tables.
15 #
16 # Note table t1, t2, and t3 are ignored in the option file to this test.
17 #
18
19 --echo **** Test case for BUG#16487 ****
20 connection master;
21 CREATE TABLE test.t4 (a int);
22 CREATE TABLE test.t1 (a int);
23
24 # Expect: The row must *not* by updated on slave, since t1 is ignored
25 UPDATE test.t4 NATURAL JOIN test.t1 SET t1.a=5;
26
27 sync_slave_with_master;
28 SELECT * FROM t4;
29
30 # Cleanup
31 connection master;
32 DROP TABLE t1;
33 DROP TABLE t4;
34
35 sync_slave_with_master;
36
37
38 #
39 # Bug#25482 GRANT statements are not replicated if
40 # you use "replicate-ignore-table"
41 #
42
43 --echo **** Test case for BUG#25482 ****
44 --echo **** Adding GRANTS on master ****
45
46 connection master;
47 create table test.t1(a int);
48 create table test.t4(a int);
49
50 set sql_mode="";
51 # Simple user that should not replicate
52 GRANT SELECT ON test.t1 TO mysqltest1@localhost;
53
54 # Partial replicate
55 GRANT INSERT ON test.t4 TO mysqltest2@localhost;
56 GRANT select, update, insert, references on t1
57 to mysqltest2@localhost;
58
59 # Partial replicate 2
60 GRANT SELECT ON test.* TO mysqltest3@localhost;
61 GRANT INSERT ON test.t4 TO mysqltest3@localhost;
62 GRANT select(a), update(a), insert(a), references(a) on t4
63 to mysqltest3@localhost;
64
65 # Create another database and table
66 create database mysqltest2;
67 create table mysqltest2.t2 (id int);
68 # Create a grant that should replicate
69 GRANT SELECT ON mysqltest2.t2 TO mysqltest4@localhost IDENTIFIED BY 'pass';
70
71 # Create a grant manually
72 insert into mysql.global_priv (user, host) values ("mysqltest5", "somehost");
73
74 # Partial replicate 3 with *.*
75 GRANT SELECT ON *.* TO mysqltest6@localhost;
76 GRANT INSERT ON *.* TO mysqltest6@localhost;
77 GRANT INSERT ON test.* TO mysqltest6@localhost;
78 GRANT INSERT ON test.t1 TO mysqltest6@localhost;
79
80 --sorted_result
81 show grants for mysqltest1@localhost;
82 --sorted_result
83 show grants for mysqltest2@localhost;
84 --sorted_result
85 show grants for mysqltest3@localhost;
86 --sorted_result
87 show grants for mysqltest4@localhost;
88 --sorted_result
89 show grants for mysqltest6@localhost;
90
91 flush privileges;
92 show grants for mysqltest5@somehost;
93
94 set sql_mode="";
95 sync_slave_with_master;
96
97 --echo **** Checking grants on slave ****
98
99 # Check that grants are replicated to slave
100 --sorted_result
101 show grants for mysqltest2@localhost;
102 --sorted_result
103 show grants for mysqltest3@localhost;
104 --sorted_result
105 show grants for mysqltest4@localhost;
106 --sorted_result
107 show grants for mysqltest5@somehost;
108 --sorted_result
109 show grants for mysqltest6@localhost;
110
111 # mysqltest1 should not be on slave
112 --error 1141
113 show grants for mysqltest1@localhost;
114
115 --echo **** Revoking grants on master ****
116 connection master;
117 REVOKE SELECT ON test.t1 FROM mysqltest1@localhost;
118 REVOKE SELECT ON mysqltest2.t2 FROM mysqltest4@localhost;
119 REVOKE select(a) on t4
120 from mysqltest3@localhost;
121
122 --sorted_result
123 show grants for mysqltest1@localhost;
124 --sorted_result
125 show grants for mysqltest3@localhost;
126 --sorted_result
127 show grants for mysqltest4@localhost;
128
129 sync_slave_with_master;
130
131 --echo **** Checking grants on slave ****
132
133 # mysqltest1 should not be on slave
134 --error 1141
135 show grants for mysqltest1@localhost;
136 show grants for mysqltest3@localhost;
137 show grants for mysqltest4@localhost;
138
139 # Cleanup
140 # connection slave;
141 # BUG31552 changes idempotency is not default any longer
142 # In order the following `delete from mysql.user',
143 # where mysqltest1 does not exist on slave,
144 # to succeed on slave the mode is temporarily changed
145 set global slave_exec_mode='IDEMPOTENT';
146 call mtr.add_suppression("Slave SQL.*Could not execute Delete_rows event on table mysql.* error.* 1032");
147
148 connection master;
149 drop table t1, mysqltest2.t2;
150 drop table t4;
151 drop database mysqltest2;
152 delete from mysql.user where user like "mysqltest%";
153 delete from mysql.db where user like "mysqltest%";
154 #
155 # BUG 27606 causes failure to replicate this statement
156 # move it to slave instead
157 #delete from mysql.tables_priv where user like "mysqltest%";
158 delete from mysql.columns_priv where user like "mysqltest%";
159
160 sync_slave_with_master;
161 # bug#31552: do not restore the mode here but later in order
162 # to succeed with yet the following delete from mysql.tables_priv
163
164 #BUG27606
165 delete from mysql.tables_priv where user like "mysqltest%";
166
167 connection master;
168
169 #BUG27606
170 delete from mysql.tables_priv where user like "mysqltest%";
171
172 #
173 # bug#22877 replication character sets get out of sync
174 # using replicate-wild-ignore-table
175 #
176 connection master;
177 --disable_warnings
178 DROP TABLE IF EXISTS t5;
179 --enable_warnings
180 CREATE TABLE t5 (
181 word varchar(50) collate utf8_unicode_ci NOT NULL default ''
182 ) DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
183 SET @@session.character_set_client=33,@@session.collation_connection=192;
184 CREATE TEMPORARY TABLE tmptbl504451f4258$1 (id INT NOT NULL) ENGINE=MEMORY;
185 INSERT INTO t5 (word) VALUES ('TEST’');
186 SELECT HEX(word) FROM t5;
187 sync_slave_with_master;
188 set @@global.slave_exec_mode= default; # bug#31552 comments above
189 connection slave;
190 SELECT HEX(word) FROM t5;
191 --error 1146
192 SELECT * FROM tmptbl504451f4258$1;
193 connection master;
194 DROP TABLE t5;
195 flush privileges;
196 sync_slave_with_master;
197
198 --source include/rpl_end.inc