diff options
17 files changed, 1419 insertions, 743 deletions
diff --git a/storage/spider/mysql-test/spider/bg/suite.pm b/storage/spider/mysql-test/spider/bg/suite.pm index 1bb6d7592c8..f106147deb6 100644 --- a/storage/spider/mysql-test/spider/bg/suite.pm +++ b/storage/spider/mysql-test/spider/bg/suite.pm @@ -6,5 +6,7 @@ return "No Spider engine" unless $ENV{HA_SPIDER_SO}; return "Not run for embedded server" if $::opt_embedded_server; return "Test needs --big-test" unless $::opt_big_test; +sub is_default { 1 } + bless { }; diff --git a/storage/spider/mysql-test/spider/handler/r/basic_sql.result b/storage/spider/mysql-test/spider/handler/r/basic_sql.result index da24c08e9fd..94a09fc317b 100644 --- a/storage/spider/mysql-test/spider/handler/r/basic_sql.result +++ b/storage/spider/mysql-test/spider/handler/r/basic_sql.result @@ -9,22 +9,27 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 create table select test +connection master_1; DROP TABLE IF EXISTS tb_l; CREATE TABLE tb_l ( a INT, @@ -43,6 +48,7 @@ CREATE TABLE ta_l ( PRIMARY KEY(a) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 SELECT a, b, c FROM tb_l +connection master_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 @@ -52,6 +58,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-12-31 23:59:59 create table ignore select test +connection master_1; DROP TABLE IF EXISTS ta_l; DROP TABLE IF EXISTS tb_l; CREATE TABLE tb_l ( @@ -70,6 +77,13 @@ CREATE TABLE ta_l ( PRIMARY KEY(a) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 IGNORE SELECT a, b, c FROM tb_l +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +Warning 1062 Duplicate entry '4' for key 'PRIMARY' +Warning 1062 Duplicate entry '5' for key 'PRIMARY' +connection master_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 @@ -79,11 +93,13 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-12-31 23:59:59 create table ignore select test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( PRIMARY KEY(a) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT_2_1 REPLACE SELECT a, b, c FROM tb_l +connection master_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 f 2008-07-01 10:21:39 @@ -92,24 +108,13 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 4 i 2003-10-30 05:01:03 5 h 2001-10-31 23:59:59 -create table with partition and select test -CREATE TABLE ta_l2 ( -PRIMARY KEY(a) -) MASTER_1_ENGINE MASTER_1_COMMENT_P_2_1 -SELECT a, b, c FROM tb_l -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 f 2008-07-01 10:21:39 -2 g 2000-02-01 00:00:00 -3 j 2007-05-04 20:03:11 -4 i 2003-10-30 05:01:03 -5 h 2001-10-31 23:59:59 - create no index table +connection master_1; DROP TABLE IF EXISTS ta_l_no_idx; CREATE TABLE ta_l_no_idx MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT2_2_1 SELECT a, b, c FROM tb_l +connection master_1; SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l_no_idx ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 f 2008-07-01 10:21:39 @@ -119,6 +124,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select table +connection master_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 f 2008-07-01 10:21:39 @@ -128,6 +134,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select table shared mode +connection master_1; SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a LOCK IN SHARE MODE; a b date_format(c, '%Y-%m-%d %H:%i:%s') @@ -138,6 +145,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select table for update +connection master_1; SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a FOR UPDATE; a b date_format(c, '%Y-%m-%d %H:%i:%s') @@ -148,6 +156,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select table join +connection master_1; SELECT a.a, a.b, date_format(b.c, '%Y-%m-%d %H:%i:%s') FROM ta_l a, tb_l b WHERE a.a = b.a ORDER BY a.a; a b date_format(b.c, '%Y-%m-%d %H:%i:%s') @@ -158,6 +167,7 @@ a b date_format(b.c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select table straight_join +connection master_1; SELECT STRAIGHT_JOIN a.a, a.b, date_format(b.c, '%Y-%m-%d %H:%i:%s') FROM ta_l a, tb_l b WHERE a.a = b.a ORDER BY a.a; a b date_format(b.c, '%Y-%m-%d %H:%i:%s') @@ -168,6 +178,7 @@ a b date_format(b.c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_small_result +connection master_1; SELECT SQL_SMALL_RESULT 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') @@ -178,6 +189,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_big_result +connection master_1; SELECT SQL_BIG_RESULT 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') @@ -188,6 +200,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_buffer_result +connection master_1; SELECT SQL_BUFFER_RESULT 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') @@ -198,6 +211,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_cache +connection master_1; SELECT SQL_CACHE 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') @@ -208,6 +222,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_no_cache +connection master_1; SELECT SQL_NO_CACHE 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') @@ -218,6 +233,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select sql_calc_found_rows +connection master_1; SELECT SQL_CALC_FOUND_ROWS a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a LIMIT 4; a b date_format(c, '%Y-%m-%d %H:%i:%s') @@ -225,11 +241,13 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 2 g 2000-02-01 00:00:00 3 j 2007-05-04 20:03:11 4 i 2003-10-30 05:01:03 +connection master_1; SELECT found_rows(); found_rows() 5 select high_priority +connection master_1; SELECT HIGH_PRIORITY 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') @@ -240,6 +258,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select distinct +connection master_1; SELECT DISTINCT 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') @@ -250,11 +269,13 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select count +connection master_1; SELECT count(*) FROM ta_l ORDER BY a; count(*) 5 select table join not use index +connection master_1; SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM tb_l a WHERE EXISTS (SELECT * FROM ta_l b WHERE b.b = a.b) ORDER BY a.a; a b date_format(a.c, '%Y-%m-%d %H:%i:%s') @@ -265,39 +286,35 @@ a b date_format(a.c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 select using pushdown +connection master_1; SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l a WHERE a.b = 'g' ORDER BY a.a; a b date_format(a.c, '%Y-%m-%d %H:%i:%s') 2 g 2000-02-01 00:00:00 select using index and pushdown +connection master_1; SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l a WHERE a.a > 0 AND a.b = 'g' ORDER BY a.a; a b date_format(a.c, '%Y-%m-%d %H:%i:%s') 2 g 2000-02-01 00:00:00 -select partition using pushdown -SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE -a.b = 'g' ORDER BY a.a; -a b date_format(a.c, '%Y-%m-%d %H:%i:%s') -2 g 2000-02-01 00:00:00 - -select partition using index pushdown -SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE -a.a > 0 AND a.b = 'g' ORDER BY a.a; -a b date_format(a.c, '%Y-%m-%d %H:%i:%s') -2 g 2000-02-01 00:00:00 - insert +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT INTO ta_l (a, b, c) VALUES (2, 'e', '2008-01-01 23:59:59'); +connection master_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') 2 e 2008-01-01 23:59:59 insert select +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT INTO ta_l (a, b, c) SELECT a, b, c FROM tb_l; +connection master_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 f 2008-07-01 10:21:39 @@ -307,59 +324,82 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 insert select a +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT INTO ta_l (a, b, c) VALUES ((SELECT a FROM tb_l ORDER BY a LIMIT 1), 'e', '2008-01-01 23:59:59'); +connection master_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 e 2008-01-01 23:59:59 insert low_priority +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT LOW_PRIORITY INTO ta_l (a, b, c) values (2, 'e', '2008-01-01 23:59:59'); +connection master_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') 2 e 2008-01-01 23:59:59 insert high_priority +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT HIGH_PRIORITY INTO ta_l (a, b, c) VALUES (2, 'e', '2008-01-01 23:59:59'); +connection master_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') 2 e 2008-01-01 23:59:59 insert ignore +connection master_1; INSERT IGNORE INTO ta_l (a, b, c) VALUES (2, 'd', '2009-02-02 01:01:01'); +Warnings: +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +connection master_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') 2 e 2008-01-01 23:59:59 insert update (insert) +connection master_1; TRUNCATE TABLE ta_l; +connection master_1; INSERT INTO ta_l (a, b, c) VALUES (2, 'e', '2008-01-01 23:59:59') ON DUPLICATE KEY UPDATE b = 'f', c = '2005-08-08 11:11:11'; +connection master_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') 2 e 2008-01-01 23:59:59 insert update (update) +connection master_1; INSERT INTO ta_l (a, b, c) VALUES (2, 'e', '2008-01-01 23:59:59') ON DUPLICATE KEY UPDATE b = 'f', c = '2005-08-08 11:11:11'; +connection master_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') 2 f 2005-08-08 11:11:11 replace +connection master_1; TRUNCATE TABLE ta_l; INSERT INTO ta_l (a, b, c) VALUES (2, 'e', '2008-01-01 23:59:59'); +connection master_1; REPLACE INTO ta_l (a, b, c) VALUES (2, 'f', '2008-02-02 02:02:02'); +connection master_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') 2 f 2008-02-02 02:02:02 replace select +connection master_1; REPLACE INTO ta_l (a, b, c) SELECT a, b, c FROM tb_l; +connection master_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 f 2008-07-01 10:21:39 @@ -369,8 +409,10 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 replace select a +connection master_1; REPLACE INTO ta_l (a, b, c) VALUES ((SELECT a FROM tb_l ORDER BY a LIMIT 1), 'e', '2008-01-01 23:59:59'); +connection master_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 e 2008-01-01 23:59:59 @@ -380,8 +422,10 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 h 2001-10-31 23:59:59 replace low_priority +connection master_1; REPLACE LOW_PRIORITY INTO ta_l (a, b, c) VALUES (3, 'g', '2009-03-03 03:03:03'); +connection master_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 e 2008-01-01 23:59:59 @@ -394,91 +438,89 @@ update TRUNCATE TABLE ta_l; INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (2, 'e', '2008-01-01 23:59:59'); +connection master_1; UPDATE ta_l SET b = 'f', c = '2008-02-02 02:02:02' WHERE a = 2; +connection master_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 e 2008-01-01 23:59:59 2 f 2008-02-02 02:02:02 update select +connection master_1; UPDATE ta_l SET b = 'g', c = '2009-03-03 03:03:03' WHERE a IN (SELECT a FROM tb_l); +connection master_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 g 2009-03-03 03:03:03 2 g 2009-03-03 03:03:03 update select a +connection master_1; UPDATE ta_l SET b = 'h', c = '2010-04-04 04:04:04' WHERE a = (SELECT a FROM tb_l ORDER BY a LIMIT 1); +connection master_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 h 2010-04-04 04:04:04 2 g 2009-03-03 03:03:03 update join +connection master_1; UPDATE ta_l a, tb_l b SET a.b = b.b, a.c = b.c WHERE a.a = b.a; +connection master_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 f 2008-07-01 10:21:39 2 g 2000-02-01 00:00:00 update join a +connection master_1; UPDATE ta_l a, tb_l b SET a.b = 'g', a.c = '2009-03-03 03:03:03' WHERE a.a = b.a; +connection master_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 g 2009-03-03 03:03:03 2 g 2009-03-03 03:03:03 update low_priority +connection master_1; UPDATE LOW_PRIORITY ta_l SET b = 'f', c = '2008-02-02 02:02:02' WHERE a = 2; +connection master_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 g 2009-03-03 03:03:03 2 f 2008-02-02 02:02:02 update ignore +connection master_1; UPDATE IGNORE ta_l SET a = 1, b = 'g', c = '2009-03-03 03:03:03' WHERE a = 2; +connection master_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 g 2009-03-03 03:03:03 2 f 2008-02-02 02:02:02 update pushdown +connection master_1; update ta_l set b = 'j', c = '2009-03-03 03:03:03' where b = 'f'; +connection master_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 g 2009-03-03 03:03:03 2 j 2009-03-03 03:03:03 update index pushdown +connection master_1; UPDATE ta_l SET b = 'g', c = '2009-03-03 03:03:03' WHERE a > 0 AND b = 'j'; +connection master_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 g 2009-03-03 03:03:03 2 g 2009-03-03 03:03:03 -update partition pushdown -UPDATE ta_l2 SET b = 'e', c = '2009-03-03 03:03:03' WHERE b = 'j'; -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 f 2008-07-01 10:21:39 -2 g 2000-02-01 00:00:00 -3 e 2009-03-03 03:03:03 -4 i 2003-10-30 05:01:03 -5 h 2001-10-31 23:59:59 - -update partition index pushdown -UPDATE ta_l2 SET b = 'j', c = '2009-03-03 03:03:03' WHERE a > 0 AND b = 'e'; -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 f 2008-07-01 10:21:39 -2 g 2000-02-01 00:00:00 -3 j 2009-03-03 03:03:03 -4 i 2003-10-30 05:01:03 -5 h 2001-10-31 23:59:59 - delete TRUNCATE TABLE ta_l; INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), @@ -487,7 +529,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE FROM ta_l WHERE a = 2; +connection master_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 e 2008-01-01 23:59:59 @@ -501,7 +545,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 10 j 2008-01-01 23:59:59 delete all +connection master_1; DELETE FROM ta_l; +connection master_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') @@ -513,7 +559,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE FROM ta_l WHERE a IN (SELECT a FROM tb_l); +connection master_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') 6 e 2008-01-01 23:59:59 @@ -530,7 +578,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE FROM ta_l WHERE a = (SELECT a FROM tb_l ORDER BY a LIMIT 1); +connection master_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') 2 e 2008-01-01 23:59:59 @@ -551,7 +601,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE a FROM ta_l a, (SELECT a FROM tb_l ORDER BY a) b WHERE a.a = b.a; +connection master_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') 6 e 2008-01-01 23:59:59 @@ -568,7 +620,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE LOW_PRIORITY FROM ta_l WHERE a = 2; +connection master_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 e 2008-01-01 23:59:59 @@ -589,7 +643,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE IGNORE FROM ta_l WHERE a = 2; +connection master_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 e 2008-01-01 23:59:59 @@ -610,7 +666,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE QUICK FROM ta_l WHERE a = 2; +connection master_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 e 2008-01-01 23:59:59 @@ -631,7 +689,9 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE FROM ta_l WHERE b = 'e'; +connection master_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') 10 j 2008-01-01 23:59:59 @@ -644,41 +704,26 @@ INSERT INTO ta_l (a, b, c) VALUES (1, 'e', '2008-01-01 23:59:59'), (6, 'e', '2008-01-01 23:59:59'), (7, 'e', '2008-01-01 23:59:59'), (8, 'e', '2008-01-01 23:59:59'), (9, 'e', '2008-01-01 23:59:59'), (10, 'j', '2008-01-01 23:59:59'); +connection master_1; DELETE FROM ta_l WHERE a > 0 AND b = 'e'; +connection master_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') 10 j 2008-01-01 23:59:59 -delete partition pushdown -TRUNCATE TABLE ta_l2; -INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; -DELETE FROM ta_l2 WHERE b = 'g'; -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 f 2008-07-01 10:21:39 -3 j 2007-05-04 20:03:11 -4 i 2003-10-30 05:01:03 -5 h 2001-10-31 23:59:59 - -delete partition index pushdown -TRUNCATE TABLE ta_l2; -INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; -DELETE FROM ta_l2 WHERE a > 0 AND b = 'g'; -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 f 2008-07-01 10:21:39 -3 j 2007-05-04 20:03:11 -4 i 2003-10-30 05:01:03 -5 h 2001-10-31 23:59:59 - truncate +connection master_1; TRUNCATE TABLE ta_l; +connection master_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') deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/r/basic_sql_part.result b/storage/spider/mysql-test/spider/handler/r/basic_sql_part.result new file mode 100644 index 00000000000..0f4029404a7 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/basic_sql_part.result @@ -0,0 +1,141 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +drop and create databases +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +test select 1 +connection master_1; +SELECT 1; +1 +1 +connection master_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, 'f', '2008-07-01 10:21:39'), +(2, 'g', '2000-02-01 00:00:00'), +(3, 'j', '2007-05-04 20:03:11'), +(4, 'i', '2003-10-30 05:01:03'), +(5, 'h', '2001-10-31 23:59:59'); + +create table with partition and select test +connection master_1; +CREATE TABLE ta_l2 ( +PRIMARY KEY(a) +) MASTER_1_ENGINE MASTER_1_COMMENT_P_2_1 +SELECT a, b, c FROM tb_l +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 f 2008-07-01 10:21:39 +2 g 2000-02-01 00:00:00 +3 j 2007-05-04 20:03:11 +4 i 2003-10-30 05:01:03 +5 h 2001-10-31 23:59:59 + +select partition using pushdown +connection master_1; +SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE +a.b = 'g' ORDER BY a.a; +a b date_format(a.c, '%Y-%m-%d %H:%i:%s') +2 g 2000-02-01 00:00:00 + +select partition using index pushdown +connection master_1; +SELECT a.a, a.b, date_format(a.c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 a WHERE +a.a > 0 AND a.b = 'g' ORDER BY a.a; +a b date_format(a.c, '%Y-%m-%d %H:%i:%s') +2 g 2000-02-01 00:00:00 + +update partition pushdown +connection master_1; +UPDATE ta_l2 SET b = 'e', c = '2009-03-03 03:03:03' WHERE b = 'j'; +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 f 2008-07-01 10:21:39 +2 g 2000-02-01 00:00:00 +3 e 2009-03-03 03:03:03 +4 i 2003-10-30 05:01:03 +5 h 2001-10-31 23:59:59 + +update partition index pushdown +connection master_1; +UPDATE ta_l2 SET b = 'j', c = '2009-03-03 03:03:03' WHERE a > 0 AND b = 'e'; +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 f 2008-07-01 10:21:39 +2 g 2000-02-01 00:00:00 +3 j 2009-03-03 03:03:03 +4 i 2003-10-30 05:01:03 +5 h 2001-10-31 23:59:59 + +delete partition pushdown +TRUNCATE TABLE ta_l2; +INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; +connection master_1; +DELETE FROM ta_l2 WHERE b = 'g'; +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 f 2008-07-01 10:21:39 +3 j 2007-05-04 20:03:11 +4 i 2003-10-30 05:01:03 +5 h 2001-10-31 23:59:59 + +delete partition index pushdown +TRUNCATE TABLE ta_l2; +INSERT INTO ta_l2 SELECT a, b, c FROM tb_l; +connection master_1; +DELETE FROM ta_l2 WHERE a > 0 AND b = 'g'; +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 f 2008-07-01 10:21:39 +3 j 2007-05-04 20:03:11 +4 i 2003-10-30 05:01:03 +5 h 2001-10-31 23:59:59 + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/handler/r/direct_aggregate.result b/storage/spider/mysql-test/spider/handler/r/direct_aggregate.result index 0e845fabda1..9a8660ba79e 100644 --- a/storage/spider/mysql-test/spider/handler/r/direct_aggregate.result +++ b/storage/spider/mysql-test/spider/handler/r/direct_aggregate.result @@ -9,22 +9,27 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 create table select test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -40,6 +45,7 @@ INSERT INTO ta_l (a, b, c) VALUES (5, 'c', '2001-12-31 23:59:59'); direct_aggregating test +connection master_1; SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value Spider_direct_aggregate 0 @@ -48,76 +54,38 @@ COUNT(*) 5 SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value -Spider_direct_aggregate 1 +Spider_direct_aggregate 0 SELECT MAX(a) FROM ta_l; MAX(a) 5 SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value -Spider_direct_aggregate 2 +Spider_direct_aggregate 0 SELECT MIN(a) FROM ta_l; MIN(a) 1 SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value -Spider_direct_aggregate 3 +Spider_direct_aggregate 0 SELECT MAX(a) FROM ta_l WHERE a < 5; MAX(a) 4 SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value -Spider_direct_aggregate 4 +Spider_direct_aggregate 0 SELECT MIN(a) FROM ta_l WHERE a > 1; MIN(a) 2 SHOW STATUS LIKE 'Spider_direct_aggregate'; Variable_name Value -Spider_direct_aggregate 5 - -handler with partition test -CREATE TABLE ta_l2 ( -a INT, -b CHAR(1), -c DATETIME, -PRIMARY KEY(a) -) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 5 -SELECT COUNT(*) FROM ta_l2; -COUNT(*) -5 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 7 -SELECT MAX(a) FROM ta_l2; -MAX(a) -5 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 9 -SELECT MIN(a) FROM ta_l2; -MIN(a) -1 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 11 -SELECT MAX(a) FROM ta_l2 WHERE a < 5; -MAX(a) -4 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 13 -SELECT MIN(a) FROM ta_l2 WHERE a > 1; -MIN(a) -2 -SHOW STATUS LIKE 'Spider_direct_aggregate'; -Variable_name Value -Spider_direct_aggregate 15 +Spider_direct_aggregate 0 deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/r/direct_aggregate_part.result b/storage/spider/mysql-test/spider/handler/r/direct_aggregate_part.result new file mode 100644 index 00000000000..760b39e16d5 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/direct_aggregate_part.result @@ -0,0 +1,90 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +drop and create databases +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +test select 1 +connection master_1; +SELECT 1; +1 +1 + +with partition test +connection master_1; +CREATE TABLE ta_l2 ( +a INT, +b CHAR(1), +c DATETIME, +PRIMARY KEY(a) +) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 +SELECT COUNT(*) FROM ta_l2; +COUNT(*) +5 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 +SELECT MAX(a) FROM ta_l2; +MAX(a) +5 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 +SELECT MIN(a) FROM ta_l2; +MIN(a) +1 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 +SELECT MAX(a) FROM ta_l2 WHERE a < 5; +MAX(a) +4 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 +SELECT MIN(a) FROM ta_l2 WHERE a > 1; +MIN(a) +2 +SHOW STATUS LIKE 'Spider_direct_aggregate'; +Variable_name Value +Spider_direct_aggregate 0 + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/handler/r/direct_update.result b/storage/spider/mysql-test/spider/handler/r/direct_update.result index ea3a23eb8b8..74dae7aec2e 100644 --- a/storage/spider/mysql-test/spider/handler/r/direct_update.result +++ b/storage/spider/mysql-test/spider/handler/r/direct_update.result @@ -9,22 +9,27 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 create table select test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -40,9 +45,9 @@ INSERT INTO ta_l (a, b, c) VALUES (5, 'c', '2001-12-31 23:59:59'); direct_updating test +connection master_1; SHOW STATUS LIKE 'Spider_direct_update'; Variable_name Value -Spider_direct_update 0 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 @@ -54,7 +59,6 @@ update all rows with function UPDATE ta_l SET c = ADDDATE(c, 1); SHOW STATUS LIKE 'Spider_direct_update'; Variable_name Value -Spider_direct_update 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-02 10:21:39 @@ -66,7 +70,6 @@ update by primary key UPDATE ta_l SET b = 'x' WHERE a = 3; SHOW STATUS LIKE 'Spider_direct_update'; Variable_name Value -Spider_direct_update 2 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-02 10:21:39 @@ -78,7 +81,6 @@ update by a column without index UPDATE ta_l SET c = '2011-10-17' WHERE b = 'x'; SHOW STATUS LIKE 'Spider_direct_update'; Variable_name Value -Spider_direct_update 3 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-02 10:21:39 @@ -90,7 +92,6 @@ update by primary key with order and limit UPDATE ta_l SET c = ADDDATE(c, 1) WHERE a < 4 ORDER BY b DESC LIMIT 1; SHOW STATUS LIKE 'Spider_direct_update'; Variable_name Value -Spider_direct_update 4 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-02 10:21:39 @@ -102,7 +103,6 @@ delete by primary key with order and limit DELETE FROM ta_l WHERE a < 4 ORDER BY c LIMIT 1; SHOW STATUS LIKE 'Spider_direct_delete'; Variable_name Value -Spider_direct_delete 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-02 10:21:39 @@ -113,7 +113,6 @@ delete by a column without index DELETE FROM ta_l WHERE b = 'c'; SHOW STATUS LIKE 'Spider_direct_delete'; Variable_name Value -Spider_direct_delete 2 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-02 10:21:39 @@ -123,111 +122,17 @@ delete by primary key DELETE FROM ta_l WHERE a = 3; SHOW STATUS LIKE 'Spider_direct_delete'; Variable_name Value -Spider_direct_delete 3 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-02 10:21:39 4 d 2003-12-01 05:01:03 -handler with partition test -CREATE TABLE ta_l2 ( -a INT, -b CHAR(1), -c DATETIME, -PRIMARY KEY(a) -) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1 -SHOW STATUS LIKE 'Spider_direct_update'; -Variable_name Value -Spider_direct_update 4 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 -update all rows with function -UPDATE ta_l2 SET c = ADDDATE(c, 1); -SHOW STATUS LIKE 'Spider_direct_update'; -Variable_name Value -Spider_direct_update 6 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -2 b 2000-01-02 00:00:00 -3 e 2007-06-05 20:03:11 -4 d 2003-12-01 05:01:03 -5 c 2002-01-01 23:59:59 -update by primary key -UPDATE ta_l2 SET b = 'x' WHERE a = 3; -SHOW STATUS LIKE 'Spider_direct_update'; -Variable_name Value -Spider_direct_update 7 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -2 b 2000-01-02 00:00:00 -3 x 2007-06-05 20:03:11 -4 d 2003-12-01 05:01:03 -5 c 2002-01-01 23:59:59 -update by a column without index -UPDATE ta_l2 SET c = '2011-10-17' WHERE b = 'x'; -SHOW STATUS LIKE 'Spider_direct_update'; -Variable_name Value -Spider_direct_update 9 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -2 b 2000-01-02 00:00:00 -3 x 2011-10-17 00:00:00 -4 d 2003-12-01 05:01:03 -5 c 2002-01-01 23:59:59 -update by primary key with order and limit -UPDATE ta_l2 SET c = ADDDATE(c, 1) WHERE a < 4 ORDER BY b DESC LIMIT 1; -SHOW STATUS LIKE 'Spider_direct_update'; -Variable_name Value -Spider_direct_update 10 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -2 b 2000-01-02 00:00:00 -3 x 2011-10-18 00:00:00 -4 d 2003-12-01 05:01:03 -5 c 2002-01-01 23:59:59 -delete by primary key with order and limit -DELETE FROM ta_l2 WHERE a < 4 ORDER BY c LIMIT 1; -SHOW STATUS LIKE 'Spider_direct_delete'; -Variable_name Value -Spider_direct_delete 4 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -3 x 2011-10-18 00:00:00 -4 d 2003-12-01 05:01:03 -5 c 2002-01-01 23:59:59 -delete by a column without index -DELETE FROM ta_l2 WHERE b = 'c'; -SHOW STATUS LIKE 'Spider_direct_delete'; -Variable_name Value -Spider_direct_delete 6 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -3 x 2011-10-18 00:00:00 -4 d 2003-12-01 05:01:03 -delete by primary key -DELETE FROM ta_l2 WHERE a = 3; -SHOW STATUS LIKE 'Spider_direct_delete'; -Variable_name Value -Spider_direct_delete 7 -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-02 10:21:39 -4 d 2003-12-01 05:01:03 - deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/r/direct_update_part.result b/storage/spider/mysql-test/spider/handler/r/direct_update_part.result new file mode 100644 index 00000000000..6db7c01f563 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/direct_update_part.result @@ -0,0 +1,137 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +drop and create databases +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +test select 1 +connection master_1; +SELECT 1; +1 +1 + +with partition test +connection master_1; +CREATE TABLE ta_l2 ( +a INT, +b CHAR(1), +c DATETIME, +PRIMARY KEY(a) +) MASTER_1_ENGINE MASTER_1_COMMENT2_P_2_1 +SHOW STATUS LIKE 'Spider_direct_update'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 +update all rows with function +UPDATE ta_l2 SET c = ADDDATE(c, 1); +SHOW STATUS LIKE 'Spider_direct_update'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +2 b 2000-01-02 00:00:00 +3 e 2007-06-05 20:03:11 +4 d 2003-12-01 05:01:03 +5 c 2002-01-01 23:59:59 +update by primary key +UPDATE ta_l2 SET b = 'x' WHERE a = 3; +SHOW STATUS LIKE 'Spider_direct_update'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +2 b 2000-01-02 00:00:00 +3 x 2007-06-05 20:03:11 +4 d 2003-12-01 05:01:03 +5 c 2002-01-01 23:59:59 +update by a column without index +UPDATE ta_l2 SET c = '2011-10-17' WHERE b = 'x'; +SHOW STATUS LIKE 'Spider_direct_update'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +2 b 2000-01-02 00:00:00 +3 x 2011-10-17 00:00:00 +4 d 2003-12-01 05:01:03 +5 c 2002-01-01 23:59:59 +update by primary key with order and limit +UPDATE ta_l2 SET c = ADDDATE(c, 1) WHERE a < 4 ORDER BY b DESC LIMIT 1; +SHOW STATUS LIKE 'Spider_direct_update'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +2 b 2000-01-02 00:00:00 +3 x 2011-10-18 00:00:00 +4 d 2003-12-01 05:01:03 +5 c 2002-01-01 23:59:59 +delete by primary key with order and limit +DELETE FROM ta_l2 WHERE a < 4 ORDER BY c LIMIT 1; +SHOW STATUS LIKE 'Spider_direct_delete'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +3 x 2011-10-18 00:00:00 +4 d 2003-12-01 05:01:03 +5 c 2002-01-01 23:59:59 +delete by a column without index +DELETE FROM ta_l2 WHERE b = 'c'; +SHOW STATUS LIKE 'Spider_direct_delete'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +3 x 2011-10-18 00:00:00 +4 d 2003-12-01 05:01:03 +delete by primary key +DELETE FROM ta_l2 WHERE a = 3; +SHOW STATUS LIKE 'Spider_direct_delete'; +Variable_name Value +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-02 10:21:39 +4 d 2003-12-01 05:01:03 + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/handler/r/function.result b/storage/spider/mysql-test/spider/handler/r/function.result index 764c774514b..c088a8a9541 100644 --- a/storage/spider/mysql-test/spider/handler/r/function.result +++ b/storage/spider/mysql-test/spider/handler/r/function.result @@ -9,22 +9,27 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 in() +connection master_1; CREATE TABLE t1 ( a VARCHAR(255), PRIMARY KEY(a) @@ -41,12 +46,14 @@ insert into t1 select a + 128 from t1; insert into t1 select a + 256 from t1; insert into t1 select a + 512 from t1; flush tables; +connection master_1; select a from t1 where a in ('15', '120'); a 120 15 date_sub() +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -124,6 +131,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 4 d 2003-02-03 06:00:03 5 c 2001-03-07 00:58:59 UPDATE ta_l SET c = DATE_ADD(c, INTERVAL 1 SECOND); +connection master_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 2007-10-07 11:20:40 @@ -133,8 +141,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-03-07 00:59:00 deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/r/ha.result b/storage/spider/mysql-test/spider/handler/r/ha.result index e05ecb32e1b..f8833c229ef 100644 --- a/storage/spider/mysql-test/spider/handler/r/ha.result +++ b/storage/spider/mysql-test/spider/handler/r/ha.result @@ -18,34 +18,43 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; +connection child2_3; DROP DATABASE IF EXISTS auto_test_remote3; CREATE DATABASE auto_test_remote3; USE auto_test_remote3; +connection child3_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child3_2; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child3_3; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; test select 1 +connection master_1; SELECT 1; 1 1 create table test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -61,6 +70,7 @@ INSERT INTO ta_l (a, b, c) VALUES (5, 'c', '2001-12-31 23:59:59'); select test +connection master_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 @@ -70,6 +80,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-12-31 23:59:59 fail-over test +connection master_1; SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; Variable_name Value Spider_mon_table_cache_version 0 @@ -101,6 +112,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 6 e 2011-05-05 20:04:05 recovery test +connection master_1; ALTER TABLE ta_l CONNECTION='host "localhost", user "root", password "", msi "5", mkd "2", @@ -113,6 +125,7 @@ auto_test_local ta_l 1 2 SELECT spider_copy_tables('ta_l', '0', '1'); spider_copy_tables('ta_l', '0', '1') 1 +connection master_1; ALTER TABLE ta_l CONNECTION='host "localhost", user "root", password "", msi "5", mkd "2", @@ -134,117 +147,14 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 6 e 2011-05-05 20:04:05 8 g 2011-05-05 21:33:30 DROP TABLE ta_l; +connection master_1; SELECT spider_flush_table_mon_cache(); spider_flush_table_mon_cache() 1 -create table with partition test -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_CHARSET MASTER_1_COMMENT_HA_P_2_1 -INSERT INTO ta_l2 (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'); - -select test -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 - -fail-over test -SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; -Variable_name Value -Spider_mon_table_cache_version 1 -Spider_mon_table_cache_version_req 2 -INSERT INTO ta_l2 (a, b, c) VALUES -(6, 'e', '2011-05-05 20:04:05'); -ERROR HY000: Table 'auto_test_remote3.ta_r4' get a problem -SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables -ORDER BY db_name, table_name, link_id; -db_name table_name link_id link_status -auto_test_local ta_l2#P#pt1 0 1 -auto_test_local ta_l2#P#pt1 1 1 -auto_test_local ta_l2#P#pt2 0 1 -auto_test_local ta_l2#P#pt2 1 3 -SELECT db_name, table_name, link_id FROM mysql.spider_link_failed_log; -db_name table_name link_id -auto_test_local ta_l 1 -auto_test_local ta_l2#P#pt2 1 -SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; -Variable_name Value -Spider_mon_table_cache_version 2 -Spider_mon_table_cache_version_req 2 -INSERT INTO ta_l2 (a, b, c) VALUES -(6, 'e', '2011-05-05 20:04:05'); -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 -6 e 2011-05-05 20:04:05 - -recovery test -ALTER TABLE ta_l2 -PARTITION BY KEY(a) ( -PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", - priority "1000"', -PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", - priority "1000001", lst "0 2"' - ); -SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables -ORDER BY db_name, table_name, link_id; -db_name table_name link_id link_status -auto_test_local ta_l2#P#pt1 0 1 -auto_test_local ta_l2#P#pt1 1 1 -auto_test_local ta_l2#P#pt2 0 1 -auto_test_local ta_l2#P#pt2 1 2 -SELECT spider_copy_tables('ta_l2#P#pt2', '0', '1'); -spider_copy_tables('ta_l2#P#pt2', '0', '1') -1 -ALTER TABLE ta_l2 -PARTITION BY KEY(a) ( -PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", - priority "1000"', -PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", - priority "1000001", lst "0 1"' - ); -SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables -ORDER BY db_name, table_name, link_id; -db_name table_name link_id link_status -auto_test_local ta_l2#P#pt1 0 1 -auto_test_local ta_l2#P#pt1 1 1 -auto_test_local ta_l2#P#pt2 0 1 -auto_test_local ta_l2#P#pt2 1 1 -INSERT INTO ta_l2 (a, b, c) VALUES -(8, 'g', '2011-05-05 21:33:30'), -(9, 'h', '2011-05-05 22:32:10'); -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 -6 e 2011-05-05 20:04:05 -8 g 2011-05-05 21:33:30 -9 h 2011-05-05 22:32:10 -DROP TABLE ta_l2; - active standby test create table test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -260,6 +170,7 @@ INSERT INTO ta_l (a, b, c) VALUES (5, 'c', '2001-12-31 23:59:59'); select test +connection master_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 @@ -269,9 +180,10 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 5 c 2001-12-31 23:59:59 fail-over test +connection master_1; SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; Variable_name Value -Spider_mon_table_cache_version 2 +Spider_mon_table_cache_version 1 Spider_mon_table_cache_version_req 2 INSERT INTO ta_l (a, b, c) VALUES (6, 'e', '2011-05-05 20:04:05'); @@ -284,7 +196,6 @@ auto_test_local ta_l 1 1 SELECT db_name, table_name, link_id FROM mysql.spider_link_failed_log; db_name table_name link_id auto_test_local ta_l 1 -auto_test_local ta_l2#P#pt2 1 auto_test_local ta_l 0 SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; Variable_name Value @@ -297,6 +208,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 6 e 2011-05-05 20:04:05 recovery test +connection master_1; ALTER TABLE ta_l CONNECTION='host "localhost", user "root", password "", msi "5", mkd "2", alc "1", @@ -312,102 +224,25 @@ 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') 8 g 2011-05-05 21:33:30 DROP TABLE ta_l; +connection master_1; SELECT spider_flush_table_mon_cache(); spider_flush_table_mon_cache() 1 -create table with partition test -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_CHARSET MASTER_1_COMMENT_HA_AS_P_2_1 -INSERT INTO ta_l2 (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'); - -select test -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 - -fail-over test -SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; -Variable_name Value -Spider_mon_table_cache_version 2 -Spider_mon_table_cache_version_req 3 -INSERT INTO ta_l2 (a, b, c) VALUES -(6, 'e', '2011-05-05 20:04:05'); -ERROR HY000: Table 'auto_test_remote.ta_r2' get a problem -SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables -ORDER BY db_name, table_name, link_id; -db_name table_name link_id link_status -auto_test_local ta_l2#P#pt1 0 1 -auto_test_local ta_l2#P#pt1 1 1 -auto_test_local ta_l2#P#pt2 0 3 -auto_test_local ta_l2#P#pt2 1 1 -SELECT db_name, table_name, link_id FROM mysql.spider_link_failed_log; -db_name table_name link_id -auto_test_local ta_l 1 -auto_test_local ta_l2#P#pt2 1 -auto_test_local ta_l 0 -auto_test_local ta_l2#P#pt2 0 -SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; -Variable_name Value -Spider_mon_table_cache_version 3 -Spider_mon_table_cache_version_req 3 -INSERT INTO ta_l2 (a, b, c) VALUES -(6, 'e', '2011-05-05 20:04:05'); -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-01 10:21:39 -3 e 2007-06-04 20:03:11 -5 c 2001-12-31 23:59:59 -6 e 2011-05-05 20:04:05 - -recovery test -ALTER TABLE ta_l2 -PARTITION BY KEY(a) ( -PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", - priority "1000"', -PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", - priority "1000001", lst "1 0"' - ); -SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables -ORDER BY db_name, table_name, link_id; -db_name table_name link_id link_status -auto_test_local ta_l2#P#pt1 0 1 -auto_test_local ta_l2#P#pt1 1 1 -auto_test_local ta_l2#P#pt2 0 1 -auto_test_local ta_l2#P#pt2 1 1 -INSERT INTO ta_l2 (a, b, c) VALUES -(8, 'g', '2011-05-05 21:33:30'), -(9, 'h', '2011-05-05 22:32:10'); -SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; -a b date_format(c, '%Y-%m-%d %H:%i:%s') -1 a 2008-08-01 10:21:39 -3 e 2007-06-04 20:03:11 -5 c 2001-12-31 23:59:59 -8 g 2011-05-05 21:33:30 -9 h 2011-05-05 22:32:10 -DROP TABLE ta_l2; - deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; +connection child2_3; DROP DATABASE IF EXISTS auto_test_remote3; +connection child3_1; DROP DATABASE IF EXISTS auto_test_local; +connection child3_2; DROP DATABASE IF EXISTS auto_test_local; +connection child3_3; DROP DATABASE IF EXISTS auto_test_local; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/r/ha_part.result b/storage/spider/mysql-test/spider/handler/r/ha_part.result new file mode 100644 index 00000000000..315f37298bc --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/ha_part.result @@ -0,0 +1,286 @@ +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +drop and create databases +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; +connection child2_3; +DROP DATABASE IF EXISTS auto_test_remote3; +CREATE DATABASE auto_test_remote3; +USE auto_test_remote3; +connection child3_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child3_2; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child3_3; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; + +test select 1 +connection master_1; +SELECT 1; +1 +1 + +create table with partition test +connection master_1; +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_CHARSET MASTER_1_COMMENT_HA_P_2_1 +INSERT INTO ta_l2 (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'); + +select test +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 + +fail-over test +connection master_1; +SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; +Variable_name Value +Spider_mon_table_cache_version 0 +Spider_mon_table_cache_version_req 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(6, 'e', '2011-05-05 20:04:05'); +ERROR HY000: Table 'auto_test_remote3.ta_r4' get a problem +SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables +ORDER BY db_name, table_name, link_id; +db_name table_name link_id link_status +auto_test_local ta_l2#P#pt1 0 1 +auto_test_local ta_l2#P#pt1 1 1 +auto_test_local ta_l2#P#pt2 0 1 +auto_test_local ta_l2#P#pt2 1 3 +SELECT db_name, table_name, link_id FROM mysql.spider_link_failed_log; +db_name table_name link_id +auto_test_local ta_l2#P#pt2 1 +SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; +Variable_name Value +Spider_mon_table_cache_version 1 +Spider_mon_table_cache_version_req 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(6, 'e', '2011-05-05 20:04:05'); +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 +6 e 2011-05-05 20:04:05 + +recovery test +connection master_1; +ALTER TABLE ta_l2 +PARTITION BY KEY(a) ( +PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", + priority "1000"', +PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", + priority "1000001", lst "0 2"' + ); +SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables +ORDER BY db_name, table_name, link_id; +db_name table_name link_id link_status +auto_test_local ta_l2#P#pt1 0 1 +auto_test_local ta_l2#P#pt1 1 1 +auto_test_local ta_l2#P#pt2 0 1 +auto_test_local ta_l2#P#pt2 1 2 +SELECT spider_copy_tables('ta_l2#P#pt2', '0', '1'); +spider_copy_tables('ta_l2#P#pt2', '0', '1') +1 +connection master_1; +ALTER TABLE ta_l2 +PARTITION BY KEY(a) ( +PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", + priority "1000"', +PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", + priority "1000001", lst "0 1"' + ); +SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables +ORDER BY db_name, table_name, link_id; +db_name table_name link_id link_status +auto_test_local ta_l2#P#pt1 0 1 +auto_test_local ta_l2#P#pt1 1 1 +auto_test_local ta_l2#P#pt2 0 1 +auto_test_local ta_l2#P#pt2 1 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(8, 'g', '2011-05-05 21:33:30'), +(9, 'h', '2011-05-05 22:32:10'); +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 +6 e 2011-05-05 20:04:05 +8 g 2011-05-05 21:33:30 +9 h 2011-05-05 22:32:10 +DROP TABLE ta_l2; + +create table with partition test +connection master_1; +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_CHARSET MASTER_1_COMMENT_HA_AS_P_2_1 +INSERT INTO ta_l2 (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'); + +select test +connection master_1; +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 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-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 + +fail-over test +connection master_1; +SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; +Variable_name Value +Spider_mon_table_cache_version 1 +Spider_mon_table_cache_version_req 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(6, 'e', '2011-05-05 20:04:05'); +ERROR HY000: Table 'auto_test_remote.ta_r2' get a problem +SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables +ORDER BY db_name, table_name, link_id; +db_name table_name link_id link_status +auto_test_local ta_l2#P#pt1 0 1 +auto_test_local ta_l2#P#pt1 1 1 +auto_test_local ta_l2#P#pt2 0 3 +auto_test_local ta_l2#P#pt2 1 1 +SELECT db_name, table_name, link_id FROM mysql.spider_link_failed_log; +db_name table_name link_id +auto_test_local ta_l2#P#pt2 1 +auto_test_local ta_l2#P#pt2 0 +SHOW STATUS LIKE 'Spider_mon_table_cache_version%'; +Variable_name Value +Spider_mon_table_cache_version 1 +Spider_mon_table_cache_version_req 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(6, 'e', '2011-05-05 20:04:05'); +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-01 10:21:39 +3 e 2007-06-04 20:03:11 +5 c 2001-12-31 23:59:59 +6 e 2011-05-05 20:04:05 + +recovery test +connection master_1; +ALTER TABLE ta_l2 +PARTITION BY KEY(a) ( +PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", + priority "1000"', +PARTITION pt2 COMMENT='srv "s_2_1 s_2_3", tbl "ta_r2 ta_r4", + priority "1000001", lst "1 0"' + ); +SELECT db_name, table_name, link_id, link_status FROM mysql.spider_tables +ORDER BY db_name, table_name, link_id; +db_name table_name link_id link_status +auto_test_local ta_l2#P#pt1 0 1 +auto_test_local ta_l2#P#pt1 1 1 +auto_test_local ta_l2#P#pt2 0 1 +auto_test_local ta_l2#P#pt2 1 1 +INSERT INTO ta_l2 (a, b, c) VALUES +(8, 'g', '2011-05-05 21:33:30'), +(9, 'h', '2011-05-05 22:32:10'); +SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l2 ORDER BY a; +a b date_format(c, '%Y-%m-%d %H:%i:%s') +1 a 2008-08-01 10:21:39 +3 e 2007-06-04 20:03:11 +5 c 2001-12-31 23:59:59 +8 g 2011-05-05 21:33:30 +9 h 2011-05-05 22:32:10 +DROP TABLE ta_l2; + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +connection child2_3; +DROP DATABASE IF EXISTS auto_test_remote3; +connection child3_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child3_2; +DROP DATABASE IF EXISTS auto_test_local; +connection child3_3; +DROP DATABASE IF EXISTS auto_test_local; +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 +for master_1 +for child2 +child2_1 +child2_2 +child2_3 +for child3 +child3_1 +child3_2 +child3_3 + +end of test diff --git a/storage/spider/mysql-test/spider/handler/r/spider3_fixes.result b/storage/spider/mysql-test/spider/handler/r/spider3_fixes.result index 6de174e1a2d..9a8a59153f0 100644 --- a/storage/spider/mysql-test/spider/handler/r/spider3_fixes.result +++ b/storage/spider/mysql-test/spider/handler/r/spider3_fixes.result @@ -10,26 +10,34 @@ child3_3 for slave1_1 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection slave1_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 3.1 auto_increment +connection master_1; +connection slave1_1; +connection master_1; DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, @@ -198,6 +206,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t2; MAX(id) 46 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 2 @@ -206,188 +215,16 @@ id 1554 2331 10000 -auto_increment with partition -DROP TABLE IF EXISTS t1, t2; -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 -CREATE TABLE t2 ( -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('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', -'srv "s_2_1"') -1 -spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', -'srv "s_2_2"') -1 -spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 2', '', -'srv "s_2_1"') -1 -spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 3', '', -'srv "s_2_2"') -1 -CREATE TABLE t1 ( -id int(11) NOT NULL AUTO_INCREMENT, -PRIMARY KEY (id) -) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 -CREATE TABLE t2 ( -id int(11) NOT NULL AUTO_INCREMENT, -PRIMARY KEY (id) -) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 -INSERT INTO t1 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -2 -SELECT MAX(id) FROM t1; -MAX(id) -2 -INSERT INTO t2 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -3 -SELECT MAX(id) FROM t2; -MAX(id) -3 -MASTER_1_AUTO_INCREMENT_OFFSET3 -INSERT INTO t1 (id) VALUES (null); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -7 -SELECT MAX(id) FROM t1; -MAX(id) -7 -MASTER_1_AUTO_INCREMENT_OFFSET4 -INSERT INTO t2 (id) VALUES (null); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -6 -SELECT MAX(id) FROM t2; -MAX(id) -7 -MASTER_1_AUTO_INCREMENT_OFFSET3 -INSERT INTO t1 () VALUES (),(),(),(); -Warnings: -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -10 -SELECT id FROM t1 ORDER BY id; -id -2 -3 -6 -7 -10 -11 -14 -15 -MASTER_1_AUTO_INCREMENT_OFFSET4 -INSERT INTO t2 () VALUES (),(),(),(); -Warnings: -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -18 -SELECT id FROM t2 ORDER BY id; -id -2 -3 -6 -7 -10 -11 -14 -15 -18 -19 -22 -23 -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; -INSERT INTO t1 () VALUES (),(),(),(); -Warnings: -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -2 -SELECT id FROM t1 ORDER BY id; -id -2 -3 -6 -7 -INSERT INTO t2 () VALUES (),(),(),(); -Warnings: -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -10 -SELECT id FROM t2 ORDER BY id; -id -2 -3 -6 -7 -10 -11 -14 -15 -SET INSERT_ID=5000; -MASTER_1_AUTO_INCREMENT_OFFSET3 -INSERT INTO t1 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -18 -SELECT MAX(id) FROM t1; -MAX(id) -18 -MASTER_1_AUTO_INCREMENT_OFFSET4 -INSERT INTO t2 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -19 -SELECT MAX(id) FROM t2; -MAX(id) -19 -INSERT INTO t1 (id) VALUES (10000); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -22 -SELECT MAX(id) FROM t1; -MAX(id) -22 -INSERT INTO t2 (id) VALUES (1000); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -26 -SELECT MAX(id) FROM t2; -MAX(id) -26 -SELECT id FROM t1 ORDER BY id; -id -2 -18 -777 -1554 -2331 -10000 +connection master_1; deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection slave1_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for slave1_1 for master_1 diff --git a/storage/spider/mysql-test/spider/handler/r/spider3_fixes_part.result b/storage/spider/mysql-test/spider/handler/r/spider3_fixes_part.result new file mode 100644 index 00000000000..f8747cff5ea --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/spider3_fixes_part.result @@ -0,0 +1,238 @@ +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 +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection slave1_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +test select 1 +connection master_1; +SELECT 1; +1 +1 +auto_increment with partition +connection master_1; +connection slave1_1; +connection master_1; +DROP TABLE IF EXISTS t1, t2; +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 +CREATE TABLE t2 ( +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('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', +'srv "s_2_1"') +1 +spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', +'srv "s_2_2"') +1 +spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 2', '', +'srv "s_2_1"') +1 +spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 3', '', +'srv "s_2_2"') +1 +CREATE TABLE t1 ( +id int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (id) +) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 +CREATE TABLE t2 ( +id int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (id) +) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 +INSERT INTO t1 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT MAX(id) FROM t1; +MAX(id) +3 +INSERT INTO t2 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +7 +SELECT MAX(id) FROM t2; +MAX(id) +7 +MASTER_1_AUTO_INCREMENT_OFFSET3 +INSERT INTO t1 (id) VALUES (null); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +2 +SELECT MAX(id) FROM t1; +MAX(id) +7 +MASTER_1_AUTO_INCREMENT_OFFSET4 +INSERT INTO t2 (id) VALUES (null); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +6 +SELECT MAX(id) FROM t2; +MAX(id) +7 +MASTER_1_AUTO_INCREMENT_OFFSET3 +INSERT INTO t1 () VALUES (),(),(),(); +Warnings: +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +10 +SELECT id FROM t1 ORDER BY id; +id +2 +3 +6 +7 +10 +11 +14 +15 +MASTER_1_AUTO_INCREMENT_OFFSET4 +INSERT INTO t2 () VALUES (),(),(),(); +Warnings: +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +18 +SELECT id FROM t2 ORDER BY id; +id +2 +3 +6 +7 +10 +11 +14 +15 +18 +19 +22 +23 +TRUNCATE TABLE t1; +TRUNCATE TABLE t2; +INSERT INTO t1 () VALUES (),(),(),(); +Warnings: +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +2 +SELECT id FROM t1 ORDER BY id; +id +2 +3 +6 +7 +INSERT INTO t2 () VALUES (),(),(),(); +Warnings: +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +10 +SELECT id FROM t2 ORDER BY id; +id +2 +3 +6 +7 +10 +11 +14 +15 +SET INSERT_ID=5000; +MASTER_1_AUTO_INCREMENT_OFFSET3 +INSERT INTO t1 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +18 +SELECT MAX(id) FROM t1; +MAX(id) +18 +MASTER_1_AUTO_INCREMENT_OFFSET4 +INSERT INTO t2 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +19 +SELECT MAX(id) FROM t2; +MAX(id) +19 +INSERT INTO t1 (id) VALUES (10000); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +22 +SELECT MAX(id) FROM t1; +MAX(id) +22 +INSERT INTO t2 (id) VALUES (1000); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +26 +SELECT MAX(id) FROM t2; +MAX(id) +26 +connection slave1_1; +SELECT id FROM t1 ORDER BY id; +id +2 +18 +777 +1554 +2331 +10000 +connection master_1; + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection slave1_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +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 diff --git a/storage/spider/mysql-test/spider/handler/r/spider_fixes.result b/storage/spider/mysql-test/spider/handler/r/spider_fixes.result index 9fd24bcc43f..9b14817eee4 100644 --- a/storage/spider/mysql-test/spider/handler/r/spider_fixes.result +++ b/storage/spider/mysql-test/spider/handler/r/spider_fixes.result @@ -10,25 +10,31 @@ child3_3 for slave1_1 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection slave1_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 create table and insert +connection master_1; DROP TABLE IF EXISTS tb_l; CREATE TABLE tb_l ( a INT, @@ -50,6 +56,7 @@ INSERT INTO ta_l SELECT a, b, c FROM tb_l; 2.13 select table with "order by desc" and "<" +connection master_1; 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') @@ -58,6 +65,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 2 b 2000-01-01 00:00:00 select table with "order by desc" and "<=" +connection master_1; 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') @@ -67,7 +75,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 2.14 update table with range scan and split_read +connection master_1; UPDATE ta_l SET c = '2000-02-02 00:00:00' WHERE a > 1; +connection master_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 @@ -80,6 +90,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') select table with range scan TRUNCATE TABLE ta_l; DROP TABLE IF EXISTS ta_l; +connection master_1; CREATE TABLE ta_l ( a int(11) NOT NULL DEFAULT '0', b char(1) DEFAULT NULL, @@ -87,50 +98,62 @@ 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; +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 +connection master_1; 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 @@ -138,6 +161,7 @@ a b c 2.16 auto_increment insert with trigger +connection master_1; CREATE TABLE ta_l_auto_inc ( a INT AUTO_INCREMENT, b CHAR(1) DEFAULT 'c', @@ -151,14 +175,17 @@ 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;; +connection master_1; INSERT INTO ta_l_auto_inc (a, b, c) VALUES (NULL, 's', '2008-12-31 20:59:59'); +connection master_1; 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 +connection master_1; 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 @@ -166,37 +193,10 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 3 e 2007-06-04 20:03:11 4 d 2003-11-30 05:01:03 5 c 2001-12-31 23:59:59 -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 +connection master_1; CREATE TABLE ta_l_int ( a INT AUTO_INCREMENT, b INT DEFAULT 10, @@ -210,27 +210,7 @@ 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 -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; +connection master_1; 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 @@ -240,6 +220,7 @@ a b c 2.24 index scan update without PK +connection master_1; DROP TABLE IF EXISTS ta_l_int; CREATE TABLE ta_l_int ( a INT NOT NULL, @@ -250,32 +231,51 @@ 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 (1, 2, 3); +INSERT INTO ta_l_int (a, b, c) VALUES (18, 2, 3); +connection master_1; UPDATE ta_l_int SET c = 4 WHERE b = 2; +connection master_1; SELECT a, b, c FROM ta_l_int ORDER BY a; a b c 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 17 2 4 +18 2 4 2.25 direct order limit +connection master_1; SHOW STATUS LIKE 'Spider_direct_order_limit'; Variable_name Value Spider_direct_order_limit 2 @@ -283,13 +283,14 @@ SELECT a, b, c FROM ta_l_int ORDER BY a LIMIT 3; a b c 1 2 4 2 3 4 -4 5 6 +3 4 5 SHOW STATUS LIKE 'Spider_direct_order_limit'; Variable_name Value Spider_direct_order_limit 3 2.26 lock tables +connection master_1; DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( @@ -304,6 +305,9 @@ LOCK TABLES t1 READ, t2 READ; UNLOCK TABLES; auto_increment +connection master_1; +connection slave1_1; +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL AUTO_INCREMENT, @@ -395,6 +399,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t1; MAX(id) 42 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 2 @@ -408,114 +413,10 @@ id 1554 2331 10000 - -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('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', -'srv "s_2_1"') -1 -spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', -'srv "s_2_2"') -1 -spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 2', '', -'srv "s_2_1"') -1 -spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 3', '', -'srv "s_2_2"') -1 -CREATE TABLE t1 ( -id int(11) NOT NULL AUTO_INCREMENT, -PRIMARY KEY (id) -) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 -INSERT INTO t1 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -2 -SELECT MAX(id) FROM t1; -MAX(id) -2 -INSERT INTO t1 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -3 -SELECT MAX(id) FROM t1; -MAX(id) -3 -INSERT INTO t1 (id) VALUES (null); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -6 -SELECT MAX(id) FROM t1; -MAX(id) -6 -INSERT INTO t1 (id) VALUES (null); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -7 -SELECT MAX(id) FROM t1; -MAX(id) -7 -INSERT INTO t1 () VALUES (),(),(),(); -Warnings: -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -10 -SELECT id FROM t1 ORDER BY id; -id -2 -3 -6 -7 -10 -11 -14 -15 -SET INSERT_ID=5000; -INSERT INTO t1 () VALUES (); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -18 -SELECT MAX(id) FROM t1; -MAX(id) -18 -INSERT INTO t1 (id) VALUES (10000); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -22 -SELECT MAX(id) FROM t1; -MAX(id) -22 -INSERT INTO t1 (id) VALUES (1000); -SELECT LAST_INSERT_ID(); -LAST_INSERT_ID() -26 -SELECT MAX(id) FROM t1; -MAX(id) -26 -SELECT id FROM t1 ORDER BY id; -id -2 -3 -6 -7 -10 -18 -777 -1000 -1554 -2331 -10000 +connection master_1; read only +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, @@ -530,6 +431,10 @@ id 18 22 26 +30 +34 +38 +42 INSERT INTO t1 (id) VALUES (1); ERROR HY000: Table 'auto_test_local.t1' is read only UPDATE t1 SET id = 4 WHERE id = 2; @@ -543,6 +448,7 @@ ERROR HY000: Table 'auto_test_local.t1' is read only 2.27 error mode +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, @@ -566,6 +472,7 @@ Error 1146 Table 'auto_test_remote.ter1_1' doesn't exist 3.0 is null +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a VARCHAR(255), @@ -588,6 +495,7 @@ 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; +connection master_1; select a from t1 where a is null order by a limit 30; a NULL @@ -654,6 +562,7 @@ NULL NULL direct_order_limit +connection master_1; TRUNCATE TABLE t1; insert into t1 values ('1', '1', '1'); insert into t1 select a + 1, b + 1, c + 1 from t1; @@ -665,6 +574,7 @@ 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; +connection master_1; select a, b, c from t1 where a = '10' and b <> '100' order by c desc limit 5; a b c 10 74 74 @@ -681,9 +591,13 @@ a c 10 170 deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection slave1_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for slave1_1 for master_1 diff --git a/storage/spider/mysql-test/spider/handler/r/spider_fixes_part.result b/storage/spider/mysql-test/spider/handler/r/spider_fixes_part.result new file mode 100644 index 00000000000..c99c02071b6 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/r/spider_fixes_part.result @@ -0,0 +1,241 @@ +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 +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection slave1_1; +DROP DATABASE IF EXISTS auto_test_local; +CREATE DATABASE auto_test_local; +USE auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +CREATE DATABASE auto_test_remote; +USE auto_test_remote; +connection child2_2; +DROP DATABASE IF EXISTS auto_test_remote2; +CREATE DATABASE auto_test_remote2; +USE auto_test_remote2; + +test select 1 +connection master_1; +SELECT 1; +1 +1 +connection master_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 +connection master_1; +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 +connection master_1; +DROP TABLE IF EXISTS ta_l2; +connection master_1; +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 +connection master_1; +DROP TABLE IF EXISTS ta_l_int; +connection master_1; +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; +connection master_1; +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 +connection master_1; +connection slave1_1; +connection master_1; +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('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', +'srv "s_2_1"') +1 +spider_direct_sql('SET SESSION AUTO_INCREMENT_INCREMENT = 4', '', +'srv "s_2_2"') +1 +spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 2', '', +'srv "s_2_1"') +1 +spider_bg_direct_sql('SET SESSION AUTO_INCREMENT_OFFSET = 3', '', +'srv "s_2_2"') +1 +CREATE TABLE t1 ( +id int(11) NOT NULL AUTO_INCREMENT, +PRIMARY KEY (id) +) SLAVE1_1_ENGINE SLAVE1_1_CHARSET SLAVE1_1_COMMENT_INCREMENT1_P_1 +INSERT INTO t1 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +3 +SELECT MAX(id) FROM t1; +MAX(id) +3 +INSERT INTO t1 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +2 +SELECT MAX(id) FROM t1; +MAX(id) +3 +INSERT INTO t1 (id) VALUES (null); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +7 +SELECT MAX(id) FROM t1; +MAX(id) +7 +INSERT INTO t1 (id) VALUES (null); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +6 +SELECT MAX(id) FROM t1; +MAX(id) +7 +INSERT INTO t1 () VALUES (),(),(),(); +Warnings: +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +Note 12520 Binlog's auto-inc value is probably different from linked table's auto-inc value +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +10 +SELECT id FROM t1 ORDER BY id; +id +2 +3 +6 +7 +10 +11 +14 +15 +SET INSERT_ID=5000; +INSERT INTO t1 () VALUES (); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +18 +SELECT MAX(id) FROM t1; +MAX(id) +18 +INSERT INTO t1 (id) VALUES (10000); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +22 +SELECT MAX(id) FROM t1; +MAX(id) +22 +INSERT INTO t1 (id) VALUES (1000); +SELECT LAST_INSERT_ID(); +LAST_INSERT_ID() +26 +SELECT MAX(id) FROM t1; +MAX(id) +26 +connection slave1_1; +SELECT id FROM t1 ORDER BY id; +id +2 +3 +6 +7 +10 +18 +777 +1000 +1554 +2331 +10000 +connection master_1; + +deinit +connection master_1; +DROP DATABASE IF EXISTS auto_test_local; +connection slave1_1; +DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; +DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; +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 diff --git a/storage/spider/mysql-test/spider/handler/r/vp_fixes.result b/storage/spider/mysql-test/spider/handler/r/vp_fixes.result index 15dd29aa4d3..cc0e4105d61 100644 --- a/storage/spider/mysql-test/spider/handler/r/vp_fixes.result +++ b/storage/spider/mysql-test/spider/handler/r/vp_fixes.result @@ -9,22 +9,27 @@ child3_2 child3_3 drop and create databases +connection master_1; DROP DATABASE IF EXISTS auto_test_local; CREATE DATABASE auto_test_local; USE auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; CREATE DATABASE auto_test_remote; USE auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; CREATE DATABASE auto_test_remote2; USE auto_test_remote2; test select 1 +connection master_1; SELECT 1; 1 1 create table and insert +connection master_1; DROP TABLE IF EXISTS tb_l; CREATE TABLE tb_l ( a INT, @@ -46,26 +51,34 @@ INSERT INTO ta_l SELECT a, b, c FROM tb_l; 0.9 create different primary key table +connection master_1; CREATE TABLE ta_l_int ( a INT DEFAULT 10, b INT AUTO_INCREMENT, c INT DEFAULT 11, PRIMARY KEY(b) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1 +connection master_1; INSERT INTO ta_l_int (a, b, c) VALUES (2, NULL, 3); create un-correspond primary key table +connection master_1; DROP TABLE IF EXISTS ta_l_int; +connection master_1; CREATE TABLE ta_l_int ( a INT DEFAULT 10, b INT DEFAULT 12, c INT DEFAULT 11, PRIMARY KEY(c) ) MASTER_1_ENGINE MASTER_1_CHARSET MASTER_1_COMMENT4_2_1 +connection master_1; INSERT INTO ta_l_int (a, b, c) VALUES (2, NULL, 3); deinit +connection master_1; DROP DATABASE IF EXISTS auto_test_local; +connection child2_1; DROP DATABASE IF EXISTS auto_test_remote; +connection child2_2; DROP DATABASE IF EXISTS auto_test_remote2; for master_1 for child2 diff --git a/storage/spider/mysql-test/spider/handler/suite.opt b/storage/spider/mysql-test/spider/handler/suite.opt new file mode 100644 index 00000000000..48457b17309 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/suite.opt @@ -0,0 +1 @@ +--loose-innodb diff --git a/storage/spider/mysql-test/spider/handler/suite.pm b/storage/spider/mysql-test/spider/handler/suite.pm new file mode 100644 index 00000000000..f106147deb6 --- /dev/null +++ b/storage/spider/mysql-test/spider/handler/suite.pm @@ -0,0 +1,12 @@ +package My::Suite::Spider; + +@ISA = qw(My::Suite); + +return "No Spider engine" unless $ENV{HA_SPIDER_SO}; +return "Not run for embedded server" if $::opt_embedded_server; +return "Test needs --big-test" unless $::opt_big_test; + +sub is_default { 1 } + +bless { }; + |