Debian Codegraph @

mysql-test/suite/federated/r/federated_server.result

raw
1 CREATE DATABASE federated;
2 CREATE DATABASE federated;
3 create database first_db;
4 create database second_db;
5 use first_db;
6 DROP TABLE IF EXISTS first_db.t1;
7 Warnings:
8 Note 1051 Unknown table 'first_db.t1'
9 CREATE TABLE first_db.t1 (
10 `id` int(20) NOT NULL,
11 `name` varchar(64) NOT NULL default ''
12 )
13 DEFAULT CHARSET=latin1;
14 Warnings:
15 Warning 1681 Integer display width is deprecated and will be removed in a future release.
16 DROP TABLE IF EXISTS first_db.t2;
17 Warnings:
18 Note 1051 Unknown table 'first_db.t2'
19 CREATE TABLE first_db.t2 (
20 `id` int(20) NOT NULL,
21 `name` varchar(64) NOT NULL default ''
22 )
23 DEFAULT CHARSET=latin1;
24 Warnings:
25 Warning 1681 Integer display width is deprecated and will be removed in a future release.
26 use second_db;
27 DROP TABLE IF EXISTS second_db.t1;
28 Warnings:
29 Note 1051 Unknown table 'second_db.t1'
30 CREATE TABLE second_db.t1 (
31 `id` int(20) NOT NULL,
32 `name` varchar(64) NOT NULL default ''
33 )
34 DEFAULT CHARSET=latin1;
35 Warnings:
36 Warning 1681 Integer display width is deprecated and will be removed in a future release.
37 DROP TABLE IF EXISTS second_db.t2;
38 Warnings:
39 Note 1051 Unknown table 'second_db.t2'
40 CREATE TABLE second_db.t2 (
41 `id` int(20) NOT NULL,
42 `name` varchar(64) NOT NULL default ''
43 )
44 DEFAULT CHARSET=latin1;
45 Warnings:
46 Warning 1681 Integer display width is deprecated and will be removed in a future release.
47 drop server if exists 'server_one';
48 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_PORT,
54 SOCKET '',
55 OWNER 'root');
56 drop server if exists 'server_two';
57 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_PORT,
63 SOCKET '',
64 OWNER 'root');
65 select * from mysql.servers order by Server_name;
66 Server_name Host Db Username Password Port Socket Wrapper Owner
67 server_one 127.0.0.1 first_db root SLAVE_PORT mysql root
68 server_two 127.0.0.1 second_db root SLAVE_PORT mysql root
69 DROP TABLE IF EXISTS federated.old;
70 Warnings:
71 Note 1051 Unknown table 'federated.old'
72 CREATE TABLE federated.old (
73 `id` int(20) NOT NULL,
74 `name` varchar(64) NOT NULL default ''
75 )
76 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
77 CONNECTION='mysql://[email protected]:SLAVE_PORT/first_db/t1';
78 Warnings:
79 Warning 1681 Integer display width is deprecated and will be removed in a future release.
80 INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
81 SELECT * FROM federated.old;
82 id name
83 1 federated.old-> first_db.t1, url format
84 DROP TABLE IF EXISTS federated.old2;
85 Warnings:
86 Note 1051 Unknown table 'federated.old2'
87 CREATE TABLE federated.old2 (
88 `id` int(20) NOT NULL,
89 `name` varchar(64) NOT NULL default ''
90 )
91 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
92 CONNECTION='mysql://[email protected]:SLAVE_PORT/first_db/t2';
93 Warnings:
94 Warning 1681 Integer display width is deprecated and will be removed in a future release.
95 INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
96 SELECT * FROM federated.old2;
97 id name
98 1 federated.old2-> first_db.t2, url format
99 DROP TABLE IF EXISTS federated.urldb2t1;
100 Warnings:
101 Note 1051 Unknown table 'federated.urldb2t1'
102 CREATE TABLE federated.urldb2t1 (
103 `id` int(20) NOT NULL,
104 `name` varchar(64) NOT NULL default ''
105 )
106 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
107 CONNECTION='mysql://[email protected]:SLAVE_PORT/second_db/t1';
108 Warnings:
109 Warning 1681 Integer display width is deprecated and will be removed in a future release.
110 INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format');
111 SELECT * FROM federated.urldb2t1;
112 id name
113 1 federated.urldb2t1 -> second_db.t1, url format
114 DROP TABLE IF EXISTS federated.urldb2t2;
115 Warnings:
116 Note 1051 Unknown table 'federated.urldb2t2'
117 CREATE TABLE federated.urldb2t2 (
118 `id` int(20) NOT NULL,
119 `name` varchar(64) NOT NULL default ''
120 )
121 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
122 CONNECTION='mysql://[email protected]:SLAVE_PORT/second_db/t2';
123 Warnings:
124 Warning 1681 Integer display width is deprecated and will be removed in a future release.
125 INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format');
126 SELECT * FROM federated.urldb2t2;
127 id name
128 1 federated.urldb2t2 -> second_db.t2, url format
129 DROP TABLE IF EXISTS federated.t1;
130 Warnings:
131 Note 1051 Unknown table 'federated.t1'
132 CREATE TABLE federated.t1 (
133 `id` int(20) NOT NULL,
134 `name` varchar(64) NOT NULL default ''
135 )
136 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
137 CONNECTION='server_one';
138 Warnings:
139 Warning 1681 Integer display width is deprecated and will be removed in a future release.
140 INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
141 SELECT * FROM federated.t1;
142 id name
143 1 federated.old-> first_db.t1, url format
144 1 server_one, new scheme, first_db.t1
145 DROP TABLE IF EXISTS federated.whatever;
146 Warnings:
147 Note 1051 Unknown table 'federated.whatever'
148 CREATE TABLE federated.whatever (
149 `id` int(20) NOT NULL,
150 `name` varchar(64) NOT NULL default ''
151 )
152 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
153 CONNECTION='server_one/t1';
154 Warnings:
155 Warning 1681 Integer display width is deprecated and will be removed in a future release.
156 INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
157 SELECT * FROM federated.whatever;
158 id name
159 1 federated.old-> first_db.t1, url format
160 1 server_one, new scheme, first_db.t1
161 1 server_one, new scheme, whatever, first_db.t1
162 ALTER SERVER 'server_one' options(DATABASE 'second_db');
163 INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
164 SELECT * FROM federated.t1;
165 id name
166 1 federated.urldb2t1 -> second_db.t1, url format
167 1 server_two, new scheme, second_db.t1
168 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
169 SELECT * FROM federated.whatever;
170 id name
171 1 federated.urldb2t1 -> second_db.t1, url format
172 1 server_two, new scheme, second_db.t1
173 1 server_two, new scheme, whatever, second_db.t1
174 drop table federated.t1;
175 drop server 'server_one';
176 drop server 'server_two';
177 select * from mysql.servers order by Server_name;
178 Server_name Host Db Username Password Port Socket Wrapper Owner
179 drop table first_db.t1;
180 drop table second_db.t1;
181 drop database first_db;
182 drop database second_db;
183 create database db_legitimate;
184 create database db_bogus;
185 use db_legitimate;
186 CREATE TABLE db_legitimate.t1 (
187 `id` int(20) NOT NULL,
188 `name` varchar(64) NOT NULL default ''
189 );
190 Warnings:
191 Warning 1681 Integer display width is deprecated and will be removed in a future release.
192 INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
193 use db_bogus;
194 CREATE TABLE db_bogus.t1 (
195 `id` int(20) NOT NULL,
196 `name` varchar(64) NOT NULL default ''
197 )
198 ;
199 Warnings:
200 Warning 1681 Integer display width is deprecated and will be removed in a future release.
201 INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
202 create server 's1' foreign data wrapper 'mysql' options
203 (HOST '127.0.0.1',
204 DATABASE 'db_legitimate',
205 USER 'root',
206 PASSWORD '',
207 PORT SLAVE_PORT,
208 SOCKET '',
209 OWNER 'root');
210 create user guest_select@localhost;
211 grant select on federated.* to guest_select@localhost;
212 create user guest_super@localhost;
213 grant select,SUPER,RELOAD on *.* to guest_super@localhost;
214 Warnings:
215 Warning 1287 The SUPER privilege identifier is deprecated
216 create user guest_usage@localhost;
217 grant usage on *.* to guest_usage@localhost;
218 CREATE TABLE federated.t1 (
219 `id` int(20) NOT NULL,
220 `name` varchar(64) NOT NULL default ''
221 ) ENGINE = FEDERATED CONNECTION = 's1';
222 Warnings:
223 Warning 1681 Integer display width is deprecated and will be removed in a future release.
224 select * from federated.t1;
225 id name
226 1 this is legitimate
227 alter server s1 options (database 'db_bogus');
228 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
229 flush tables;
230 select * from federated.t1;
231 id name
232 1 this is legitimate
233 alter server s1 options (database 'db_bogus');
234 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
235 flush tables;
236 select * from federated.t1;
237 id name
238 1 this is legitimate
239 alter server s1 options (database 'db_bogus');
240 flush tables;
241 select * from federated.t1;
242 id name
243 2 this is bogus
244 drop server if exists 's1';
245 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
246 create server 's1' foreign data wrapper 'mysql' options
247 (HOST '127.0.0.1',
248 DATABASE 'db_legitimate',
249 USER 'root',
250 PASSWORD '',
251 PORT SLAVE_PORT,
252 SOCKET '',
253 OWNER 'root');
254 ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
255 drop server 's1';
256 create server 's1' foreign data wrapper 'mysql' options
257 (HOST '127.0.0.1',
258 DATABASE 'db_legitimate',
259 USER 'root',
260 PASSWORD '',
261 PORT SLAVE_PORT,
262 SOCKET '',
263 OWNER 'root');
264 flush tables;
265 select * from federated.t1;
266 id name
267 1 this is legitimate
268 drop database db_legitimate;
269 drop database db_bogus;
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 (port 3306);
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 use test;
285 create table t1(a int);
286 insert into t1 values (1);
287 create procedure p1 ()
288 begin
289 DECLARE v INT DEFAULT 0;
290 DECLARE i INT;
291 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
292 SET i = sleep(5);
293 WHILE v < 10000 do
294 CREATE SERVER s
295 FOREIGN DATA WRAPPER mysql
296 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
297 ALTER SERVER s OPTIONS (USER 'Remote');
298 DROP SERVER s;
299 SET v = v + 1;
300 END WHILE;
301 select a into @a from t1; # Just reset warnings
302 END//
303 use test;
304 call p1();
305 call p1();
306 drop procedure p1;
307 drop table t1;
308 drop server if exists s;
309 create database db_remote;
310 use db_remote;
311 CREATE TABLE db_remote.t1 (
312 `id` int(20) NOT NULL,
313 `name` varchar(64) NOT NULL default ''
314 );
315 Warnings:
316 Warning 1681 Integer display width is deprecated and will be removed in a future release.
317 INSERT INTO db_remote.t1 VALUES ('1','this is remote');
318 SET NAMES utf8;
319 Warnings:
320 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
321 CREATE USER очень_очень_очень_длинный_юзер__@localhost IDENTIFIED WITH 'mysql_native_password' BY 'auth_string';
322 grant all on *.* to очень_очень_очень_длинный_юзер__@localhost;
323 SET NAMES utf8;
324 Warnings:
325 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
326 create server 's1' foreign data wrapper 'mysql' options
327 (HOST '127.0.0.1',
328 DATABASE 'db_remote',
329 USER 'очень_очень_очень_длинный_юзер__',
330 PASSWORD 'auth_string',
331 PORT SLAVE_PORT,
332 SOCKET '',
333 OWNER 'очень_очень_очень_длинный_юзер__');
334 create user user_name_len_32_012345678901234@localhost;
335 grant select on federated.* to user_name_len_32_012345678901234@localhost;
336 CREATE TABLE federated.t1 (
337 `id` int(20) NOT NULL,
338 `name` varchar(64) NOT NULL default ''
339 ) ENGINE = FEDERATED CONNECTION = 's1' CHARACTER SET utf8;
340 Warnings:
341 Warning 1681 Integer display width is deprecated and will be removed in a future release.
342 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
343 select * from federated.t1;
344 id name
345 1 this is remote
346 SET NAMES utf8;
347 Warnings:
348 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
349 select * from federated.t1;
350 id name
351 1 this is remote
352 drop database db_remote;
353 drop user очень_очень_очень_длинный_юзер__@localhost;
354 SET NAMES default;
355 drop user user_name_len_32_012345678901234@localhost;
356 drop table federated.t1;
357 drop server 's1';
358 SET NAMES default;
359 DROP TABLE IF EXISTS federated.t1;
360 DROP DATABASE federated;
361 DROP TABLE IF EXISTS federated.t1;
362 DROP DATABASE federated;