diff options
author | Sergey Vojtovich <svoj@mariadb.org> | 2016-03-25 20:51:22 +0400 |
---|---|---|
committer | Sergey Vojtovich <svoj@mariadb.org> | 2016-03-31 10:11:16 +0400 |
commit | 282497dd6d1049b4fb963641504c2733752845a7 (patch) | |
tree | 7288d17c29fbbe9ac47ec51f6988fb954f59a361 /storage/spider | |
parent | 5052e2479e873461bebfcedbc674bbaf57d3c968 (diff) | |
download | mariadb-git-282497dd6d1049b4fb963641504c2733752845a7.tar.gz |
MDEV-6720 - enable connection log in mysqltest by default
Diffstat (limited to 'storage/spider')
28 files changed, 660 insertions, 0 deletions
diff --git a/storage/spider/mysql-test/spider/bg/r/basic_sql.result b/storage/spider/mysql-test/spider/bg/r/basic_sql.result index 1e9fe78acea..94a09fc317b 100644 --- a/storage/spider/mysql-test/spider/bg/r/basic_sql.result +++ b/storage/spider/mysql-test/spider/bg/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 ( @@ -76,6 +83,7 @@ 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 @@ -85,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 @@ -99,10 +109,12 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -112,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 @@ -121,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') @@ -131,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') @@ -141,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') @@ -151,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') @@ -161,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') @@ -171,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') @@ -181,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') @@ -191,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') @@ -201,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') @@ -211,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') @@ -218,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') @@ -233,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') @@ -243,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') @@ -258,27 +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 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 @@ -288,61 +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 @@ -352,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 @@ -363,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 @@ -377,66 +438,84 @@ 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 @@ -450,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 @@ -464,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') @@ -476,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 @@ -493,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 @@ -514,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 @@ -531,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 @@ -552,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 @@ -573,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 @@ -594,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 @@ -607,19 +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 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/bg/r/basic_sql_part.result b/storage/spider/mysql-test/spider/bg/r/basic_sql_part.result index 9e1201c17c9..0f4029404a7 100644 --- a/storage/spider/mysql-test/spider/bg/r/basic_sql_part.result +++ b/storage/spider/mysql-test/spider/bg/r/basic_sql_part.result @@ -9,20 +9,25 @@ 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, @@ -38,10 +43,12 @@ INSERT INTO tb_l (a, b, c) VALUES (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 @@ -51,19 +58,23 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -73,7 +84,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -85,7 +98,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -96,7 +111,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -105,8 +122,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 diff --git a/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result b/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result index 3a9c7be3076..9a8660ba79e 100644 --- a/storage/spider/mysql-test/spider/bg/r/direct_aggregate.result +++ b/storage/spider/mysql-test/spider/bg/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 @@ -75,8 +81,11 @@ 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 diff --git a/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result b/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result index bbdc943601b..760b39e16d5 100644 --- a/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.result +++ b/storage/spider/mysql-test/spider/bg/r/direct_aggregate_part.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 with partition test +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -66,8 +71,11 @@ 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 diff --git a/storage/spider/mysql-test/spider/bg/r/direct_update.result b/storage/spider/mysql-test/spider/bg/r/direct_update.result index 517491253e9..74dae7aec2e 100644 --- a/storage/spider/mysql-test/spider/bg/r/direct_update.result +++ b/storage/spider/mysql-test/spider/bg/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,6 +45,7 @@ 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 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a; @@ -122,8 +128,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/bg/r/direct_update_part.result b/storage/spider/mysql-test/spider/bg/r/direct_update_part.result index bd8f1b89f69..6db7c01f563 100644 --- a/storage/spider/mysql-test/spider/bg/r/direct_update_part.result +++ b/storage/spider/mysql-test/spider/bg/r/direct_update_part.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 with partition test +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -113,8 +118,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/bg/r/function.result b/storage/spider/mysql-test/spider/bg/r/function.result index 764c774514b..c088a8a9541 100644 --- a/storage/spider/mysql-test/spider/bg/r/function.result +++ b/storage/spider/mysql-test/spider/bg/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/bg/r/ha.result b/storage/spider/mysql-test/spider/bg/r/ha.result index 9837faebd87..f8833c229ef 100644 --- a/storage/spider/mysql-test/spider/bg/r/ha.result +++ b/storage/spider/mysql-test/spider/bg/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,12 +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 active standby test create table test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -155,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 @@ -164,6 +180,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 1 @@ -191,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", @@ -206,17 +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 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/bg/r/ha_part.result b/storage/spider/mysql-test/spider/bg/r/ha_part.result index 8c0300ba5a5..315f37298bc 100644 --- a/storage/spider/mysql-test/spider/bg/r/ha_part.result +++ b/storage/spider/mysql-test/spider/bg/r/ha_part.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 with partition test +connection master_1; DROP TABLE IF EXISTS ta_l2; CREATE TABLE ta_l2 ( a INT, @@ -61,6 +70,7 @@ INSERT INTO ta_l2 (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_l2 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 @@ -103,6 +114,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_l2 PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", @@ -120,6 +132,7 @@ 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", @@ -150,6 +163,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') DROP TABLE ta_l2; create table with partition test +connection master_1; DROP TABLE IF EXISTS ta_l2; CREATE TABLE ta_l2 ( a INT, @@ -165,6 +179,7 @@ INSERT INTO ta_l2 (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_l2 ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 a 2008-08-01 10:21:39 @@ -174,6 +189,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 1 @@ -206,6 +222,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_l2 PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", @@ -233,12 +250,19 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/bg/r/spider3_fixes.result b/storage/spider/mysql-test/spider/bg/r/spider3_fixes.result index 98073fa5e54..aa734573a1a 100644 --- a/storage/spider/mysql-test/spider/bg/r/spider3_fixes.result +++ b/storage/spider/mysql-test/spider/bg/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, @@ -182,6 +190,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t2; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -190,11 +199,16 @@ id 3108 5000 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/bg/r/spider3_fixes_part.result b/storage/spider/mysql-test/spider/bg/r/spider3_fixes_part.result index 12f43ef09b2..b793346df4b 100644 --- a/storage/spider/mysql-test/spider/bg/r/spider3_fixes_part.result +++ b/storage/spider/mysql-test/spider/bg/r/spider3_fixes_part.result @@ -10,24 +10,32 @@ 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, @@ -180,6 +188,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t2; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -188,11 +197,16 @@ id 3108 5000 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/bg/r/spider_fixes.result b/storage/spider/mysql-test/spider/bg/r/spider_fixes.result index 3033586821e..f50c9822534 100644 --- a/storage/spider/mysql-test/spider/bg/r/spider_fixes.result +++ b/storage/spider/mysql-test/spider/bg/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 @@ -169,6 +196,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 2.23 index merge +connection master_1; CREATE TABLE ta_l_int ( a INT AUTO_INCREMENT, b INT DEFAULT 10, @@ -182,6 +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; +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 @@ -191,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, @@ -219,7 +249,9 @@ a b c 16 17 18 INSERT INTO ta_l_int (a, b, c) VALUES (0, 2, 3); INSERT INTO ta_l_int (a, b, c) VALUES (18, 2, 3); +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 @@ -243,6 +275,7 @@ a b c 2.25 direct order limit +connection master_1; SHOW STATUS LIKE 'Spider_direct_order_limit'; Variable_name Value Spider_direct_order_limit 2 @@ -257,6 +290,7 @@ 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 ( @@ -271,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, @@ -358,6 +395,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t1; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -371,8 +409,10 @@ id 5439 6216 10000 +connection master_1; read only +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, @@ -404,6 +444,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, @@ -427,6 +468,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), @@ -449,6 +491,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 @@ -515,6 +558,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; @@ -526,6 +570,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 @@ -542,9 +587,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/bg/r/spider_fixes_part.result b/storage/spider/mysql-test/spider/bg/r/spider_fixes_part.result index 104ba971df9..faf0b6efbea 100644 --- a/storage/spider/mysql-test/spider/bg/r/spider_fixes_part.result +++ b/storage/spider/mysql-test/spider/bg/r/spider_fixes_part.result @@ -10,23 +10,29 @@ 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, @@ -43,6 +49,7 @@ INSERT INTO tb_l (a, b, c) VALUES 2.17 partition with sort +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -60,7 +67,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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), @@ -73,7 +82,9 @@ 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, @@ -87,6 +98,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; +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 @@ -96,6 +108,9 @@ a b c 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, @@ -183,6 +198,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t1; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -196,11 +212,16 @@ id 5439 6216 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/bg/r/vp_fixes.result b/storage/spider/mysql-test/spider/bg/r/vp_fixes.result index 15dd29aa4d3..cc0e4105d61 100644 --- a/storage/spider/mysql-test/spider/bg/r/vp_fixes.result +++ b/storage/spider/mysql-test/spider/bg/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/r/basic_sql.result b/storage/spider/mysql-test/spider/r/basic_sql.result index 1e9fe78acea..94a09fc317b 100644 --- a/storage/spider/mysql-test/spider/r/basic_sql.result +++ b/storage/spider/mysql-test/spider/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 ( @@ -76,6 +83,7 @@ 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 @@ -85,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 @@ -99,10 +109,12 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -112,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 @@ -121,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') @@ -131,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') @@ -141,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') @@ -151,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') @@ -161,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') @@ -171,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') @@ -181,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') @@ -191,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') @@ -201,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') @@ -211,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') @@ -218,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') @@ -233,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') @@ -243,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') @@ -258,27 +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 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 @@ -288,61 +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 @@ -352,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 @@ -363,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 @@ -377,66 +438,84 @@ 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 @@ -450,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 @@ -464,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') @@ -476,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 @@ -493,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 @@ -514,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 @@ -531,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 @@ -552,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 @@ -573,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 @@ -594,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 @@ -607,19 +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 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/r/basic_sql_part.result b/storage/spider/mysql-test/spider/r/basic_sql_part.result index 9e1201c17c9..0f4029404a7 100644 --- a/storage/spider/mysql-test/spider/r/basic_sql_part.result +++ b/storage/spider/mysql-test/spider/r/basic_sql_part.result @@ -9,20 +9,25 @@ 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, @@ -38,10 +43,12 @@ INSERT INTO tb_l (a, b, c) VALUES (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 @@ -51,19 +58,23 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -73,7 +84,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -85,7 +98,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -96,7 +111,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 @@ -105,8 +122,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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 diff --git a/storage/spider/mysql-test/spider/r/direct_aggregate.result b/storage/spider/mysql-test/spider/r/direct_aggregate.result index 3a9c7be3076..9a8660ba79e 100644 --- a/storage/spider/mysql-test/spider/r/direct_aggregate.result +++ b/storage/spider/mysql-test/spider/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 @@ -75,8 +81,11 @@ 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 diff --git a/storage/spider/mysql-test/spider/r/direct_aggregate_part.result b/storage/spider/mysql-test/spider/r/direct_aggregate_part.result index bbdc943601b..760b39e16d5 100644 --- a/storage/spider/mysql-test/spider/r/direct_aggregate_part.result +++ b/storage/spider/mysql-test/spider/r/direct_aggregate_part.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 with partition test +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -66,8 +71,11 @@ 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 diff --git a/storage/spider/mysql-test/spider/r/direct_update.result b/storage/spider/mysql-test/spider/r/direct_update.result index 517491253e9..74dae7aec2e 100644 --- a/storage/spider/mysql-test/spider/r/direct_update.result +++ b/storage/spider/mysql-test/spider/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,6 +45,7 @@ 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 SELECT a, b, date_format(c, '%Y-%m-%d %H:%i:%s') FROM ta_l ORDER BY a; @@ -122,8 +128,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/r/direct_update_part.result b/storage/spider/mysql-test/spider/r/direct_update_part.result index bd8f1b89f69..6db7c01f563 100644 --- a/storage/spider/mysql-test/spider/r/direct_update_part.result +++ b/storage/spider/mysql-test/spider/r/direct_update_part.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 with partition test +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -113,8 +118,11 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/r/function.result b/storage/spider/mysql-test/spider/r/function.result index 764c774514b..c088a8a9541 100644 --- a/storage/spider/mysql-test/spider/r/function.result +++ b/storage/spider/mysql-test/spider/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/r/ha.result b/storage/spider/mysql-test/spider/r/ha.result index 9837faebd87..f8833c229ef 100644 --- a/storage/spider/mysql-test/spider/r/ha.result +++ b/storage/spider/mysql-test/spider/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,12 +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 active standby test create table test +connection master_1; DROP TABLE IF EXISTS ta_l; CREATE TABLE ta_l ( a INT, @@ -155,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 @@ -164,6 +180,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 1 @@ -191,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", @@ -206,17 +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 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/r/ha_part.result b/storage/spider/mysql-test/spider/r/ha_part.result index 8c0300ba5a5..315f37298bc 100644 --- a/storage/spider/mysql-test/spider/r/ha_part.result +++ b/storage/spider/mysql-test/spider/r/ha_part.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 with partition test +connection master_1; DROP TABLE IF EXISTS ta_l2; CREATE TABLE ta_l2 ( a INT, @@ -61,6 +70,7 @@ INSERT INTO ta_l2 (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_l2 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 @@ -103,6 +114,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_l2 PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", @@ -120,6 +132,7 @@ 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", @@ -150,6 +163,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') DROP TABLE ta_l2; create table with partition test +connection master_1; DROP TABLE IF EXISTS ta_l2; CREATE TABLE ta_l2 ( a INT, @@ -165,6 +179,7 @@ INSERT INTO ta_l2 (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_l2 ORDER BY a; a b date_format(c, '%Y-%m-%d %H:%i:%s') 1 a 2008-08-01 10:21:39 @@ -174,6 +189,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 1 @@ -206,6 +222,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_l2 PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='srv "s_2_1 s_2_2", tbl "ta_r ta_r3", @@ -233,12 +250,19 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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/r/spider3_fixes.result b/storage/spider/mysql-test/spider/r/spider3_fixes.result index cb62468d3ae..d6aec25bfc1 100644 --- a/storage/spider/mysql-test/spider/r/spider3_fixes.result +++ b/storage/spider/mysql-test/spider/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, @@ -182,6 +190,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t2; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -190,11 +199,16 @@ id 3108 5000 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/r/spider3_fixes_part.result b/storage/spider/mysql-test/spider/r/spider3_fixes_part.result index 12f43ef09b2..b793346df4b 100644 --- a/storage/spider/mysql-test/spider/r/spider3_fixes_part.result +++ b/storage/spider/mysql-test/spider/r/spider3_fixes_part.result @@ -10,24 +10,32 @@ 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, @@ -180,6 +188,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t2; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -188,11 +197,16 @@ id 3108 5000 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/r/spider_fixes.result b/storage/spider/mysql-test/spider/r/spider_fixes.result index 3033586821e..f50c9822534 100644 --- a/storage/spider/mysql-test/spider/r/spider_fixes.result +++ b/storage/spider/mysql-test/spider/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 @@ -169,6 +196,7 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 2.23 index merge +connection master_1; CREATE TABLE ta_l_int ( a INT AUTO_INCREMENT, b INT DEFAULT 10, @@ -182,6 +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; +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 @@ -191,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, @@ -219,7 +249,9 @@ a b c 16 17 18 INSERT INTO ta_l_int (a, b, c) VALUES (0, 2, 3); INSERT INTO ta_l_int (a, b, c) VALUES (18, 2, 3); +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 @@ -243,6 +275,7 @@ a b c 2.25 direct order limit +connection master_1; SHOW STATUS LIKE 'Spider_direct_order_limit'; Variable_name Value Spider_direct_order_limit 2 @@ -257,6 +290,7 @@ 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 ( @@ -271,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, @@ -358,6 +395,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t1; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -371,8 +409,10 @@ id 5439 6216 10000 +connection master_1; read only +connection master_1; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id int(11) NOT NULL, @@ -404,6 +444,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, @@ -427,6 +468,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), @@ -449,6 +491,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 @@ -515,6 +558,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; @@ -526,6 +570,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 @@ -542,9 +587,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/r/spider_fixes_part.result b/storage/spider/mysql-test/spider/r/spider_fixes_part.result index 104ba971df9..faf0b6efbea 100644 --- a/storage/spider/mysql-test/spider/r/spider_fixes_part.result +++ b/storage/spider/mysql-test/spider/r/spider_fixes_part.result @@ -10,23 +10,29 @@ 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, @@ -43,6 +49,7 @@ INSERT INTO tb_l (a, b, c) VALUES 2.17 partition with sort +connection master_1; CREATE TABLE ta_l2 ( a INT, b CHAR(1), @@ -60,7 +67,9 @@ a b date_format(c, '%Y-%m-%d %H:%i:%s') 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), @@ -73,7 +82,9 @@ 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, @@ -87,6 +98,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; +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 @@ -96,6 +108,9 @@ a b c 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, @@ -183,6 +198,7 @@ LAST_INSERT_ID() SELECT MAX(id) FROM t1; MAX(id) 10000 +connection slave1_1; SELECT id FROM t1 ORDER BY id; id 777 @@ -196,11 +212,16 @@ id 5439 6216 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/r/vp_fixes.result b/storage/spider/mysql-test/spider/r/vp_fixes.result index 15dd29aa4d3..cc0e4105d61 100644 --- a/storage/spider/mysql-test/spider/r/vp_fixes.result +++ b/storage/spider/mysql-test/spider/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 |