Debian Codegraph @

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

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