connect master,127.0.0.1,root,,test,$MASTER_MYPORT,; connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; connection master; CREATE DATABASE federated; connection slave; CREATE DATABASE federated; connection slave; create database first_db; create database second_db; use first_db; DROP TABLE IF EXISTS first_db.t1; Warnings: Note 1051 Unknown table 'first_db.t1' CREATE TABLE first_db.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS first_db.t2; Warnings: Note 1051 Unknown table 'first_db.t2' CREATE TABLE first_db.t2 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) DEFAULT CHARSET=latin1; use second_db; DROP TABLE IF EXISTS second_db.t1; Warnings: Note 1051 Unknown table 'second_db.t1' CREATE TABLE second_db.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) DEFAULT CHARSET=latin1; DROP TABLE IF EXISTS second_db.t2; Warnings: Note 1051 Unknown table 'second_db.t2' CREATE TABLE second_db.t2 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) DEFAULT CHARSET=latin1; connection master; drop server if exists 'server_one'; create server 'server_one' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'first_db', USER 'root', PASSWORD '', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); drop server if exists 'server_two'; create server 'server_two' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'second_db', USER 'root', PASSWORD '', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); select * from mysql.servers order by db; Server_name Host Db Username Password Port Socket Wrapper Owner server_one 127.0.0.1 first_db root SLAVE_PORT mysql root server_two 127.0.0.1 second_db root SLAVE_PORT mysql root DROP TABLE IF EXISTS federated.old; Warnings: Note 1051 Unknown table 'federated.old' CREATE TABLE federated.old ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t1'; INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format'); SELECT * FROM federated.old; id name 1 federated.old-> first_db.t1, url format DROP TABLE IF EXISTS federated.old2; Warnings: Note 1051 Unknown table 'federated.old2' CREATE TABLE federated.old2 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t2'; INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format'); SELECT * FROM federated.old2; id name 1 federated.old2-> first_db.t2, url format DROP TABLE IF EXISTS federated.urldb2t1; Warnings: Note 1051 Unknown table 'federated.urldb2t1' CREATE TABLE federated.urldb2t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t1'; INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format'); SELECT * FROM federated.urldb2t1; id name 1 federated.urldb2t1 -> second_db.t1, url format DROP TABLE IF EXISTS federated.urldb2t2; Warnings: Note 1051 Unknown table 'federated.urldb2t2' CREATE TABLE federated.urldb2t2 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t2'; INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format'); SELECT * FROM federated.urldb2t2; id name 1 federated.urldb2t2 -> second_db.t2, url format DROP TABLE IF EXISTS federated.t1; Warnings: Note 1051 Unknown table 'federated.t1' CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='server_one'; INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1'); SELECT * FROM federated.t1; id name 1 federated.old-> first_db.t1, url format 1 server_one, new scheme, first_db.t1 DROP TABLE IF EXISTS federated.whatever; Warnings: Note 1051 Unknown table 'federated.whatever' CREATE TABLE federated.whatever ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE="FEDERATED" DEFAULT CHARSET=latin1 CONNECTION='server_one/t1'; INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1'); SELECT * FROM federated.whatever; id name 1 federated.old-> first_db.t1, url format 1 server_one, new scheme, first_db.t1 1 server_one, new scheme, whatever, first_db.t1 ALTER SERVER 'server_one' options(DATABASE 'second_db'); INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1'); SELECT * FROM federated.t1; id name 1 federated.urldb2t1 -> second_db.t1, url format 1 server_two, new scheme, second_db.t1 INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1'); SELECT * FROM federated.whatever; id name 1 federated.urldb2t1 -> second_db.t1, url format 1 server_two, new scheme, second_db.t1 1 server_two, new scheme, whatever, second_db.t1 drop table federated.t1; drop server 'server_one'; drop server 'server_two'; select * from mysql.servers order by db; Server_name Host Db Username Password Port Socket Wrapper Owner connection slave; drop table first_db.t1; drop table second_db.t1; drop database first_db; drop database second_db; create database db_legitimate; create database db_bogus; use db_legitimate; CREATE TABLE db_legitimate.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ); INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate'); use db_bogus; CREATE TABLE db_bogus.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ; INSERT INTO db_bogus.t1 VALUES ('2','this is bogus'); connection slave; create user test_fed@localhost identified by 'foo'; grant all on db_legitimate.* to test_fed@localhost; connection master; create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'test_fed', PASSWORD 'foo', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); create user guest_select@localhost; grant select on federated.* to guest_select@localhost; create user guest_super@localhost; grant select,SUPER,RELOAD on *.* to guest_super@localhost; create user guest_usage@localhost; grant usage on *.* to guest_usage@localhost; CREATE TABLE federated.t1 ( `id` int(20) NOT NULL, `name` varchar(64) NOT NULL default '' ) ENGINE = FEDERATED CONNECTION = 's1'; select * from federated.t1; id name 1 this is legitimate connect conn_select,127.0.0.1,guest_select,,federated,$MASTER_MYPORT; connect conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT; connect conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT; connection conn_select; alter server s1 options (database 'db_bogus'); ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation connection master; flush tables; select * from federated.t1; id name 1 this is legitimate connection conn_usage; alter server s1 options (database 'db_bogus'); ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation connection master; flush tables; select * from federated.t1; id name 1 this is legitimate connection conn_super; alter server s1 options (database 'db_bogus'); connection master; flush tables; select * from federated.t1; Got one of the listed errors connection conn_select; drop server if exists 's1'; ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'test_fed', PASSWORD 'foo', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); ERROR 42000: Access denied; you need (at least one of) the SUPER, FEDERATED ADMIN privilege(s) for this operation connection conn_super; drop server 's1'; create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'test_fed', PASSWORD 'foo', PORT SLAVE_PORT, SOCKET '', OWNER 'root'); connection master; flush tables; select * from federated.t1; id name 1 this is legitimate connection slave; drop user test_fed@localhost; drop database db_legitimate; drop database db_bogus; disconnect conn_select; disconnect conn_usage; disconnect conn_super; connection master; drop user guest_super@localhost; drop user guest_usage@localhost; drop user guest_select@localhost; drop table federated.t1; drop server 's1'; create server 's1' foreign data wrapper 'mysql' options (host 'foo'); alter server 's1' options (host 'localhost', database '', user '', password '', socket '', owner '', port 3306); alter server 's1' options (host 'localhost', database 'database1', user '', password '', socket '', owner '', port 3306); drop server 's1'; # End of 5.1 tests connect other,localhost,root,,; connection master; use test; create table t1(a int); insert into t1 values (1); create procedure p1 () begin DECLARE v INT DEFAULT 0; DECLARE i INT; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET i = sleep(5); WHILE v < 10000 do CREATE SERVER s FOREIGN DATA WRAPPER mysql OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test'); ALTER SERVER s OPTIONS (USER 'Remote'); DROP SERVER s; SET v = v + 1; END WHILE; select a into @a from t1; # Just reset warnings END// connection other; use test; call p1(); connection master; call p1(); connection other; drop procedure p1; drop table t1; drop server if exists s; connection master; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; connection slave; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated;