EVOLUTION-MANAGER

Edit File: spider_fixes.result

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 create table and insert 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'); DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( PRIMARY KEY(a) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 INSERT INTO ta_l SELECT a, b, c FROM tb_l; 2.13 select table with "order by desc" and "<" SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l WHERE a < 5 ORDER BY a DESC LIMIT 3; a b date_format(c, '%Y-%m-%d %H:%i:%s') 4 d 2003-11-30 05:01:03 3 e 2007-06-04 20:03:11 2 b 2000-01-01 00:00:00 select table with "order by desc" and "<=" SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l WHERE a <= 5 ORDER BY a DESC LIMIT 3; a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-12-31 23:59:59 4 d 2003-11-30 05:01:03 3 e 2007-06-04 20:03:11 2.14 update table with range scan and split_read UPDATE ta_l SET c = '2000-02-02 00:00:00' WHERE a > 1; SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 a 2008-08-01 10:21:39 2 b 2000-02-02 00:00:00 3 e 2000-02-02 00:00:00 4 d 2000-02-02 00:00:00 5 c 2000-02-02 00:00:00 2.15 select table with range scan TRUNCATE TABLE ta_l; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a int(11) NOT NULL DEFAULT '0', b char(1) DEFAULT NULL, c datetime DEFAULT NULL, PRIMARY KEY (a, b, c) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT5_2_1 INSERT INTO ta_l SELECT a, b, c FROM tb_l; SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b >= 'b' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b > 'b' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a >= 4 AND b = 'd' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a > 4 AND b = 'c' AND c = '2001-12-31 23:59:59'; a b c 5 c 2001-12-31 23:59:59 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b <= 'd' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b < 'e' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a <= 4 AND b = 'b' AND c = '2000-01-01 00:00:00'; a b c 2 b 2000-01-01 00:00:00 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a < 4 AND b = 'b' AND c = '2000-01-01 00:00:00'; a b c 2 b 2000-01-01 00:00:00 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b >= 'b' AND b <= 'd' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a = 4 AND b > 'b' AND b < 'e' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a <= 4 AND a >= 1 AND b >= 'b' AND c = '2003-11-30 05:01:03'; a b c 4 d 2003-11-30 05:01:03 SELECT a, b, c FROM ta_l FORCE INDEX(PRIMARY) WHERE a < 4 AND a > 1 AND b >= 'b' AND c = '2000-01-01 00:00:00'; a b c 2 b 2000-01-01 00:00:00 2.16 auto_increment insert with trigger CREATE TABLE ta_l_auto_inc ( a INT AUTO_INCREMENT, b CHAR(1) DEFAULT 'c', c DATETIME DEFAULT '1999-10-10 10:10:10', PRIMARY KEY(a) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT3_2_1 CREATE TABLE tc_l ( a INT, b CHAR(1), c DATETIME, PRIMARY KEY(a) ) MASTER_1_ENGINE2 MASTER_1_CHARSET2 CREATE TRIGGER ins_ta_l_auto_inc AFTER INSERT ON ta_l_auto_inc FOR EACH ROW BEGIN INSERT INTO tc_l (a, b, c) VALUES (NEW.a, NEW.b, NEW.c); END;; INSERT INTO ta_l_auto_inc (a, b, c) VALUES (NULL, 's', '2008-12-31 20:59:59'); SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM tc_l ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 s 2008-12-31 20:59:59 2.17 engine-condition-pushdown with "or" and joining SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l WHERE a = 1 OR a IN (SELECT a FROM tb_l); a b date_format(c, '%Y-%m-%d %H:%i:%s') 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.23 index merge 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_COMMENT4_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.24 index scan update without PK DROP TABLE IF EXISTS ta_l_int; CREATE TABLE ta_l_int ( a INT NOT NULL, b INT DEFAULT 10, c INT DEFAULT 11, KEY idx1(b), KEY idx2(c) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1 SELECT a, b, c FROM ta_l_int ORDER BY a; a b c 1 2 3 2 3 4 3 4 5 4 5 6 5 6 7 6 7 8 7 8 9 8 9 10 9 10 11 10 11 12 11 12 13 12 13 14 13 14 15 14 15 16 15 16 17 16 17 18 INSERT INTO ta_l_int (a, b, c) VALUES (0, 2, 3); INSERT INTO ta_l_int (a, b, c) VALUES (18, 2, 3); UPDATE ta_l_int SET c = 4 WHERE b = 2; SELECT a, b, c FROM ta_l_int ORDER BY a; a b c 0 2 4 1 2 4 2 3 4 3 4 5 4 5 6 5 6 7 6 7 8 7 8 9 8 9 10 9 10 11 10 11 12 11 12 13 12 13 14 13 14 15 14 15 16 15 16 17 16 17 18 18 2 4 2.25 direct order limit SHOW GLOBAL STATUS LIKE 'Spider_direct_order_limit%'; Variable_name Value Spider_direct_order_limit 0 SELECT a, b, c FROM ta_l_int ORDER BY a LIMIT 3; a b c 1 2 4 2 3 4 3 4 5 SHOW GLOBAL STATUS LIKE 'Spider_direct_order_limit%'; Variable_name Value Spider_direct_order_limit 0 2.26 lock tables DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( id int(11) NOT NULL, PRIMARY KEY (id) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_LOCK1 CREATE TABLE t2 ( id int(11) NOT NULL, PRIMARY KEY (id) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_LOCK2 LOCK TABLES t1 READ, t2 READ; UNLOCK TABLES; auto_increment 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_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 read only DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, PRIMARY KEY (id) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_READONLY1_1 SELECT id FROM t1 ORDER BY id; id 777 1000 1554 2331 3108 3885 4662 5000 5439 6216 10000 INSERT INTO t1 (id) VALUES (1); ERROR HY000: Table 'auto_test_local.t1' is read only UPDATE t1 SET id = 4 WHERE id = 777; ERROR HY000: Table 'auto_test_local.t1' is read only DELETE FROM t1 WHERE id = 777; ERROR HY000: Table 'auto_test_local.t1' is read only DELETE FROM t1; ERROR HY000: Table 'auto_test_local.t1' is read only TRUNCATE t1; ERROR HY000: Table 'auto_test_local.t1' is read only 2.27 error mode DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, PRIMARY KEY (id) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_ERROR_MODE1_1 SELECT id FROM t1 ORDER BY id; id Warnings: Error 12712 Error from Oracle -1 942 ORA-00942: ??????????????? Error 12712 Oracle error Error 12712 Error from Oracle -1 942 ORA-00942: ??????????????? Error 12712 Oracle error INSERT INTO t1 (id) VALUES (1); Warnings: Error 12712 Error from Oracle -1 942 ORA-00942: ??????????????? Error 12712 Oracle error DELETE FROM t1; Warnings: Error 12712 Error from Oracle -1 942 ORA-00942: ??????????????? Error 12712 Oracle error TRUNCATE t1; Warnings: Error 12712 Error from Oracle -1 942 ORA-00942: ??????????????? Error 12712 Oracle error 3.0 is null DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a VARCHAR(255), b VARCHAR(255), c VARCHAR(255), KEY idx1(a,b), KEY idx2(b), PRIMARY KEY(c) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_TEXT_KEY1_1 insert into t1 values (null, null, '2048'); insert into t1 values ('1', '1', '1'); insert into t1 select a + 1, b + 1, c + 1 from t1; insert into t1 select a + 2, b + 2, c + 2 from t1; insert into t1 select a + 4, b + 4, c + 4 from t1; insert into t1 select a + 8, b + 8, c + 8 from t1; insert into t1 select a + 16, b + 16, c + 16 from t1; insert into t1 select a + 32, b + 32, c + 32 from t1; insert into t1 select a + 64, b + 64, c + 64 from t1; insert into t1 select a + 128, b + 128, c + 128 from t1; insert into t1 select a + 256, b + 256, c + 256 from t1; insert into t1 select a + 512, b + 512, c + 512 from t1; flush tables; select a from t1 where a is null order by a limit 30; a NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL select b from t1 where b is null order by b limit 30; b NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL direct_order_limit TRUNCATE TABLE t1; insert into t1 values ('1', '1', '1'); insert into t1 select a + 1, b + 1, c + 1 from t1; insert into t1 select a + 2, b + 2, c + 2 from t1; insert into t1 select a + 4, b + 4, c + 4 from t1; insert into t1 select a + 8, b + 8, c + 8 from t1; insert into t1 select a + 16, b + 16, c + 16 from t1; insert into t1 select a, b + 32, c + 32 from t1; insert into t1 select a, b + 64, c + 64 from t1; insert into t1 select a, b + 128, c + 128 from t1; flush tables; select a, b, c from t1 where a = '10' and b <> '100' order by c desc limit 5; a b c 10 74 74 10 42 42 10 234 234 10 202 202 10 170 170 select a, c from t1 where a = '10' order by b desc limit 5; a c 10 74 10 42 10 234 10 202 10 170 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