1
# WL #3031 This test tests the new servers table as well as
2
# if federated can utilise the servers table
3
-- source federated.inc
6
create database first_db;
7
create database second_db;
11
DROP TABLE IF EXISTS first_db.t1;
12
CREATE TABLE first_db.t1 (
13
`id` int(20) NOT NULL,
14
`name` varchar(64) NOT NULL default ''
16
DEFAULT CHARSET=latin1;
18
DROP TABLE IF EXISTS first_db.t2;
19
CREATE TABLE first_db.t2 (
20
`id` int(20) NOT NULL,
21
`name` varchar(64) NOT NULL default ''
23
DEFAULT CHARSET=latin1;
26
DROP TABLE IF EXISTS second_db.t1;
27
CREATE TABLE second_db.t1 (
28
`id` int(20) NOT NULL,
29
`name` varchar(64) NOT NULL default ''
31
DEFAULT CHARSET=latin1;
33
DROP TABLE IF EXISTS second_db.t2;
34
CREATE TABLE second_db.t2 (
35
`id` int(20) NOT NULL,
36
`name` varchar(64) NOT NULL default ''
38
DEFAULT CHARSET=latin1;
42
drop server if exists 'server_one';
43
--replace_result $SLAVE_MYPORT SLAVE_PORT
44
eval create server 'server_one' foreign data wrapper 'mysql' options
53
drop server if exists 'server_two';
54
--replace_result $SLAVE_MYPORT SLAVE_PORT
55
eval create server 'server_two' foreign data wrapper 'mysql' options
64
--replace_result $SLAVE_MYPORT SLAVE_PORT
65
eval select * from mysql.servers;
67
DROP TABLE IF EXISTS federated.old;
68
--replace_result $SLAVE_MYPORT SLAVE_PORT
69
eval CREATE TABLE federated.old (
70
`id` int(20) NOT NULL,
71
`name` varchar(64) NOT NULL default ''
73
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
74
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/first_db/t1';
76
INSERT INTO federated.old (id, name) values (1, 'federated.old-> first_db.t1, url format');
78
SELECT * FROM federated.old;
80
DROP TABLE IF EXISTS federated.old2;
81
--replace_result $SLAVE_MYPORT SLAVE_PORT
82
eval CREATE TABLE federated.old2 (
83
`id` int(20) NOT NULL,
84
`name` varchar(64) NOT NULL default ''
86
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
87
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/first_db/t2';
89
INSERT INTO federated.old2 (id, name) values (1, 'federated.old2-> first_db.t2, url format');
90
SELECT * FROM federated.old2;
92
DROP TABLE IF EXISTS federated.urldb2t1;
93
--replace_result $SLAVE_MYPORT SLAVE_PORT
94
eval CREATE TABLE federated.urldb2t1 (
95
`id` int(20) NOT NULL,
96
`name` varchar(64) NOT NULL default ''
98
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
99
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/second_db/t1';
100
INSERT INTO federated.urldb2t1 (id, name) values (1, 'federated.urldb2t1 -> second_db.t1, url format');
101
SELECT * FROM federated.urldb2t1;
103
DROP TABLE IF EXISTS federated.urldb2t2;
104
--replace_result $SLAVE_MYPORT SLAVE_PORT
105
eval CREATE TABLE federated.urldb2t2 (
106
`id` int(20) NOT NULL,
107
`name` varchar(64) NOT NULL default ''
109
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
110
CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/second_db/t2';
111
INSERT INTO federated.urldb2t2 (id, name) values (1, 'federated.urldb2t2 -> second_db.t2, url format');
112
SELECT * FROM federated.urldb2t2;
114
DROP TABLE IF EXISTS federated.t1;
115
CREATE TABLE federated.t1 (
116
`id` int(20) NOT NULL,
117
`name` varchar(64) NOT NULL default ''
119
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
120
CONNECTION='server_one';
122
INSERT INTO federated.t1 (id, name) values (1, 'server_one, new scheme, first_db.t1');
124
SELECT * FROM federated.t1;
126
DROP TABLE IF EXISTS federated.whatever;
127
CREATE TABLE federated.whatever (
128
`id` int(20) NOT NULL,
129
`name` varchar(64) NOT NULL default ''
131
ENGINE="FEDERATED" DEFAULT CHARSET=latin1
132
CONNECTION='server_one/t1';
133
INSERT INTO federated.whatever (id, name) values (1, 'server_one, new scheme, whatever, first_db.t1');
134
SELECT * FROM federated.whatever;
136
ALTER SERVER 'server_one' options(DATABASE 'second_db');
138
# FLUSH TABLES is now unneccessary
140
INSERT INTO federated.t1 (id, name) values (1, 'server_two, new scheme, second_db.t1');
141
SELECT * FROM federated.t1;
143
INSERT INTO federated.whatever (id, name) values (1, 'server_two, new scheme, whatever, second_db.t1');
144
SELECT * FROM federated.whatever;
146
drop table federated.t1;
148
drop server 'server_one';
149
drop server 'server_two';
150
select * from mysql.servers;
153
drop table first_db.t1;
154
drop table second_db.t1;
155
drop database first_db;
156
drop database second_db;
159
# Bug#25671 - CREATE/DROP/ALTER SERVER should require privileges
161
# Changes to SERVER declarations should require SUPER privilege.
162
# Based upon test case by Giuseppe Maxia
164
create database db_legitimate;
165
create database db_bogus;
168
CREATE TABLE db_legitimate.t1 (
169
`id` int(20) NOT NULL,
170
`name` varchar(64) NOT NULL default ''
172
INSERT INTO db_legitimate.t1 VALUES ('1','this is legitimate');
175
CREATE TABLE db_bogus.t1 (
176
`id` int(20) NOT NULL,
177
`name` varchar(64) NOT NULL default ''
180
INSERT INTO db_bogus.t1 VALUES ('2','this is bogus');
183
--replace_result $SLAVE_MYPORT SLAVE_PORT
184
eval create server 's1' foreign data wrapper 'mysql' options
186
DATABASE 'db_legitimate',
193
create user guest_select@localhost;
194
grant select on federated.* to guest_select@localhost;
196
create user guest_super@localhost;
197
grant select,SUPER,RELOAD on *.* to guest_super@localhost;
199
create user guest_usage@localhost;
200
grant usage on *.* to guest_usage@localhost;
202
CREATE TABLE federated.t1 (
203
`id` int(20) NOT NULL,
204
`name` varchar(64) NOT NULL default ''
205
) ENGINE = FEDERATED CONNECTION = 's1';
207
select * from federated.t1;
209
connect (conn_select,127.0.0.1,guest_select,,federated,$MASTER_MYPORT);
210
connect (conn_usage,127.0.0.1,guest_usage,,,$MASTER_MYPORT);
211
connect (conn_super,127.0.0.1,guest_super,,,$MASTER_MYPORT);
213
connection conn_select;
214
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
215
alter server s1 options (database 'db_bogus');
219
select * from federated.t1;
221
connection conn_usage;
222
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
223
alter server s1 options (database 'db_bogus');
227
select * from federated.t1;
229
connection conn_super;
230
alter server s1 options (database 'db_bogus');
234
select * from federated.t1;
236
connection conn_select;
237
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
238
drop server if exists 's1';
239
--replace_result $SLAVE_MYPORT SLAVE_PORT
240
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
241
eval create server 's1' foreign data wrapper 'mysql' options
243
DATABASE 'db_legitimate',
250
connection conn_super;
252
--replace_result $SLAVE_MYPORT SLAVE_PORT
253
eval create server 's1' foreign data wrapper 'mysql' options
255
DATABASE 'db_legitimate',
264
select * from federated.t1;
268
drop database db_legitimate;
269
drop database db_bogus;
271
disconnect conn_select;
272
disconnect conn_usage;
273
disconnect conn_super;
276
drop user guest_super@localhost;
277
drop user guest_usage@localhost;
278
drop user guest_select@localhost;
279
drop table federated.t1;
283
# Bug#30671 - ALTER SERVER causes the server to crash
285
create server 's1' foreign data wrapper 'mysql' options (port 3306);
286
alter server 's1' options
287
(host 'localhost', database '', user '',
288
password '', socket '', owner '', port 3306);
289
# The next statement would crash unpatched server
290
alter server 's1' options
291
(host 'localhost', database 'database1', user '',
292
password '', socket '', owner '', port 3306);
295
--echo # End of 5.1 tests
299
# Bug#25721 - deadlock with ALTER/CREATE SERVER
301
connect (other,localhost,root,,);
305
create procedure p1 ()
307
DECLARE v INT DEFAULT 0;
309
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
313
FOREIGN DATA WRAPPER mysql
314
OPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test');
315
ALTER SERVER s OPTIONS (USER 'Remote');
329
drop server if exists s;
332
source federated_cleanup.inc;