federated.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

# should work with embedded server after mysqltest is fixed
-- source include/not_embedded.inc
source include/federated.inc;

connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  DEFAULT CHARSET=latin1;

connection master;
DROP TABLE IF EXISTS federated.t1;
# test too many items (malformed) in the comment string url
--error 1432
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:@/too/many/items/federated/t1';

# test not enough items (malformed) in the comment string url
--error 1432 
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1';

# test non-existant table
--replace_result $SLAVE_MYPORT SLAVE_PORT
--error 1434
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t3';

# test bad user/password 
--replace_result $SLAVE_MYPORT SLAVE_PORT
--error 1429
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://user:pass@127.0.0.1:$SLAVE_MYPORT/federated/t1';

DROP TABLE IF EXISTS federated.t1;
# # correct connection, same named tables
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (id, name) VALUES (1, 'foo');
INSERT INTO federated.t1 (id, name) VALUES (2, 'fee');

SELECT * FROM federated.t1;
DELETE FROM federated.t1;
DROP TABLE federated.t1;

# correct connection, differently named tables
DROP TABLE IF EXISTS federated.t2;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t2 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

--replace_result $SLAVE_MYPORT SLAVE_PORT
eval SHOW CREATE TABLE federated.t2;

INSERT INTO federated.t2 (id, name) VALUES (1, 'foo');
INSERT INTO federated.t2 (id, name) VALUES (2, 'fee');

SELECT * FROM federated.t2;
DROP TABLE federated.t2;

connection slave;
DROP TABLE IF EXISTS federated.t1;

DROP TABLE IF EXISTS federated.`t1%`;
CREATE TABLE federated.`t1%` (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  DEFAULT CHARSET=latin1;

connection master; 
DROP TABLE IF EXISTS federated.t1;

--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1%';

INSERT INTO federated.t1 (id, name) VALUES (1, 'foo');
INSERT INTO federated.t1 (id, name) VALUES (2, 'fee');

SELECT * FROM federated.t1;
DELETE FROM federated.t1;
DROP TABLE IF EXISTS federated.t1;

--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.`t1%` (
    `id` int(20) NOT NULL,
    `name` varchar(32) NOT NULL default ''
    )
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1%';

INSERT INTO federated.`t1%` (id, name) VALUES (1, 'foo');
INSERT INTO federated.`t1%` (id, name) VALUES (2, 'fee');

SELECT * FROM federated.`t1%`;
DELETE FROM federated.`t1%`;
DROP TABLE IF EXISTS federated.`t1%`;

connection slave;
DROP TABLE IF EXISTS federated.`t1%`;

# I wanted to use timestamp, but results will fail if so!!!
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL default '',
    `other` int(20) NOT NULL default '0',
    `created` datetime default '2004-04-04 04:04:04',
    PRIMARY KEY  (`id`))
  DEFAULT CHARSET=latin1;

connection master;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL default '',
    `other` int(20) NOT NULL default '0',
    `created` datetime default '2004-04-04 04:04:04',
    PRIMARY KEY  (`id`))
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, other) VALUES ('First Name', 11111);
INSERT INTO federated.t1 (name, other) VALUES ('Second Name', 22222);
INSERT INTO federated.t1 (name, other) VALUES ('Third Name', 33333);
INSERT INTO federated.t1 (name, other) VALUES ('Fourth Name', 44444);
INSERT INTO federated.t1 (name, other) VALUES ('Fifth Name', 55555);
INSERT INTO federated.t1 (name, other) VALUES ('Sixth Name', 66666);
INSERT INTO federated.t1 (name, other) VALUES ('Seventh Name', 77777);
INSERT INTO federated.t1 (name, other) VALUES ('Eigth Name', 88888);
INSERT INTO federated.t1 (name, other) VALUES ('Ninth Name', 99999);
INSERT INTO federated.t1 (name, other) VALUES ('Tenth Name', 101010);

# basic select
SELECT * FROM federated.t1;
# with PRIMARY KEY index_read_idx
SELECT * FROM federated.t1 WHERE id = 5;
SELECT * FROM federated.t1 WHERE name = 'Sixth Name';
SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name';
SELECT * FROM federated.t1 WHERE name = 'Sixth Name' AND other = 44444;
SELECT * FROM federated.t1 WHERE name like '%th%';
UPDATE federated.t1 SET name = '3rd name' WHERE id = 3;
SELECT * FROM federated.t1 WHERE name = '3rd name';
UPDATE federated.t1 SET name = 'Third name' WHERE name = '3rd name';
SELECT * FROM federated.t1 WHERE name = 'Third name';
# rnd_post, ::position
SELECT * FROM federated.t1 ORDER BY id DESC;
SELECT * FROM federated.t1 ORDER BY name;
SELECT * FROM federated.t1 ORDER BY name DESC;
SELECT * FROM federated.t1 ORDER BY name ASC;
SELECT * FROM federated.t1 GROUP BY other;

# ::delete_row
DELETE FROM federated.t1 WHERE id = 5; 
SELECT * FROM federated.t1 WHERE id = 5;

# ::delete_all_rows
DELETE FROM federated.t1;
SELECT * FROM federated.t1 WHERE id = 5;

# previous test, but this time with indexes
connection slave;
DROP TABLE IF EXISTS federated.t1;
CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL default '',
    `other` int(20) NOT NULL default '0',
    `created` datetime NOT NULL,
    PRIMARY KEY  (`id`),
    key name(`name`),
    key other(`other`),
    key created(`created`))
  DEFAULT CHARSET=latin1;

connection master;
DROP TABLE IF EXISTS federated.t1;
--replace_result $SLAVE_MYPORT SLAVE_PORT
eval CREATE TABLE federated.t1 (
    `id` int(20) NOT NULL auto_increment,
    `name` varchar(32) NOT NULL default '',
    `other` int(20) NOT NULL default '0',
    `created` datetime NOT NULL,
    PRIMARY KEY  (`id`),
    key name(`name`),
    key other(`other`),
    key created(`created`))
  ENGINE="FEDERATED" DEFAULT CHARSET=latin1
  CONNECTION='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1';

INSERT INTO federated.t1 (name, other, created)
  VALUES ('First Name', 11111, '2004-01-01 01:01:01');
INSERT INTO federated.t1 (name, other, created)
  VALUES ('Second Name', 22222, '2004-01-23 02:43:00');
INSERT INTO federated.t1 (name, other, created)
  VALUES ('Third Name', 33333, '2004-02-14 02:14:00');
INSERT INTO federated.t1 (name, other, created)
  VALUES ('Fourth Name', 44444, '2003-04-05 00:00:00');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Fifth Name', 55555, '2001-02-02 02:02:02');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Sixth Name', 66666, '2005-06-06 15:30:00');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Seventh Name', 77777, '2003-12-12 18:32:00');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Eigth Name', 88888, '2005-03-12 11:00:00');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Ninth Name', 99999, '2005-03-12 11:00:01');
INSERT INTO federated.t1 (name, other, created) 
  VALUES ('Tenth Name', 101010, '2005-03-12 12:00:01');

# basic select
SELECT * FROM federated.t1;
# with PRIMARY KEY index_read_idx
SELECT * FROM federated.t1 WHERE id = 5;
# with regular key index_read -> index_read_idx
# regular and PRIMARY KEY index_read_idx
SELECT * FROM federated.t1 WHERE id = 6 and name = 'Sixth Name';
# with regular key index_read -> index_read_idx
SELECT * FROM federated.t1 WHERE other = 44444;
SELECT * FROM federated.t1 WHERE name like '%th%';
# update - update_row, index_read_idx
UPDATE federated.t1 SET name = '3rd name' WHERE id = 3;
SELECT * FROM federated.t1 WHERE name = '3rd name';
# update - update_row, index_read -> index_read_idx
UPDATE federated.t1 SET name = 'Third name' WHERE name = '3rd name';
SELECT * FROM federated.t1 WHERE name = 'Third name';
# rnd_post, ::position
SELECT * FROM federated.t1 ORDER BY id DESC;
SELECT * FROM federated.t1 ORDER BY name;