drop database if exists spidertest;
drop database if exists spidertest_1;
drop database if exists spidertest_2;
create database spidertest default charset utf8;
create database spidertest_1 default charset utf8;
create database spidertest_2 default charset utf8;

use spidertest_1;
drop table if exists `spidertest`;
CREATE TABLE `spidertest` (
	`pkey` VARCHAR(250) NOT NULL DEFAULT '',
	`stat` VARCHAR(250) NULL DEFAULT NULL,
	`testtext` VARCHAR(250) NULL DEFAULT NULL,
	PRIMARY KEY (`pkey`),
	INDEX `IDX_STAT` (`stat`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

use spidertest_2;
drop table if exists `spidertest`;
CREATE TABLE `spidertest` (
	`pkey` VARCHAR(250) NOT NULL DEFAULT '',
	`stat` VARCHAR(250) NULL DEFAULT NULL,
	`testtext` VARCHAR(250) NULL DEFAULT NULL,
	PRIMARY KEY (`pkey`),
	INDEX `IDX_STAT` (`stat`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

grant delete,insert,select,update,index,lock tables on `spidertest_1`.* to 'user'@'%' identified by 'pass';
grant delete,insert,select,update,index,lock tables on `spidertest_2`.* to 'user'@'%' identified by 'pass';
DROP SERVER if exists spidertest_1;
DROP SERVER if exists spidertest_2;
CREATE SERVER spidertest_1 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'user', HOST '127.0.0.1', PASSWORD 'pass', PORT 3306, DATABASE 'spidertest_1');
CREATE SERVER spidertest_2 FOREIGN DATA WRAPPER mysql OPTIONS (USER 'user', HOST '127.0.0.1', PASSWORD 'pass', PORT 3306, DATABASE 'spidertest_2');

use spidertest;
drop table if exists `spidertest`;
CREATE TABLE `spidertest` (
	`pkey` VARCHAR(250) NOT NULL DEFAULT '',
	`stat` VARCHAR(250) NULL DEFAULT NULL,
	`testtext` VARCHAR(250) NULL DEFAULT NULL,
	PRIMARY KEY (`pkey`),
	INDEX `IDX_STAT` (`stat`)
)
ENGINE=SPIDER DEFAULT CHARSET=utf8
PARTITION BY KEY() (
        PARTITION p1 COMMENT ='table "spidertest", server "spidertest_1"',
        PARTITION p2 COMMENT ='table "spidertest", server "spidertest_2"');
        
grant delete,insert,select,update,index,lock tables on `spidertest`.* to 'user'@'%' identified by 'pass';

insert into `spidertest` values ( '1',repeat('a',250) , repeat('test',50) );
insert into `spidertest` values ( '2',repeat('b',250) , repeat('fizz',50) );
insert into `spidertest` values ( '3',repeat('c',250) , repeat('buzz',50) );

select * from `spidertest` where `stat` like 'a%' order by `testtext` limit 2;