Debian Codegraph @

mysql-test/suite/federated/t/federated_server.test

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