1 # WL #3031 This test tests the new servers table as well as
2 # if federated can utilise the servers table
3 -- source suite/federated/include/federated.inc
6 create database first_db;
7 create database second_db;
11 DROP TABLE IF EXISTS first_db.t1;
12 CREATE TABLE first_db.t1 (
13 `id` int(20) NOT NULL,
14 `name` varchar(64) NOT NULL default ''
16 DEFAULT CHARSET=latin1;
18 DROP TABLE IF EXISTS first_db.t2;
19 CREATE TABLE first_db.t2 (
20 `id` int(20) NOT NULL,
21 `name` varchar(64) NOT NULL default ''
23 DEFAULT CHARSET=latin1;
26 DROP TABLE IF EXISTS second_db.t1;
27 CREATE TABLE second_db.t1 (
28 `id` int(20) NOT NULL,
29 `name` varchar(64) NOT NULL default ''
31 DEFAULT CHARSET=latin1;
33 DROP TABLE IF EXISTS second_db.t2;
34 CREATE TABLE second_db.t2 (
35 `id` int(20) NOT NULL,
36 `name` varchar(64) NOT NULL default ''
38 DEFAULT CHARSET=latin1;
42 drop server if exists 'server_one';
43 --replace_result $SLAVE_MYPORT SLAVE_PORT
44 eval create server 'server_one' foreign data wrapper 'mysql' options
53 drop server if exists 'server_two';
54 --replace_result $SLAVE_MYPORT SLAVE_PORT
55 eval create server 'server_two' foreign data wrapper 'mysql' options
64 --replace_result $SLAVE_MYPORT SLAVE_PORT
65 # Adding 'order by' clause here, in order to maintain consistent result out of the select query.
66 #eval select * from mysql.servers;
67 eval select * from mysql.servers order by Server_name;
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 ''
75 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
76 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/first_db/t1';
78 INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
80 SELECT * FROM federated.old;
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 ''
88 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
89 CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/first_db/t2';
91 INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
92 SELECT * FROM federated.old2;
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 ''
100 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
101 CONNECTION='mysql://root@127.0.0.1:$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;
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 ''
111 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
112 CONNECTION='mysql://root@127.0.0.1:$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;
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 ''
121 ENGINE="FEDERATED" DEFAULT CHARSET=latin1
122 CONNECTION='server_one';
124 INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
126 SELECT * FROM federated.t1;
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 ''
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;
138 ALTER SERVER 'server_one' options(DATABASE 'second_db');
140 # FLUSH TABLES is now unneccessary
142 INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
143 SELECT * FROM federated.t1;
145 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
146 SELECT * FROM federated.whatever;
148 drop table federated.t1;
150 drop server 'server_one';
151 drop server 'server_two';
152 # Adding 'order by' clause here, in order to maintain consistent result out of the select query.
153 #select * from mysql.servers;
154 eval select * from mysql.servers order by Server_name;
157 drop table first_db.t1;
158 drop table second_db.t1;
159 drop database first_db;
160 drop database second_db;
163 # Bug#25671 - CREATE/DROP/ALTER SERVER should require privileges
165 # Changes to SERVER declarations should require SUPER privilege.
166 # Based upon test case by Giuseppe Maxia
168 create database db_legitimate;
169 create database db_bogus;
172 CREATE TABLE db_legitimate.t1 (
173 `id` int(20) NOT NULL,
174 `name` varchar(64) NOT NULL default ''
176 INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
179 CREATE TABLE db_bogus.t1 (
180 `id` int(20) NOT NULL,
181 `name` varchar(64) NOT NULL default ''
184 INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
187 --replace_result $SLAVE_MYPORT SLAVE_PORT
188 eval create server 's1' foreign data wrapper 'mysql' options
190 DATABASE 'db_legitimate',
197 create user guest_select@localhost;
198 grant select on federated.* to guest_select@localhost;
200 create user guest_super@localhost;
201 grant select,SUPER,RELOAD on *.* to guest_super@localhost;
203 create user guest_usage@localhost;
204 grant usage on *.* to guest_usage@localhost;
206 CREATE TABLE federated.t1 (
207 `id` int(20) NOT NULL,
208 `name` varchar(64) NOT NULL default ''
209 ) ENGINE = FEDERATED CONNECTION = 's1';
211 select * from federated.t1;
213 connect (conn_select,127.0.0.1,guest_select,,federated,$MASTER_MYPORT);
214 connect (conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT);
215 connect (conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT);
217 connection conn_select;
218 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
219 alter server s1 options (database 'db_bogus');
223 select * from federated.t1;
225 connection conn_usage;
226 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
227 alter server s1 options (database 'db_bogus');
231 select * from federated.t1;
233 connection conn_super;
234 alter server s1 options (database 'db_bogus');
238 select * from federated.t1;
240 connection conn_select;
241 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
242 drop server if exists 's1';
243 --replace_result $SLAVE_MYPORT SLAVE_PORT
244 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
245 eval create server 's1' foreign data wrapper 'mysql' options
247 DATABASE 'db_legitimate',
254 connection conn_super;
256 --replace_result $SLAVE_MYPORT SLAVE_PORT
257 eval create server 's1' foreign data wrapper 'mysql' options
259 DATABASE 'db_legitimate',
268 select * from federated.t1;
272 drop database db_legitimate;
273 drop database db_bogus;
275 disconnect conn_select;
276 disconnect conn_usage;
277 disconnect conn_super;
280 drop user guest_super@localhost;
281 drop user guest_usage@localhost;
282 drop user guest_select@localhost;
283 drop table federated.t1;
287 # Bug#30671 - ALTER SERVER causes the server to crash
289 create server 's1' foreign data wrapper 'mysql' options (port 3306);
290 alter server 's1' options
291 (host 'localhost', database '', user '',
292 password '', socket '', owner '', port 3306);
293 # The next statement would crash unpatched server
294 alter server 's1' options
295 (host 'localhost', database 'database1', user '',
296 password '', socket '', owner '', port 3306);
299 --echo # End of 5.1 tests
303 # Bug#25721 - deadlock with ALTER/CREATE SERVER
305 connect (other,localhost,root,,);
309 create procedure p1 ()
311 DECLARE v INT DEFAULT 0;
313 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
317 FOREIGN DATA WRAPPER mysql
318 OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
319 ALTER SERVER s OPTIONS (USER 'Remote');
333 drop server if exists s;
336 source suite/federated/include/federated_cleanup.inc;