diff options
Diffstat (limited to 'mysql-test/suite/federated/federated_server.result')
| -rw-r--r-- | mysql-test/suite/federated/federated_server.result | 319 |
1 files changed, 319 insertions, 0 deletions
diff --git a/mysql-test/suite/federated/federated_server.result b/mysql-test/suite/federated/federated_server.result new file mode 100644 index 00000000..d25676e7 --- /dev/null +++ b/mysql-test/suite/federated/federated_server.result @@ -0,0 +1,319 @@ +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; |