Debian Codegraph @

mysql-test/suite/federated/federated_server.test

raw
1 # WL #3031 This test tests the new servers table as well as
2 # if federated can utilise the servers table
3
4 # Slow test, don't run during staging part
5 -- source include/not_staging.inc
6 -- source include/big_test.inc
7 -- source include/federated.inc
8
9 connection slave;
10 create database first_db;
11 create database second_db;
12
13 use first_db;
14
15 DROP TABLE IF EXISTS first_db.t1;
16 CREATE TABLE first_db.t1 (
17 `id` int(20) NOT NULL,
18 `name` varchar(64) NOT NULL default ''
19 )
20 DEFAULT CHARSET=latin1;
21
22 DROP TABLE IF EXISTS first_db.t2;
23 CREATE TABLE first_db.t2 (
24 `id` int(20) NOT NULL,
25 `name` varchar(64) NOT NULL default ''
26 )
27 DEFAULT CHARSET=latin1;
28
29 use second_db;
30 DROP TABLE IF EXISTS second_db.t1;
31 CREATE TABLE second_db.t1 (
32 `id` int(20) NOT NULL,
33 `name` varchar(64) NOT NULL default ''
34 )
35 DEFAULT CHARSET=latin1;
36
37 DROP TABLE IF EXISTS second_db.t2;
38 CREATE TABLE second_db.t2 (
39 `id` int(20) NOT NULL,
40 `name` varchar(64) NOT NULL default ''
41 )
42 DEFAULT CHARSET=latin1;
43
44 connection master;
45
46 drop server if exists 'server_one';
47 --replace_result $SLAVE_MYPORT SLAVE_PORT
48 eval create server 'server_one' foreign data wrapper 'mysql' options
49 (HOST '127.0.0.1',
50 DATABASE 'first_db',
51 USER 'root',
52 PASSWORD '',
53 PORT $SLAVE_MYPORT,
54 SOCKET '',
55 OWNER 'root');
56
57 drop server if exists 'server_two';
58 --replace_result $SLAVE_MYPORT SLAVE_PORT
59 eval create server 'server_two' foreign data wrapper 'mysql' options
60 (HOST '127.0.0.1',
61 DATABASE 'second_db',
62 USER 'root',
63 PASSWORD '',
64 PORT $SLAVE_MYPORT,
65 SOCKET '',
66 OWNER 'root');
67
68 --replace_result $SLAVE_MYPORT SLAVE_PORT
69 eval select * from mysql.servers order by db;
70
71 DROP TABLE IF EXISTS federated.old;
72 --replace_result $SLAVE_MYPORT SLAVE_PORT
73 eval CREATE TABLE federated.old (
74 `id` int(20) NOT NULL,
75 `name` varchar(64) NOT NULL default ''
76 )
77 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
78 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/first_db/t1';
79
80 INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
81
82 SELECT * FROM federated.old;
83
84 DROP TABLE IF EXISTS federated.old2;
85 --replace_result $SLAVE_MYPORT SLAVE_PORT
86 eval CREATE TABLE federated.old2 (
87 `id` int(20) NOT NULL,
88 `name` varchar(64) NOT NULL default ''
89 )
90 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
91 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/first_db/t2';
92
93 INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
94 SELECT * FROM federated.old2;
95
96 DROP TABLE IF EXISTS federated.urldb2t1;
97 --replace_result $SLAVE_MYPORT SLAVE_PORT
98 eval CREATE TABLE federated.urldb2t1 (
99 `id` int(20) NOT NULL,
100 `name` varchar(64) NOT NULL default ''
101 )
102 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
103 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/second_db/t1';
104 INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format');
105 SELECT * FROM federated.urldb2t1;
106
107 DROP TABLE IF EXISTS federated.urldb2t2;
108 --replace_result $SLAVE_MYPORT SLAVE_PORT
109 eval CREATE TABLE federated.urldb2t2 (
110 `id` int(20) NOT NULL,
111 `name` varchar(64) NOT NULL default ''
112 )
113 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
114 CONNECTION='mysql://[email protected]:$SLAVE_MYPORT/second_db/t2';
115 INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format');
116 SELECT * FROM federated.urldb2t2;
117
118 DROP TABLE IF EXISTS federated.t1;
119 CREATE TABLE federated.t1 (
120 `id` int(20) NOT NULL,
121 `name` varchar(64) NOT NULL default ''
122 )
123 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
124 CONNECTION='server_one';
125
126 INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
127
128 SELECT * FROM federated.t1;
129
130 DROP TABLE IF EXISTS federated.whatever;
131 CREATE TABLE federated.whatever (
132 `id` int(20) NOT NULL,
133 `name` varchar(64) NOT NULL default ''
134 )
135 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
136 CONNECTION='server_one/t1';
137 INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
138 SELECT * FROM federated.whatever;
139
140 ALTER SERVER 'server_one' options(DATABASE 'second_db');
141
142 # FLUSH TABLES is now unneccessary
143
144 INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
145 SELECT * FROM federated.t1;
146
147 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
148 SELECT * FROM federated.whatever;
149
150 drop table federated.t1;
151
152 drop server 'server_one';
153 drop server 'server_two';
154 select * from mysql.servers order by db;
155
156 connection slave;
157 drop table first_db.t1;
158 drop table second_db.t1;
159 drop database first_db;
160 drop database second_db;
161
162 #
163 # Bug#25671 - CREATE/DROP/ALTER SERVER should require privileges
164 #
165 # Changes to SERVER declarations should require FEDERATED ADMIN privilege.
166 # Based upon test case by Giuseppe Maxia
167
168 create database db_legitimate;
169 create database db_bogus;
170
171 use db_legitimate;
172 CREATE TABLE db_legitimate.t1 (
173 `id` int(20) NOT NULL,
174 `name` varchar(64) NOT NULL default ''
175 );
176 INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
177
178 use db_bogus;
179 CREATE TABLE db_bogus.t1 (
180 `id` int(20) NOT NULL,
181 `name` varchar(64) NOT NULL default ''
182 )
183 ;
184 INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
185
186 connection slave;
187 create user test_fed@localhost identified by 'foo';
188 grant all on db_legitimate.* to test_fed@localhost;
189
190 connection master;
191 --replace_result $SLAVE_MYPORT SLAVE_PORT
192 eval create server 's1' foreign data wrapper 'mysql' options
193 (HOST '127.0.0.1',
194 DATABASE 'db_legitimate',
195 USER 'test_fed',
196 PASSWORD 'foo',
197 PORT $SLAVE_MYPORT,
198 SOCKET '',
199 OWNER 'root');
200
201 create user guest_select@localhost;
202 grant select on federated.* to guest_select@localhost;
203
204 create user guest_super@localhost;
205 grant select,FEDERATED ADMIN,RELOAD on *.* to guest_super@localhost;
206
207 create user guest_usage@localhost;
208 grant usage on *.* to guest_usage@localhost;
209
210 CREATE TABLE federated.t1 (
211 `id` int(20) NOT NULL,
212 `name` varchar(64) NOT NULL default ''
213 ) ENGINE = FEDERATED CONNECTION = 's1';
214
215 select * from federated.t1;
216
217 connect (conn_select,127.0.0.1,guest_select,,federated,$MASTER_MYPORT);
218 connect (conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT);
219 connect (conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT);
220
221 connection conn_select;
222 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
223 alter server s1 options (database 'db_bogus');
224
225 connection master;
226 flush tables;
227 select * from federated.t1;
228
229 connection conn_usage;
230 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
231 alter server s1 options (database 'db_bogus');
232
233 connection master;
234 flush tables;
235 select * from federated.t1;
236
237 connection conn_super;
238 alter server s1 options (database 'db_bogus');
239
240 connection master;
241 flush tables;
242 --error ER_DBACCESS_DENIED_ERROR,ER_CONNECT_TO_FOREIGN_DATA_SOURCE
243 select * from federated.t1;
244
245 connection conn_select;
246 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
247 drop server if exists 's1';
248 --replace_result $SLAVE_MYPORT SLAVE_PORT
249 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
250 eval create server 's1' foreign data wrapper 'mysql' options
251 (HOST '127.0.0.1',
252 DATABASE 'db_legitimate',
253 USER 'test_fed',
254 PASSWORD 'foo',
255 PORT $SLAVE_MYPORT,
256 SOCKET '',
257 OWNER 'root');
258
259 connection conn_super;
260 drop server 's1';
261 --replace_result $SLAVE_MYPORT SLAVE_PORT
262 eval create server 's1' foreign data wrapper 'mysql' options
263 (HOST '127.0.0.1',
264 DATABASE 'db_legitimate',
265 USER 'test_fed',
266 PASSWORD 'foo',
267 PORT $SLAVE_MYPORT,
268 SOCKET '',
269 OWNER 'root');
270
271 connection master;
272 flush tables;
273 select * from federated.t1;
274
275 # clean up test
276 connection slave;
277 drop user test_fed@localhost;
278 drop database db_legitimate;
279 drop database db_bogus;
280
281 disconnect conn_select;
282 disconnect conn_usage;
283 disconnect conn_super;
284
285 connection master;
286 drop user guest_super@localhost;
287 drop user guest_usage@localhost;
288 drop user guest_select@localhost;
289 drop table federated.t1;
290 drop server 's1';
291
292 #
293 # Bug#30671 - ALTER SERVER causes the server to crash
294 #
295 create server 's1' foreign data wrapper 'mysql' options (host 'foo');
296 alter server 's1' options
297 (host 'localhost', database '', user '',
298 password '', socket '', owner '', port 3306);
299 # The next statement would crash unpatched server
300 alter server 's1' options
301 (host 'localhost', database 'database1', user '',
302 password '', socket '', owner '', port 3306);
303 drop server 's1';
304
305 --echo # End of 5.1 tests
306
307
308 #
309 # Bug#25721 - deadlock with ALTER/CREATE SERVER
310 #
311 connect (other,localhost,root,,);
312 connection master;
313 use test;
314 create table t1(a int);
315 insert into t1 values (1);
316 delimiter //;
317 create procedure p1 ()
318 begin
319 DECLARE v INT DEFAULT 0;
320 DECLARE i INT;
321 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
322 SET i = sleep(5);
323 WHILE v < 10000 do
324 CREATE SERVER s
325 FOREIGN DATA WRAPPER mysql
326 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
327 ALTER SERVER s OPTIONS (USER 'Remote');
328 DROP SERVER s;
329 SET v = v + 1;
330 END WHILE;
331 select a into @a from t1; # Just reset warnings
332 END//
333 delimiter ;//
334 connection other;
335 use test;
336 send call p1();
337 connection master;
338 call p1();
339 connection other;
340 reap;
341 drop procedure p1;
342 drop table t1;
343 drop server if exists s;
344
345
346 source suite/federated/include/federated_cleanup.inc;