~drizzle-trunk/drizzle/development : contents of tests/r/federated

~drizzle-trunk/drizzle/development : (revision 212.2.1)

stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
stop slave;
DROP DATABASE IF EXISTS federated;
CREATE DATABASE federated;
DROP DATABASE IF EXISTS 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 '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 '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 '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 '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;
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 '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 '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 '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 '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 '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 '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;
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 the SUPER privilege 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 the SUPER privilege 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 the SUPER privilege 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 the SUPER privilege 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 4427);
alter server 's1' options
(host 'localhost', database '', user '',
password '', socket '', owner '', port 4427);
alter server 's1' options
(host 'localhost', database 'database1', user '',
password '', socket '', owner '', port 4427);
drop server 's1';
# End of 5.1 tests
use test;
create procedure p1 ()
begin
DECLARE v INT DEFAULT 0;
DECLARE e INT DEFAULT 0;
DECLARE i INT;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e = e + 1;
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 e > 0;
END//
use test;
call p1();
call p1();
e > 0
1
e > 0
1
drop procedure p1;
drop server if exists s;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;
DROP TABLE IF EXISTS federated.t1;
DROP DATABASE IF EXISTS federated;

1 by brian clean slate	1	stop slave;
	2	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	3	reset master;
	4	reset slave;
	5	drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
	6	start slave;
	7	stop slave;
	8	DROP DATABASE IF EXISTS federated;
	9	CREATE DATABASE federated;
	10	DROP DATABASE IF EXISTS federated;
	11	CREATE DATABASE federated;
	12	create database first_db;
	13	create database second_db;
	14	use first_db;
	15	DROP TABLE IF EXISTS first_db.t1;
	16	Warnings:
	17	Note 1051 Unknown table 't1'
	18	CREATE TABLE first_db.t1 (
	19	`id` int(20) NOT NULL,
	20	`name` varchar(64) NOT NULL default ''
	21	)
	22	DEFAULT CHARSET=latin1;
	23	DROP TABLE IF EXISTS first_db.t2;
	24	Warnings:
	25	Note 1051 Unknown table 't2'
	26	CREATE TABLE first_db.t2 (
	27	`id` int(20) NOT NULL,
	28	`name` varchar(64) NOT NULL default ''
	29	)
	30	DEFAULT CHARSET=latin1;
	31	use second_db;
	32	DROP TABLE IF EXISTS second_db.t1;
	33	Warnings:
	34	Note 1051 Unknown table 't1'
	35	CREATE TABLE second_db.t1 (
	36	`id` int(20) NOT NULL,
	37	`name` varchar(64) NOT NULL default ''
	38	)
	39	DEFAULT CHARSET=latin1;
	40	DROP TABLE IF EXISTS second_db.t2;
	41	Warnings:
	42	Note 1051 Unknown table 't2'
	43	CREATE TABLE second_db.t2 (
	44	`id` int(20) NOT NULL,
	45	`name` varchar(64) NOT NULL default ''
	46	)
	47	DEFAULT CHARSET=latin1;
	48	drop server if exists 'server_one';
	49	create server 'server_one' foreign data wrapper 'mysql' options
	50	(HOST '127.0.0.1',
	51	DATABASE 'first_db',
	52	USER 'root',
	53	PASSWORD '',
	54	PORT SLAVE_PORT,
	55	SOCKET '',
	56	OWNER 'root');
	57	drop server if exists 'server_two';
	58	create server 'server_two' foreign data wrapper 'mysql' options
	59	(HOST '127.0.0.1',
	60	DATABASE 'second_db',
	61	USER 'root',
	62	PASSWORD '',
	63	PORT SLAVE_PORT,
	64	SOCKET '',
65	OWNER 'root');
66	select * from mysql.servers;
67	Server_name Host Db Username Password Port Socket Wrapper Owner
68	server_one 127.0.0.1 first_db root SLAVE_PORT mysql root
69	server_two 127.0.0.1 second_db root SLAVE_PORT mysql root
70	DROP TABLE IF EXISTS federated.old;
71	Warnings:
72	Note 1051 Unknown table 'old'
73	CREATE TABLE federated.old (
74	`id` int(20) NOT NULL,
75	`name` varchar(64) NOT NULL default ''
76	)
77	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
78	CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t1';
79	INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
80	SELECT * FROM federated.old;
81	id name
82	1 federated.old-> first_db.t1, url format
83	DROP TABLE IF EXISTS federated.old2;
84	Warnings:
85	Note 1051 Unknown table 'old2'
86	CREATE TABLE federated.old2 (
87	`id` int(20) NOT NULL,
88	`name` varchar(64) NOT NULL default ''
89	)
90	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
91	CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/first_db/t2';
92	INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
93	SELECT * FROM federated.old2;
94	id name
95	1 federated.old2-> first_db.t2, url format
96	DROP TABLE IF EXISTS federated.urldb2t1;
97	Warnings:
98	Note 1051 Unknown table 'urldb2t1'
99	CREATE TABLE federated.urldb2t1 (
100	`id` int(20) NOT NULL,
101	`name` varchar(64) NOT NULL default ''
102	)
103	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
104	CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t1';
105	INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format');
106	SELECT * FROM federated.urldb2t1;
107	id name
108	1 federated.urldb2t1 -> second_db.t1, url format
109	DROP TABLE IF EXISTS federated.urldb2t2;
110	Warnings:
111	Note 1051 Unknown table 'urldb2t2'
112	CREATE TABLE federated.urldb2t2 (
113	`id` int(20) NOT NULL,
114	`name` varchar(64) NOT NULL default ''
115	)
116	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
117	CONNECTION='mysql://root@127.0.0.1:SLAVE_PORT/second_db/t2';
118	INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format');
119	SELECT * FROM federated.urldb2t2;
120	id name
121	1 federated.urldb2t2 -> second_db.t2, url format
122	DROP TABLE IF EXISTS federated.t1;
123	Warnings:
124	Note 1051 Unknown table 't1'
125	CREATE TABLE federated.t1 (
126	`id` int(20) NOT NULL,
127	`name` varchar(64) NOT NULL default ''
128	)
129	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
130	CONNECTION='server_one';
131	INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
132	SELECT * FROM federated.t1;
133	id name
134	1 federated.old-> first_db.t1, url format
135	1 server_one, new scheme, first_db.t1
136	DROP TABLE IF EXISTS federated.whatever;
137	Warnings:
138	Note 1051 Unknown table 'whatever'
139	CREATE TABLE federated.whatever (
140	`id` int(20) NOT NULL,
141	`name` varchar(64) NOT NULL default ''
142	)
143	ENGINE="FEDERATED" DEFAULT CHARSET=latin1
144	CONNECTION='server_one/t1';
145	INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
146	SELECT * FROM federated.whatever;
147	id name
148	1 federated.old-> first_db.t1, url format
149	1 server_one, new scheme, first_db.t1
150	1 server_one, new scheme, whatever, first_db.t1
151	ALTER SERVER 'server_one' options(DATABASE 'second_db');
152	INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
153	SELECT * FROM federated.t1;
154	id name
155	1 federated.urldb2t1 -> second_db.t1, url format
156	1 server_two, new scheme, second_db.t1
157	INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
158	SELECT * FROM federated.whatever;
159	id name
160	1 federated.urldb2t1 -> second_db.t1, url format
161	1 server_two, new scheme, second_db.t1
162	1 server_two, new scheme, whatever, second_db.t1
163	drop table federated.t1;
164	drop server 'server_one';
165	drop server 'server_two';
166	select * from mysql.servers;
167	Server_name Host Db Username Password Port Socket Wrapper Owner
168	drop table first_db.t1;
169	drop table second_db.t1;
170	drop database first_db;
171	drop database second_db;
172	create database db_legitimate;
173	create database db_bogus;
174	use db_legitimate;
175	CREATE TABLE db_legitimate.t1 (
176	`id` int(20) NOT NULL,
177	`name` varchar(64) NOT NULL default ''
178	);
179	INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
180	use db_bogus;
181	CREATE TABLE db_bogus.t1 (
182	`id` int(20) NOT NULL,
183	`name` varchar(64) NOT NULL default ''
184	)
185	;
186	INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
187	create server 's1' foreign data wrapper 'mysql' options
188	(HOST '127.0.0.1',
189	DATABASE 'db_legitimate',
190	USER 'root',
191	PASSWORD '',
192	PORT SLAVE_PORT,
193	SOCKET '',
194	OWNER 'root');
195	create user guest_select@localhost;
196	grant select on federated.* to guest_select@localhost;
197	create user guest_super@localhost;
198	grant select,SUPER,RELOAD on . to guest_super@localhost;
199	create user guest_usage@localhost;
200	grant usage on . to guest_usage@localhost;
201	CREATE TABLE federated.t1 (
202	`id` int(20) NOT NULL,
203	`name` varchar(64) NOT NULL default ''
204	) ENGINE = FEDERATED CONNECTION = 's1';
205	select * from federated.t1;
206	id name
207	1 this is legitimate
208	alter server s1 options (database 'db_bogus');
209	ERROR 42000: Access denied; you need the SUPER privilege for this operation
210	flush tables;
211	select * from federated.t1;
212	id name
213	1 this is legitimate
214	alter server s1 options (database 'db_bogus');
215	ERROR 42000: Access denied; you need the SUPER privilege for this operation
216	flush tables;
217	select * from federated.t1;
218	id name
219	1 this is legitimate
220	alter server s1 options (database 'db_bogus');
221	flush tables;
222	select * from federated.t1;
223	id name
224	2 this is bogus
225	drop server if exists 's1';
226	ERROR 42000: Access denied; you need the SUPER privilege for this operation
227	create server 's1' foreign data wrapper 'mysql' options
228	(HOST '127.0.0.1',
229	DATABASE 'db_legitimate',
230	USER 'root',
231	PASSWORD '',
232	PORT SLAVE_PORT,
233	SOCKET '',
234	OWNER 'root');
235	ERROR 42000: Access denied; you need the SUPER privilege for this operation
236	drop server 's1';
237	create server 's1' foreign data wrapper 'mysql' options
238	(HOST '127.0.0.1',
239	DATABASE 'db_legitimate',
240	USER 'root',
241	PASSWORD '',
242	PORT SLAVE_PORT,
243	SOCKET '',
244	OWNER 'root');
245	flush tables;
246	select * from federated.t1;
247	id name
248	1 this is legitimate
249	drop database db_legitimate;
250	drop database db_bogus;
251	drop user guest_super@localhost;
252	drop user guest_usage@localhost;
253	drop user guest_select@localhost;
254	drop table federated.t1;
255	drop server 's1';
165.1.1 by Elliot Murphy new port number from IANA	256	create server 's1' foreign data wrapper 'mysql' options (port 4427);
1 by brian clean slate	257	alter server 's1' options
1 by brian clean slate	258	(host 'localhost', database '', user '',
165.1.1 by Elliot Murphy new port number from IANA	259	password '', socket '', owner '', port 4427);
1 by brian clean slate	260	alter server 's1' options
1 by brian clean slate	261	(host 'localhost', database 'database1', user '',
165.1.1 by Elliot Murphy new port number from IANA	262	password '', socket '', owner '', port 4427);
1 by brian clean slate	263	drop server 's1';
	264	# End of 5.1 tests
	265	use test;
	266	create procedure p1 ()
	267	begin
	268	DECLARE v INT DEFAULT 0;
	269	DECLARE e INT DEFAULT 0;
	270	DECLARE i INT;
	271	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e = e + 1;
	272	SET i = sleep(5);
	273	WHILE v < 10000 do
	274	CREATE SERVER s
	275	FOREIGN DATA WRAPPER mysql
	276	OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
	277	ALTER SERVER s OPTIONS (USER 'Remote');
	278	DROP SERVER s;
	279	SET v = v + 1;
	280	END WHILE;
	281	SELECT e > 0;
	282	END//
	283	use test;
	284	call p1();
	285	call p1();
	286	e > 0
	287	1
	288	e > 0
	289	1
	290	drop procedure p1;
	291	drop server if exists s;
	292	DROP TABLE IF EXISTS federated.t1;
	293	DROP DATABASE IF EXISTS federated;
	294	DROP TABLE IF EXISTS federated.t1;
	295	DROP DATABASE IF EXISTS federated;