mirror of
https://github.com/MariaDB/server.git
synced 2025-02-22 21:33:08 +01:00
d2eba35653
The existing syntax for CREATE SERVER
CREATE [OR REPLACE] SERVER [IF NOT EXISTS] server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option] ...)
option:
{ HOST character-literal
| DATABASE character-literal
| USER character-literal
| PASSWORD character-literal
| SOCKET character-literal
| OWNER character-literal
| PORT numeric-literal }
With this change we have:
option:
{ HOST character-literal
| DATABASE character-literal
| USER character-literal
| PASSWORD character-literal
| SOCKET character-literal
| OWNER character-literal
| PORT numeric-literal
| PORT quoted-numerical-literal
| identifier character-literal}
We store these options as a JSON field in the mysql.servers system
table. We retain the restriction that PORT needs to be a number, but
also allow it to be a quoted number, so that SHOW CREATE SERVER can be
used for dumping. Without an accompanied implementation of SHOW CREATE
SERVER, some mysqldump tests will fail. Therefore this commit should
be immediately followed by the one implementating SHOW CREATE SERVER,
with testing covering both.
319 lines
9.9 KiB
Text
319 lines
9.9 KiB
Text
connect master,127.0.0.1,root,,test,$MASTER_MYPORT,;
|
|
connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,;
|
|
connection master;
|
|
CREATE DATABASE federated;
|
|
connection slave;
|
|
CREATE DATABASE federated;
|
|
connection slave;
|
|
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;
|
|
connection master;
|
|
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 db;
|
|
Server_name Host Db Username Password Port Socket Wrapper Owner Options
|
|
server_one 127.0.0.1 first_db root SLAVE_PORT mysql root {"HOST": "127.0.0.1", "DATABASE": "first_db", "USER": "root", "PASSWORD": "", "PORT": "SLAVE_PORT", "SOCKET": "", "OWNER": "root"}
|
|
server_two 127.0.0.1 second_db root SLAVE_PORT mysql root {"HOST": "127.0.0.1", "DATABASE": "second_db", "USER": "root", "PASSWORD": "", "PORT": "SLAVE_PORT", "SOCKET": "", "OWNER": "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 db;
|
|
Server_name Host Db Username Password Port Socket Wrapper Owner Options
|
|
connection slave;
|
|
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');
|
|
connection slave;
|
|
create user test_fed@localhost identified by 'foo';
|
|
grant all on db_legitimate.* to test_fed@localhost;
|
|
connection master;
|
|
create server 's1' foreign data wrapper 'mysql' options
|
|
(HOST '127.0.0.1',
|
|
DATABASE 'db_legitimate',
|
|
USER 'test_fed',
|
|
PASSWORD 'foo',
|
|
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,FEDERATED ADMIN,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
|
|
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;
|
|
alter server s1 options (database 'db_bogus');
|
|
ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
|
|
connection master;
|
|
flush tables;
|
|
select * from federated.t1;
|
|
id name
|
|
1 this is legitimate
|
|
connection conn_usage;
|
|
alter server s1 options (database 'db_bogus');
|
|
ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
|
|
connection master;
|
|
flush tables;
|
|
select * from federated.t1;
|
|
id name
|
|
1 this is legitimate
|
|
connection conn_super;
|
|
alter server s1 options (database 'db_bogus');
|
|
connection master;
|
|
flush tables;
|
|
select * from federated.t1;
|
|
Got one of the listed errors
|
|
connection conn_select;
|
|
drop server if exists 's1';
|
|
ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
|
|
create server 's1' foreign data wrapper 'mysql' options
|
|
(HOST '127.0.0.1',
|
|
DATABASE 'db_legitimate',
|
|
USER 'test_fed',
|
|
PASSWORD 'foo',
|
|
PORT SLAVE_PORT,
|
|
SOCKET '',
|
|
OWNER 'root');
|
|
ERROR 42000: Access denied; you need (at least one of) the FEDERATED ADMIN privilege(s) for this operation
|
|
connection conn_super;
|
|
drop server 's1';
|
|
create server 's1' foreign data wrapper 'mysql' options
|
|
(HOST '127.0.0.1',
|
|
DATABASE 'db_legitimate',
|
|
USER 'test_fed',
|
|
PASSWORD 'foo',
|
|
PORT SLAVE_PORT,
|
|
SOCKET '',
|
|
OWNER 'root');
|
|
connection master;
|
|
flush tables;
|
|
select * from federated.t1;
|
|
id name
|
|
1 this is legitimate
|
|
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';
|
|
create server 's1' foreign data wrapper 'mysql' options (host 'foo');
|
|
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
|
|
connect other,localhost,root,,;
|
|
connection master;
|
|
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//
|
|
connection other;
|
|
use test;
|
|
call p1();
|
|
connection master;
|
|
call p1();
|
|
connection other;
|
|
drop procedure p1;
|
|
drop table t1;
|
|
drop server if exists s;
|
|
connection master;
|
|
DROP TABLE IF EXISTS federated.t1;
|
|
DROP DATABASE IF EXISTS federated;
|
|
connection slave;
|
|
DROP TABLE IF EXISTS federated.t1;
|
|
DROP DATABASE IF EXISTS federated;
|