1 CREATE DATABASE federated;
2 CREATE DATABASE federated;
3 create database first_db;
4 create database second_db;
6 DROP TABLE IF EXISTS first_db.t1;
8 Note 1051 Unknown table 't1'
9 CREATE TABLE first_db.t1 (
10 `id` int(20) NOT NULL,
11 `name` varchar(64) NOT NULL default ''
13 DEFAULT CHARSET=latin1;
14 DROP TABLE IF EXISTS first_db.t2;
16 Note 1051 Unknown table 't2'
17 CREATE TABLE first_db.t2 (
18 `id` int(20) NOT NULL,
19 `name` varchar(64) NOT NULL default ''
21 DEFAULT CHARSET=latin1;
23 DROP TABLE IF EXISTS second_db.t1;
25 Note 1051 Unknown table 't1'
26 CREATE TABLE second_db.t1 (
27 `id` int(20) NOT NULL,
28 `name` varchar(64) NOT NULL default ''
30 DEFAULT CHARSET=latin1;
31 DROP TABLE IF EXISTS second_db.t2;
33 Note 1051 Unknown table 't2'
34 CREATE TABLE second_db.t2 (
35 `id` int(20) NOT NULL,
36 `name` varchar(64) NOT NULL default ''
38 DEFAULT CHARSET=latin1;
39 drop server if exists 'server_one';
40 create server 'server_one' foreign data wrapper 'mysql' options
48 drop server if exists 'server_two';
49 create server 'server_two' foreign data wrapper 'mysql' options
57 select * from mysql.servers order by Server_name;
58 Server_name Host Db Username Password Port Socket Wrapper Owner
59 server_one 127.0.0.1 first_db root SLAVE_PORT mysql root
60 server_two 127.0.0.1 second_db root SLAVE_PORT mysql root
61 DROP TABLE IF EXISTS federated.old;
63 Note 1051 Unknown table 'old'
64 CREATE TABLE federated.old (
65 `id` int(20) NOT NULL,
66 `name` varchar(64) NOT NULL default ''
68 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
69 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t1';
70 INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
71 SELECT * FROM federated.old;
73 1 federated.old-> first_db.t1, url format
74 DROP TABLE IF EXISTS federated.old2;
76 Note 1051 Unknown table 'old2'
77 CREATE TABLE federated.old2 (
78 `id` int(20) NOT NULL,
79 `name` varchar(64) NOT NULL default ''
81 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
82 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t2';
83 INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
84 SELECT * FROM federated.old2;
86 1 federated.old2-> first_db.t2, url format
87 DROP TABLE IF EXISTS federated.urldb2t1;
89 Note 1051 Unknown table 'urldb2t1'
90 CREATE TABLE federated.urldb2t1 (
91 `id` int(20) NOT NULL,
92 `name` varchar(64) NOT NULL default ''
94 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
95 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t1';
96 INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format');
97 SELECT * FROM federated.urldb2t1;
99 1 federated.urldb2t1 -> second_db.t1, url format
100 DROP TABLE IF EXISTS federated.urldb2t2;
102 Note 1051 Unknown table 'urldb2t2'
103 CREATE TABLE federated.urldb2t2 (
104 `id` int(20) NOT NULL,
105 `name` varchar(64) NOT NULL default ''
107 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
108 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t2';
109 INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format');
110 SELECT * FROM federated.urldb2t2;
112 1 federated.urldb2t2 -> second_db.t2, url format
113 DROP TABLE IF EXISTS federated.t1;
115 Note 1051 Unknown table 't1'
116 CREATE TABLE federated.t1 (
117 `id` int(20) NOT NULL,
118 `name` varchar(64) NOT NULL default ''
120 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
121 CONNECTION='server_one';
122 INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
123 SELECT * FROM federated.t1;
125 1 federated.old-> first_db.t1, url format
126 1 server_one, new scheme, first_db.t1
127 DROP TABLE IF EXISTS federated.whatever;
129 Note 1051 Unknown table 'whatever'
130 CREATE TABLE federated.whatever (
131 `id` int(20) NOT NULL,
132 `name` varchar(64) NOT NULL default ''
134 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
135 CONNECTION='server_one/t1';
136 INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
137 SELECT * FROM federated.whatever;
139 1 federated.old-> first_db.t1, url format
140 1 server_one, new scheme, first_db.t1
141 1 server_one, new scheme, whatever, first_db.t1
142 ALTER SERVER 'server_one' options(DATABASE 'second_db');
143 INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
144 SELECT * FROM federated.t1;
146 1 federated.urldb2t1 -> second_db.t1, url format
147 1 server_two, new scheme, second_db.t1
148 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
149 SELECT * FROM federated.whatever;
151 1 federated.urldb2t1 -> second_db.t1, url format
152 1 server_two, new scheme, second_db.t1
153 1 server_two, new scheme, whatever, second_db.t1
154 drop table federated.t1;
155 drop server 'server_one';
156 drop server 'server_two';
157 select * from mysql.servers order by Server_name;
158 Server_name Host Db Username Password Port Socket Wrapper Owner
159 drop table first_db.t1;
160 drop table second_db.t1;
161 drop database first_db;
162 drop database second_db;
163 create database db_legitimate;
164 create database db_bogus;
166 CREATE TABLE db_legitimate.t1 (
167 `id` int(20) NOT NULL,
168 `name` varchar(64) NOT NULL default ''
170 INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
172 CREATE TABLE db_bogus.t1 (
173 `id` int(20) NOT NULL,
174 `name` varchar(64) NOT NULL default ''
177 INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
178 create server 's1' foreign data wrapper 'mysql' options
180 DATABASE 'db_legitimate',
186 create user guest_select@localhost;
187 grant select on federated.* to guest_select@localhost;
188 create user guest_super@localhost;
189 grant select,SUPER,RELOAD on *.* to guest_super@localhost;
190 create user guest_usage@localhost;
191 grant usage on *.* to guest_usage@localhost;
192 CREATE TABLE federated.t1 (
193 `id` int(20) NOT NULL,
194 `name` varchar(64) NOT NULL default ''
195 ) ENGINE = FEDERATED CONNECTION = 's1';
196 select * from federated.t1;
199 alter server s1 options (database 'db_bogus');
200 ERROR 42000: Access denied; you need the SUPER privilege for this operation
202 select * from federated.t1;
205 alter server s1 options (database 'db_bogus');
206 ERROR 42000: Access denied; you need the SUPER privilege for this operation
208 select * from federated.t1;
211 alter server s1 options (database 'db_bogus');
213 select * from federated.t1;
216 drop server if exists 's1';
217 ERROR 42000: Access denied; you need the SUPER privilege for this operation
218 create server 's1' foreign data wrapper 'mysql' options
220 DATABASE 'db_legitimate',
226 ERROR 42000: Access denied; you need the SUPER privilege for this operation
228 create server 's1' foreign data wrapper 'mysql' options
230 DATABASE 'db_legitimate',
237 select * from federated.t1;
240 drop database db_legitimate;
241 drop database db_bogus;
242 drop user guest_super@localhost;
243 drop user guest_usage@localhost;
244 drop user guest_select@localhost;
245 drop table federated.t1;
247 create server 's1' foreign data wrapper 'mysql' options (port 3306);
248 alter server 's1' options
249 (host 'localhost', database '', user '',
250 password '', socket '', owner '', port 3306);
251 alter server 's1' options
252 (host 'localhost', database 'database1', user '',
253 password '', socket '', owner '', port 3306);
257 create procedure p1 ()
259 DECLARE v INT DEFAULT 0;
261 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
265 FOREIGN DATA WRAPPER mysql
266 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
267 ALTER SERVER s OPTIONS (USER 'Remote');
276 drop server if exists s;
277 DROP TABLE IF EXISTS federated.t1;
278 DROP DATABASE federated;
279 DROP TABLE IF EXISTS federated.t1;
280 DROP DATABASE federated;