# WL #3031 This test tests the new servers table as well as # if federated can utilise the servers table -- source federated.inc connection slave; create database first_db; create database second_db; use first_db; DROP TABLE IF EXISTS 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; 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; 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; 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'; --replace_result $SLAVE_MYPORT SLAVE_PORT eval create server 'server_one' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'first_db', USER 'root', PASSWORD '', PORT $SLAVE_MYPORT, SOCKET '', OWNER 'root'); drop server if exists 'server_two'; --replace_result $SLAVE_MYPORT SLAVE_PORT eval create server 'server_two' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'second_db', USER 'root', PASSWORD '', PORT $SLAVE_MYPORT, SOCKET '', OWNER 'root'); --replace_result $SLAVE_MYPORT SLAVE_PORT eval select * from mysql.servers; DROP TABLE IF EXISTS federated.old; --replace_result $SLAVE_MYPORT SLAVE_PORT eval 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_MYPORT/first_db/t1'; INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format'); SELECT * FROM federated.old; DROP TABLE IF EXISTS federated.old2; --replace_result $SLAVE_MYPORT SLAVE_PORT eval 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_MYPORT/first_db/t2'; INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format'); SELECT * FROM federated.old2; DROP TABLE IF EXISTS federated.urldb2t1; --replace_result $SLAVE_MYPORT SLAVE_PORT eval 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_MYPORT/second_db/t1'; INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format'); SELECT * FROM federated.urldb2t1; DROP TABLE IF EXISTS federated.urldb2t2; --replace_result $SLAVE_MYPORT SLAVE_PORT eval 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_MYPORT/second_db/t2'; INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format'); SELECT * FROM federated.urldb2t2; DROP TABLE IF EXISTS 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; DROP TABLE IF EXISTS 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; ALTER SERVER 'server_one' options(DATABASE 'second_db'); # FLUSH TABLES is now unneccessary INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1'); SELECT * FROM federated.t1; INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1'); SELECT * FROM federated.whatever; drop table federated.t1; drop server 'server_one'; drop server 'server_two'; select * from mysql.servers; connection slave; drop table first_db.t1; drop table second_db.t1; drop database first_db; drop database second_db; # # Bug#25671 - CREATE/DROP/ALTER SERVER should require privileges # # Changes to SERVER declarations should require SUPER privilege. # Based upon test case by Giuseppe Maxia 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 master; --replace_result $SLAVE_MYPORT SLAVE_PORT eval create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'root', PASSWORD '', PORT $SLAVE_MYPORT, 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; 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; --error ER_SPECIFIC_ACCESS_DENIED_ERROR alter server s1 options (database 'db_bogus'); connection master; flush tables; select * from federated.t1; connection conn_usage; --error ER_SPECIFIC_ACCESS_DENIED_ERROR alter server s1 options (database 'db_bogus'); connection master; flush tables; select * from federated.t1; connection conn_super; alter server s1 options (database 'db_bogus'); connection master; flush tables; select * from federated.t1; connection conn_select; --error ER_SPECIFIC_ACCESS_DENIED_ERROR drop server if exists 's1'; --replace_result $SLAVE_MYPORT SLAVE_PORT --error ER_SPECIFIC_ACCESS_DENIED_ERROR eval create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'root', PASSWORD '', PORT $SLAVE_MYPORT, SOCKET '', OWNER 'root'); connection conn_super; drop server 's1'; --replace_result $SLAVE_MYPORT SLAVE_PORT eval create server 's1' foreign data wrapper 'mysql' options (HOST '127.0.0.1', DATABASE 'db_legitimate', USER 'root', PASSWORD '', PORT $SLAVE_MYPORT, SOCKET '', OWNER 'root'); connection master; flush tables; select * from federated.t1; # clean up test connection slave; 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'; # # Bug#30671 - ALTER SERVER causes the server to crash # create server 's1' foreign data wrapper 'mysql' options (port 3306); alter server 's1' options (host 'localhost', database '', user '', password '', socket '', owner '', port 3306); # The next statement would crash unpatched server alter server 's1' options (host 'localhost', database 'database1', user '', password '', socket '', owner '', port 3306); drop server 's1'; --echo # End of 5.1 tests # # Bug#25721 - deadlock with ALTER/CREATE SERVER # connect (other,localhost,root,,); connection master; use test; delimiter //; 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; END// delimiter ;// connection other; use test; send call p1(); connection master; call p1(); connection other; reap; drop procedure p1; drop server if exists s; source federated_cleanup.inc;