for master_1
for child2
child2_1
child2_2
child2_3
for child3
child3_1
child3_2
child3_3
for slave1_1

drop and create databases
DROP DATABASE IF EXISTS auto_test_local;
CREATE DATABASE auto_test_local;
USE auto_test_local;
DROP DATABASE IF EXISTS auto_test_remote;
CREATE DATABASE auto_test_remote;
USE auto_test_remote;
DROP DATABASE IF EXISTS auto_test_remote2;
CREATE DATABASE auto_test_remote2;
USE auto_test_remote2;

test select 1
SELECT 1;
1
1
DROP TABLE IF EXISTS tb_l;
CREATE TABLE tb_l (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE2 MASTER_1_CHARSET2
INSERT INTO tb_l (a, b, c) VALUES
(1, 'a', '2008-08-01 10:21:39'),
(2, 'b', '2000-01-01 00:00:00'),
(3, 'e', '2007-06-04 20:03:11'),
(4, 'd', '2003-11-30 05:01:03'),
(5, 'c', '2001-12-31 23:59:59');

2.17
partition with sort
CREATE TABLE ta_l2 (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1
INSERT INTO ta_l2 SELECT a, b, c FROM tb_l;
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 WHERE a > 1
ORDER BY a;
a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
2	b	2000-01-01 00:00:00
3	e	2007-06-04 20:03:11
4	d	2003-11-30 05:01:03
5	c	2001-12-31 23:59:59

2.23
partition update with moving partition
DROP TABLE IF EXISTS ta_l2;
CREATE TABLE ta_l2 (
a INT,
b CHAR(1),
c DATETIME,
PRIMARY KEY(a)
) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1
INSERT INTO ta_l2 (a, b, c) VALUES (3, 'B', '2010-09-26 00:00:00');
UPDATE ta_l2 SET a = 4 WHERE a = 3;
SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2;
a	b	date_format(c, '%Y-%m-%d %H:%i:%s')
4	B	2010-09-26 00:00:00
index merge with partition
DROP TABLE IF EXISTS ta_l_int;
CREATE TABLE ta_l_int (
a INT AUTO_INCREMENT,
b INT DEFAULT 10,
c INT DEFAULT 11,
PRIMARY KEY(a),
KEY idx1(b),
KEY idx2(c)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT3_P_2_1
INSERT INTO ta_l_int (a, b, c) VALUES (1, 2, 3);
INSERT INTO ta_l_int (a, b, c) SELECT a + 1, b + 1, c + 1 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 2, b + 2, c + 2 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 4, b + 4, c + 4 FROM ta_l_int;
INSERT INTO ta_l_int (a, b, c) SELECT a + 8, b + 8, c + 8 FROM ta_l_int;
SELECT a, b, c FROM ta_l_int force index(primary, idx1, idx2)
WHERE a = 5 OR b = 5 OR c = 5 ORDER BY a;
a	b	c
3	4	5
4	5	6
5	6	7

2.26
auto_increment with partition
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_INCREMENT1_P_1
MASTER_1_AUTO_INCREMENT_INCREMENT2
MASTER_1_AUTO_INCREMENT_OFFSET2
spider_direct_sql('begin execute immediate \'DROP SEQUENCE "seq_t1_1"\'; exception when others then null; end;', '', 'srv "s_2_1"')
1
spider_direct_sql('CREATE SEQUENCE "seq_t1_1" START WITH 2 INCREMENT BY 4', '', 'srv "s_2_1"')
1
spider_direct_sql('begin execute immediate \'DROP SEQUENCE "seq_t1_2"\'; exception when others then null; end;', '', 'srv "s_2_2"')
1
spider_direct_sql('CREATE SEQUENCE "seq_t1_2" START WITH 3 INCREMENT BY 4', '', 'srv "s_2_2"')
1
spider_direct_sql('begin execute immediate \'DROP SEQUENCE "seq_t1_1"\'; exception when others then null; end;', '', 'srv "s_2_1"')
1
spider_direct_sql('CREATE SEQUENCE "seq_t1_1" START WITH 2 INCREMENT BY 4', '', 'srv "s_2_1"')
1
spider_direct_sql('begin execute immediate \'DROP SEQUENCE "seq_t1_2"\'; exception when others then null; end;', '', 'srv "s_2_2"')
1
spider_direct_sql('CREATE SEQUENCE "seq_t1_2" START WITH 3 INCREMENT BY 4', '', 'srv "s_2_2"')
1
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
777
SELECT MAX(id) FROM t1;
MAX(id)
777
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
1554
SELECT MAX(id) FROM t1;
MAX(id)
1554
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
2331
SELECT MAX(id) FROM t1;
MAX(id)
2331
INSERT INTO t1 (id) VALUES (null);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3108
SELECT MAX(id) FROM t1;
MAX(id)
3108
INSERT INTO t1 () VALUES (),(),(),();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
3885
SELECT id FROM t1 ORDER BY id;
id
777
1554
2331
3108
3885
4662
5439
6216
SET INSERT_ID=5000;
INSERT INTO t1 () VALUES ();
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
6216
INSERT INTO t1 (id) VALUES (10000);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
10000
INSERT INTO t1 (id) VALUES (1000);
SELECT LAST_INSERT_ID();
LAST_INSERT_ID()
5000
SELECT MAX(id) FROM t1;
MAX(id)
10000

deinit
DROP DATABASE IF EXISTS auto_test_local;
DROP DATABASE IF EXISTS auto_test_remote;
DROP DATABASE IF EXISTS auto_test_remote2;
for slave1_1
for master_1
for child2
child2_1
child2_2
child2_3
for child3
child3_1
child3_2
child3_3

end of test
