federated_server.result 8.57 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284
CREATE DATABASE federated;
CREATE DATABASE federated;
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;
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 Server_name;
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 Server_name;
Server_name	Host	Db	Username	Password	Port	Socket	Wrapper	Owner
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');
create server 's1' foreign data wrapper 'mysql' options
(HOST '127.0.0.1',
DATABASE 'db_legitimate',
USER 'root',
PASSWORD '',
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
alter server s1 options (database 'db_bogus');
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
flush tables;
select * from federated.t1;
id	name
1	this is legitimate
alter server s1 options (database 'db_bogus');
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
flush tables;
select * from federated.t1;
id	name
1	this is legitimate
alter server s1 options (database 'db_bogus');
flush tables;
select * from federated.t1;
id	name
2	this is bogus
drop server if exists 's1';
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
create server 's1' foreign data wrapper 'mysql' options
(HOST '127.0.0.1',
DATABASE 'db_legitimate',
USER 'root',
PASSWORD '',
PORT SLAVE_PORT,
SOCKET '',
OWNER 'root');
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
drop server 's1';
create server 's1' foreign data wrapper 'mysql' options
(HOST '127.0.0.1',
DATABASE 'db_legitimate',
USER 'root',
PASSWORD '',
PORT SLAVE_PORT,
SOCKET '',
OWNER 'root');
flush tables;
select * from federated.t1;
id	name
1	this is legitimate
drop database db_legitimate;
drop database db_bogus;
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 (port 3306);
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
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//
use test;
call p1();
call p1();
drop procedure p1;
drop table t1;
drop server if exists s;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE federated;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE federated;