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