1 files changed, 346 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 00000000..3d491b1d
--- /dev/null
+++ b/mysql-test/suite/federated/federated_server.test
@@ -0,0 +1,346 @@
+# WL #3031 This test tests the new servers table as well as
+# if federated can utilise the servers table
+
+# Slow test, don't run during staging part
+-- source include/not_staging.inc
+-- source include/big_test.inc
+-- source include/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 order by db;
+
+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 order by db;
+
+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 slave;
+create user test_fed@localhost identified by 'foo';
+grant all on db_legitimate.* to test_fed@localhost;
+
+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 'test_fed',
+  PASSWORD 'foo',
+  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;
+--error ER_DBACCESS_DENIED_ERROR,ER_CONNECT_TO_FOREIGN_DATA_SOURCE
+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 'test_fed',
+  PASSWORD 'foo',
+  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 'test_fed',
+  PASSWORD 'foo',
+  PORT $SLAVE_MYPORT,
+  SOCKET '',
+  OWNER 'root');
+
+connection master;
+flush tables;
+select * from federated.t1;
+
+# clean up test
+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';
+
+#
+# Bug#30671 - ALTER SERVER causes the server to crash
+#
+create server 's1' foreign data wrapper 'mysql' options (host 'foo');
+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;
+create table t1(a int);
+insert into t1 values (1);
+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;
+  select a into @a from t1; # Just reset warnings
+END//
+delimiter ;//
+connection other;
+use test;
+send call p1();
+connection master;
+call p1();
+connection other;
+reap;
+drop procedure p1;
+drop table t1;
+drop server if exists s;
+
+
+source suite/federated/include/federated_cleanup.inc;