Debian Codegraph @

mysql-test/suite/federated/federated_server.result

raw
1 connect master,127.0.0.1,root,,test,$MASTER_MYPORT,;
2 connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
3 connection master;
4 CREATE DATABASE federated;
5 connection slave;
6 CREATE DATABASE federated;
7 connection slave;
8 create database first_db;
9 create database second_db;
10 use first_db;
11 DROP TABLE IF EXISTS first_db.t1;
12 Warnings:
13 Note 1051 Unknown table '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 DROP TABLE IF EXISTS first_db.t2;
20 Warnings:
21 Note 1051 Unknown table 'first_db.t2'
22 CREATE TABLE first_db.t2 (
23 `id` int(20) NOT NULL,
24 `name` varchar(64) NOT NULL default ''
25 )
26 DEFAULT CHARSET=latin1;
27 use second_db;
28 DROP TABLE IF EXISTS second_db.t1;
29 Warnings:
30 Note 1051 Unknown table '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 DROP TABLE IF EXISTS second_db.t2;
37 Warnings:
38 Note 1051 Unknown table 'second_db.t2'
39 CREATE TABLE second_db.t2 (
40 `id` int(20) NOT NULL,
41 `name` varchar(64) NOT NULL default ''
42 )
43 DEFAULT CHARSET=latin1;
44 connection master;
45 drop server if exists 'server_one';
46 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_PORT,
52 SOCKET '',
53 OWNER 'root');
54 drop server if exists 'server_two';
55 create server 'server_two' foreign data wrapper 'mysql' options
56 (HOST '127.0.0.1',
57 DATABASE 'second_db',
58 USER 'root',
59 PASSWORD '',
60 PORT SLAVE_PORT,
61 SOCKET '',
62 OWNER 'root');
63 select * from mysql.servers order by db;
64 Server_name Host Db Username Password Port Socket Wrapper Owner Options
65 server_one 127.0.0.1 first_db root SLAVE_PORT mysql root {"HOST": "127.0.0.1", "DATABASE": "first_db", "USER": "root", "PASSWORD": "", "PORT": "SLAVE_PORT", "SOCKET": "", "OWNER": "root"}
66 server_two 127.0.0.1 second_db root SLAVE_PORT mysql root {"HOST": "127.0.0.1", "DATABASE": "second_db", "USER": "root", "PASSWORD": "", "PORT": "SLAVE_PORT", "SOCKET": "", "OWNER": "root"}
67 DROP TABLE IF EXISTS federated.old;
68 Warnings:
69 Note 1051 Unknown table 'federated.old'
70 CREATE TABLE federated.old (
71 `id` int(20) NOT NULL,
72 `name` varchar(64) NOT NULL default ''
73 )
74 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
75 CONNECTION='mysql://[email protected]:SLAVE_PORT/first_db/t1';
76 INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
77 SELECT * FROM federated.old;
78 id name
79 1 federated.old-> first_db.t1, url format
80 DROP TABLE IF EXISTS federated.old2;
81 Warnings:
82 Note 1051 Unknown table 'federated.old2'
83 CREATE TABLE federated.old2 (
84 `id` int(20) NOT NULL,
85 `name` varchar(64) NOT NULL default ''
86 )
87 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
88 CONNECTION='mysql://[email protected]:SLAVE_PORT/first_db/t2';
89 INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
90 SELECT * FROM federated.old2;
91 id name
92 1 federated.old2-> first_db.t2, url format
93 DROP TABLE IF EXISTS federated.urldb2t1;
94 Warnings:
95 Note 1051 Unknown table 'federated.urldb2t1'
96 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_PORT/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 id name
105 1 federated.urldb2t1 -> second_db.t1, url format
106 DROP TABLE IF EXISTS federated.urldb2t2;
107 Warnings:
108 Note 1051 Unknown table 'federated.urldb2t2'
109 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_PORT/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 id name
118 1 federated.urldb2t2 -> second_db.t2, url format
119 DROP TABLE IF EXISTS federated.t1;
120 Warnings:
121 Note 1051 Unknown table 'federated.t1'
122 CREATE TABLE federated.t1 (
123 `id` int(20) NOT NULL,
124 `name` varchar(64) NOT NULL default ''
125 )
126 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
127 CONNECTION='server_one';
128 INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
129 SELECT * FROM federated.t1;
130 id name
131 1 federated.old-> first_db.t1, url format
132 1 server_one, new scheme, first_db.t1
133 DROP TABLE IF EXISTS federated.whatever;
134 Warnings:
135 Note 1051 Unknown table 'federated.whatever'
136 CREATE TABLE federated.whatever (
137 `id` int(20) NOT NULL,
138 `name` varchar(64) NOT NULL default ''
139 )
140 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
141 CONNECTION='server_one/t1';
142 INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
143 SELECT * FROM federated.whatever;
144 id name
145 1 federated.old-> first_db.t1, url format
146 1 server_one, new scheme, first_db.t1
147 1 server_one, new scheme, whatever, first_db.t1
148 ALTER SERVER 'server_one' options(DATABASE 'second_db');
149 INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
150 SELECT * FROM federated.t1;
151 id name
152 1 federated.urldb2t1 -> second_db.t1, url format
153 1 server_two, new scheme, second_db.t1
154 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
155 SELECT * FROM federated.whatever;
156 id name
157 1 federated.urldb2t1 -> second_db.t1, url format
158 1 server_two, new scheme, second_db.t1
159 1 server_two, new scheme, whatever, second_db.t1
160 drop table federated.t1;
161 drop server 'server_one';
162 drop server 'server_two';
163 select * from mysql.servers order by db;
164 Server_name Host Db Username Password Port Socket Wrapper Owner Options
165 connection slave;
166 drop table first_db.t1;
167 drop table second_db.t1;
168 drop database first_db;
169 drop database second_db;
170 create database db_legitimate;
171 create database db_bogus;
172 use db_legitimate;
173 CREATE TABLE db_legitimate.t1 (
174 `id` int(20) NOT NULL,
175 `name` varchar(64) NOT NULL default ''
176 );
177 INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
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 connection slave;
186 create user test_fed@localhost identified by 'foo';
187 grant all on db_legitimate.* to test_fed@localhost;
188 connection master;
189 create server 's1' foreign data wrapper 'mysql' options
190 (HOST '127.0.0.1',
191 DATABASE 'db_legitimate',
192 USER 'test_fed',
193 PASSWORD 'foo',
194 PORT SLAVE_PORT,
195 SOCKET '',
196 OWNER 'root');
197 create user guest_select@localhost;
198 grant select on federated.* to guest_select@localhost;
199 create user guest_super@localhost;
200 grant select,FEDERATED ADMIN,RELOAD on *.* to guest_super@localhost;
201 create user guest_usage@localhost;
202 grant usage on *.* to guest_usage@localhost;
203 CREATE TABLE federated.t1 (
204 `id` int(20) NOT NULL,
205 `name` varchar(64) NOT NULL default ''
206 ) ENGINE = FEDERATED CONNECTION = 's1';
207 select * from federated.t1;
208 id name
209 1 this is legitimate
210 connect conn_select,127.0.0.1,guest_select,,federated,$MASTER_MYPORT;
211 connect conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT;
212 connect conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT;
213 connection conn_select;
214 alter server s1 options (database 'db_bogus');
215 ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
216 connection master;
217 flush tables;
218 select * from federated.t1;
219 id name
220 1 this is legitimate
221 connection conn_usage;
222 alter server s1 options (database 'db_bogus');
223 ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
224 connection master;
225 flush tables;
226 select * from federated.t1;
227 id name
228 1 this is legitimate
229 connection conn_super;
230 alter server s1 options (database 'db_bogus');
231 connection master;
232 flush tables;
233 select * from federated.t1;
234 Got one of the listed errors
235 connection conn_select;
236 drop server if exists 's1';
237 ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
238 create server 's1' foreign data wrapper 'mysql' options
239 (HOST '127.0.0.1',
240 DATABASE 'db_legitimate',
241 USER 'test_fed',
242 PASSWORD 'foo',
243 PORT SLAVE_PORT,
244 SOCKET '',
245 OWNER 'root');
246 ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
247 connection conn_super;
248 drop server 's1';
249 create server 's1' foreign data wrapper 'mysql' options
250 (HOST '127.0.0.1',
251 DATABASE 'db_legitimate',
252 USER 'test_fed',
253 PASSWORD 'foo',
254 PORT SLAVE_PORT,
255 SOCKET '',
256 OWNER 'root');
257 connection master;
258 flush tables;
259 select * from federated.t1;
260 id name
261 1 this is legitimate
262 connection slave;
263 drop user test_fed@localhost;
264 drop database db_legitimate;
265 drop database db_bogus;
266 disconnect conn_select;
267 disconnect conn_usage;
268 disconnect conn_super;
269 connection master;
270 drop user guest_super@localhost;
271 drop user guest_usage@localhost;
272 drop user guest_select@localhost;
273 drop table federated.t1;
274 drop server 's1';
275 create server 's1' foreign data wrapper 'mysql' options (host 'foo');
276 alter server 's1' options
277 (host 'localhost', database '', user '',
278 password '', socket '', owner '', port 3306);
279 alter server 's1' options
280 (host 'localhost', database 'database1', user '',
281 password '', socket '', owner '', port 3306);
282 drop server 's1';
283 # End of 5.1 tests
284 connect other,localhost,root,,;
285 connection master;
286 use test;
287 create table t1(a int);
288 insert into t1 values (1);
289 create procedure p1 ()
290 begin
291 DECLARE v INT DEFAULT 0;
292 DECLARE i INT;
293 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
294 SET i = sleep(5);
295 WHILE v < 10000 do
296 CREATE SERVER s
297 FOREIGN DATA WRAPPER mysql
298 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
299 ALTER SERVER s OPTIONS (USER 'Remote');
300 DROP SERVER s;
301 SET v = v + 1;
302 END WHILE;
303 select a into @a from t1; # Just reset warnings
304 END//
305 connection other;
306 use test;
307 call p1();
308 connection master;
309 call p1();
310 connection other;
311 drop procedure p1;
312 drop table t1;
313 drop server if exists s;
314 connection master;
315 DROP TABLE IF EXISTS federated.t1;
316 DROP DATABASE IF EXISTS federated;
317 connection slave;
318 DROP TABLE IF EXISTS federated.t1;
319 DROP DATABASE IF EXISTS federated;