diff options
Diffstat (limited to 'mysql-test/t')
52 files changed, 836 insertions, 198 deletions
diff --git a/mysql-test/t/bigint.test b/mysql-test/t/bigint.test index cec3e1ca45c..15b35ac7c87 100644 --- a/mysql-test/t/bigint.test +++ b/mysql-test/t/bigint.test @@ -5,12 +5,18 @@ select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296; select 9223372036854775807,-009223372036854775808; select +9999999999999999999,-9999999999999999999; +# +# In 3.23 we have to disable the test of column to bigint as +# this fails on AIX powerpc (the resolution for double is not good enough) +# This will work on 4.0 as we then have internal handling of bigint variables. +# + drop table if exists t1; create table t1 (a bigint unsigned not null, primary key(a)); insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE); select * from t1; select * from t1 where a=18446744073709551615; -select * from t1 where a='18446744073709551615'; +# select * from t1 where a='18446744073709551615'; delete from t1 where a=18446744073709551615; select * from t1; drop table t1; diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index ac2f9cd5879..0beb71aac48 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -105,3 +105,5 @@ drop table if exists t1,t2; create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b)); show create table t1; drop table t1; +create table t1 select if(1,'1','0'), month("2002-08-02"); +drop table t1; diff --git a/mysql-test/t/ctype_latin1_de.test b/mysql-test/t/ctype_latin1_de.test index d39d7f6e5dd..e829005a229 100644 --- a/mysql-test/t/ctype_latin1_de.test +++ b/mysql-test/t/ctype_latin1_de.test @@ -38,9 +38,10 @@ select strcmp('u','öa'),strcmp('u','ö'); # Some other simple tests with the current character set # -create table t1 (a varchar(10), key(a)); +create table t1 (a varchar(10), key(a), fulltext (a)); insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); select * from t1 where a like "abc%"; select * from t1 where a like "test%"; select * from t1 where a like "te_t"; +select * from t1 where match a against ("te*" in boolean mode)+0; drop table t1; diff --git a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test index b850ec5d562..e4503b0cf72 100644 --- a/mysql-test/t/distinct.test +++ b/mysql-test/t/distinct.test @@ -218,3 +218,27 @@ select * from t1 where a >= '1'; select distinct a from t1 order by a desc; select distinct a from t1 where a >= '1' order by a desc; drop table t1; + +# +# Test when using a not previously used column in ORDER BY +# + +CREATE TABLE t1 (email varchar(50), infoID BIGINT, dateentered +DATETIME); +CREATE TABLE t2 (infoID BIGINT, shipcode varchar(10)); + +INSERT INTO t1 (email, infoID, dateentered) VALUES + ('test1@testdomain.com', 1, '2002-07-30 22:56:38'), + ('test1@testdomain.com', 1, '2002-07-27 22:58:16'), + ('test2@testdomain.com', 1, '2002-06-19 15:22:19'), + ('test2@testdomain.com', 2, '2002-06-18 14:23:47'), + ('test3@testdomain.com', 1, '2002-05-19 22:17:32'); + +INSERT INTO t2(infoID, shipcode) VALUES + (1, 'Z001'), + (2, 'R002'); + +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID; +SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; +drop table t1,t2; diff --git a/mysql-test/t/fulltext.test b/mysql-test/t/fulltext.test index 238705c8e3e..65d5381d725 100644 --- a/mysql-test/t/fulltext.test +++ b/mysql-test/t/fulltext.test @@ -35,6 +35,7 @@ select *, MATCH(a,b) AGAINST("collections support" IN BOOLEAN MODE) as x from t1 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); +select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"text search" "now support"' IN BOOLEAN MODE); select * from t1 where MATCH a,b AGAINST ('"text search" -"now support"' IN BOOLEAN MODE); @@ -154,4 +155,8 @@ insert into t1 values (2,"fullaaa fullzzz"); select * from t1 where match b against ('full*' in boolean mode); drop table t1; +CREATE TABLE t1 ( id int(11) NOT NULL auto_increment primary key, mytext text NOT NULL, FULLTEXT KEY mytext (mytext)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,'my small mouse'),(2,'la-la-la'),(3,'It is so funny'),(4,'MySQL Tutorial'); +select 8 from t1; +drop table t1; diff --git a/mysql-test/t/func_if.test b/mysql-test/t/func_if.test index c5cc73ecd1f..85553d1a2fd 100644 --- a/mysql-test/t/func_if.test +++ b/mysql-test/t/func_if.test @@ -28,3 +28,7 @@ create table t1 (num double(12,2)); insert into t1 values (144.54); select sum(if(num is null,0.00,num)) from t1; drop table t1; +create table t1 (x int, y int); +insert into t1 values (0,6),(10,16),(20,26),(30,10),(40,46),(50,56); +select min(if(y -x > 5,y,NULL)), max(if(y - x > 5,y,NULL)) from t1; +drop table t1; diff --git a/mysql-test/t/func_math.test b/mysql-test/t/func_math.test index 5299897d0f0..74e8a5ce092 100644 --- a/mysql-test/t/func_math.test +++ b/mysql-test/t/func_math.test @@ -8,7 +8,10 @@ select truncate(52.64,1),truncate(52.64,2),truncate(52.64,-1),truncate(52.64,-2) select round(5.5),round(-5.5); select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2); select abs(-10), sign(-5), sign(5), sign(0); -select log(exp(10)),exp(log(sqrt(10))*2); +select log(exp(10)),exp(log(sqrt(10))*2),log(-1),log(NULL),log(1,1),log(3,9),log(-1,2),log(NULL,2); +select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); +select log2(8),log2(15),log2(-2),log2(0),log2(NULL); +select log10(100),log10(18),log10(-4),log10(0),log10(NULL); select pow(10,log10(10)),power(2,4); select rand(999999),rand(); select pi(),sin(pi()/2),cos(pi()/2),abs(tan(pi())),cot(1),asin(1),acos(0),atan(1); diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 26272fba03f..daf5587b450 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -37,6 +37,16 @@ select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb') select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; select soundex(''),soundex('he'),soundex('hello all folks'); select md5('hello'); +select sha('abc'); +select sha1('abc'); +select aes_decrypt(aes_encrypt('abc','1'),'1'); +select aes_decrypt(aes_encrypt('abc','1'),1); +select aes_encrypt(NULL,"a"); +select aes_encrypt("a",NULL); +select aes_decrypt(NULL,"a"); +select aes_decrypt("a",NULL); +select aes_decrypt("a","a"); +select aes_decrypt(aes_encrypt("","a"),"a"); select repeat('monty',5),concat('*',space(5),'*'); select reverse('abc'),reverse('abcd'); select rpad('a',4,'1'),rpad('a',4,'12'),rpad('abcd',3,'12'); @@ -51,6 +61,10 @@ select least(1,2,3) | greatest(16,32,8), least(5,4)*1,greatest(-1.0,1.0)*1,least select decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000); select decode(encode("abcdef","monty"),"monty")="abcdef"; +select quote('\'\"\\test'); +select quote(concat('abc\'', '\\cba')); +select quote(1/0), quote('\0\Z'); + # # Wrong usage of functions # diff --git a/mysql-test/t/func_test.test b/mysql-test/t/func_test.test index ec44009b1a6..f5ad2e21c73 100644 --- a/mysql-test/t/func_test.test +++ b/mysql-test/t/func_test.test @@ -15,6 +15,8 @@ select 2 between 1 and 3, "monty" between "max" and "my",2=2 and "monty" between select 'b' between 'a' and 'c', 'B' between 'a' and 'c'; select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2,1.0); select -1.49 or -1.49,0.6 or 0.6; +select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; # # Wrong usage of functions diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test index 171b5510227..072a1830f57 100644 --- a/mysql-test/t/group_by.test +++ b/mysql-test/t/group_by.test @@ -284,18 +284,31 @@ INSERT INTO t1 values ('hij','klm'); SELECT CONCAT(a, b),count(*) FROM t1 GROUP BY 1; DROP TABLE t1; drop table if exists t1; + +# +# Test problem with ORDER BY on a SUM() column +# + create table t1 (One int unsigned, Two int unsigned, Three int unsigned, Four int unsigned); -insert into t1 values (1,2,1,4); -insert into t1 values (1,2,2,4); -insert into t1 values (1,2,3,4); -insert into t1 values (1,2,4,4); -insert into t1 values (1,1,1,4); -insert into t1 values (1,1,2,4); -insert into t1 values (1,1,3,4); -insert into t1 values (1,1,4,4); -insert into t1 values (1,3,1,4); -insert into t1 values (1,3,2,4); -insert into t1 values (1,3,3,4); -insert into t1 values (1,3,4,4); +insert into t1 values (1,2,1,4),(1,2,2,4),(1,2,3,4),(1,2,4,4),(1,1,1,4),(1,1,2,4),(1,1,3,4),(1,1,4,4),(1,3,1,4),(1,3,2,4),(1,3,3,4),(1,3,4,4); select One, Two, sum(Four) from t1 group by One,Two; -drop table if exists t1; +drop table t1; + +create table t1 (id integer primary key not null auto_increment, gender char(1)); +insert into t1 values (NULL, 'M'), (NULL, 'F'),(NULL, 'F'),(NULL, 'F'),(NULL, 'M'); +create table t2 (user_id integer not null, date date); +insert into t2 values (1, '2002-06-09'),(2, '2002-06-09'),(1, '2002-06-09'),(3, '2002-06-09'),(4, '2002-06-09'),(4, '2002-06-09'); +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender; +select u.gender as gender, count(distinct u.id) as dist_count, (count(distinct u.id)/5*100) as percentage from t1 u, t2 l where l.user_id = u.id group by u.gender order by percentage; +drop table t1,t2; + +# +# The GROUP BY returned rows in wrong order in 3.23.51 +# + +CREATE TABLE t1 (ID1 int, ID2 int, ID int NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID +)); +insert into t1 values (1,244,NULL),(2,243,NULL),(134,223,NULL),(185,186,NULL); +select S.ID as xID, S.ID1 as xID1 from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2; +select S.ID as xID, S.ID1 as xID1, repeat('*',count(distinct yS.ID)) as Level from t1 as S left join t1 as yS on S.ID1 between yS.ID1 and yS.ID2 group by xID order by xID1; +drop table t1; diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index 1b7ee99e15a..e6d57899082 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -578,17 +578,11 @@ explain select a,b,c from t1; drop table t1; # -# Check describe & handler +# Check describe # create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; ---error 1031 -handler t1 open t1; ---error 1109 -handler t1 read t first; ---error 1109 -handler t1 close; drop table t1; # @@ -638,3 +632,31 @@ select * from t1; select * from t2; select * from t2; drop table t1,t2; + +# +# A simple test with some isolation levels +# TODO: Make this into a test using replication to really test how +# this works. +# + +create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; +SELECT @@tx_isolation,@@global.tx_isolation; +insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); +select id, code, name from t1 order by id; +COMMIT; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha'); +select id, code, name from t1 order by id; +COMMIT; + +BEGIN; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt'); +select id, code, name from t1 order by id; +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/t/innodb_handler.test b/mysql-test/t/innodb_handler.test new file mode 100644 index 00000000000..6b85be9c7ab --- /dev/null +++ b/mysql-test/t/innodb_handler.test @@ -0,0 +1,76 @@ +-- source include/have_innodb.inc + +# +# test of HANDLER ... +# + +drop table if exists t1; +create table t1 (a int, b char(10), key a(a), key b(a,b)) type=innodb; +insert into t1 values +(17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"), +(14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"), +(20,"ggg"),(21,"hhh"),(22,"iii"); +handler t1 open as t2; +handler t2 read a first; +handler t2 read a next; +handler t2 read a next; +handler t2 read a prev; +handler t2 read a last; +handler t2 read a prev; +handler t2 read a prev; + +handler t2 read a first; +handler t2 read a prev; + +handler t2 read a last; +handler t2 read a prev; +handler t2 read a next; +handler t2 read a next; + +handler t2 read a=(15); +handler t2 read a=(16); + +--error 1070 +handler t2 read a=(19,"fff"); + +handler t2 read b=(19,"fff"); +handler t2 read b=(19,"yyy"); +handler t2 read b=(19); + +--error 1109 +handler t1 read a last; + +handler t2 read a=(11); +handler t2 read a>=(11); + +handler t2 read a=(18); +handler t2 read a>=(18); +handler t2 read a>(18); +handler t2 read a<=(18); +handler t2 read a<(18); + +handler t2 read a first limit 5; +handler t2 read a next limit 3; +handler t2 read a prev limit 10; + +handler t2 read a>=(16) limit 4; +handler t2 read a>=(16) limit 2,2; +handler t2 read a last limit 3; + +handler t2 read a=(19); +handler t2 read a=(19) where b="yyy"; + +handler t2 read first; +handler t2 read next; +# +# We alter the table even if it's still in use by to test the Innodb +# delayed-drop code. This will generate a warning in the master.err log. +# +alter table t1 type=innodb; +handler t2 read next; +--error 1064 +handler t2 read last; + +handler t2 close; +drop table if exists t1; + diff --git a/mysql-test/t/insert.test b/mysql-test/t/insert.test index 270b1cd4c79..2c912d94c70 100644 --- a/mysql-test/t/insert.test +++ b/mysql-test/t/insert.test @@ -22,3 +22,30 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; + +# +# Test insert syntax +# + +create table t1 (a int not null auto_increment, primary key (a), t timestamp, c char(10) default "hello", i int); +insert into t1 values (default,default,default,default), (default,default,default,default), (4,0,"a",5),(default,default,default,default); +select a,t>0,c,i from t1; +truncate table t1; +insert into t1 set a=default,t=default,c=default; +insert into t1 set a=default,t=default,c=default,i=default; +insert into t1 set a=4,t=0,c="a",i=5; +insert into t1 set a=5,t=0,c="a",i=null; +insert into t1 set a=default,t=default,c=default,i=default; +select a,t>0,c,i from t1; +drop table t1; + +# +# Test of mysqld crash with fully qualified column names +# + +drop database if exists foo; +create database foo; +use foo; +create table t1 (c int); +insert into foo.t1 set foo.t1.c = '1'; +drop database foo; diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test index 6fc975a1cb3..1996c793880 100644 --- a/mysql-test/t/key.test +++ b/mysql-test/t/key.test @@ -168,3 +168,23 @@ drop table t1; create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique); show keys from t1; drop table t1; + +# +# Problem with UNIQUE() with NULL parts and auto increment +# + +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT, +UNIQUE (c,i)); +INSERT INTO t1 (c) VALUES (NULL),(NULL); +SELECT * FROM t1; +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +drop table t1; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 5bd78769a05..86e787db0a3 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -129,18 +129,18 @@ drop table t3,t1,t2; drop table if exists t6, t5, t4, t3, t2, t1; # first testing of common stuff with new parameters -create table t1 (a int not null, b int not null, key(a,b)); -create table t2 (a int not null, b int not null, key(a,b)); +create table t1 (a int not null, b int not null auto_increment, primary key(a,b)); +create table t2 (a int not null, b int not null auto_increment, primary key(a,b)); create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO; create table t4 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=NO; -create table t5 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; -create table t6 (a int not null, b int not null, key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; +create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; +create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; show create table t3; show create table t4; show create table t5; show create table t6; -insert into t1 values (1,1),(1,2),(1,3),(1,4); -insert into t2 values (2,1),(2,2),(2,3),(2,4); +insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL); +insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL); select * from t3 order by b,a limit 3; select * from t4 order by b,a limit 3; select * from t5 order by b,a limit 3,3; @@ -167,4 +167,21 @@ select * from t2 order by a,b; select * from t3 order by a,b; select * from t4 order by a,b; select * from t5 order by a,b; +# auto_increment +select 1; +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +select * from t2 order by a,b; +select * from t5 order by a,b; +select * from t6 order by a,b; drop table if exists t6, t5, t4, t3, t2, t1; + +CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1,1), (2,1); +CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,2), (2,2); +CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b)) TYPE=MRG_MyISAM UNION=(t1,t2); +select max(b) from t where a = 2; +select max(b) from t1 where a = 2; +drop table if exists t,t1,t2; diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test index c16189168d2..205dbb10ccf 100644 --- a/mysql-test/t/multi_update.test +++ b/mysql-test/t/multi_update.test @@ -62,3 +62,81 @@ select count(*) from t2 where id2; select count(*) from t3 where id3; drop table t1,t2,t3; +create table t1(id1 int not null primary key, t varchar(100)) pack_keys = 1; +create table t2(id2 int not null, t varchar(100), index(id2)) pack_keys = 1; +disable_query_log; +let $1 = 1000; +while ($1) + { + let $2 = 5; + eval insert into t1 values ($1,'aaaaaaaaaaaaaaaaaaaa'); + while ($2) + { + eval insert into t2(id2,t) values ($1,'bbbbbbbbbbbbbbbbb'); + dec $2; + } + dec $1; + } +enable_query_log; +delete t1 from t1,t2 where t1.id1 = t2.id2 and t1.id1 > 500; +drop table t1,t2; +DROP TABLE IF EXISTS a,b,c; +CREATE TABLE a ( + id int(11) NOT NULL default '0', + name varchar(10) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO a VALUES (1,'aaa'),(2,'aaa'),(3,'aaa'); +CREATE TABLE b ( + id int(11) NOT NULL default '0', + name varchar(10) default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO b VALUES (2,'bbb'),(3,'bbb'),(4,'bbb'); +CREATE TABLE c ( + id int(11) NOT NULL default '0', + mydate datetime default NULL, + PRIMARY KEY (id) +) TYPE=MyISAM; +INSERT INTO c VALUES (1,'2002-02-04 00:00:00'),(3,'2002-05-12 00:00:00'),(5,'2002-05-12 00:00:00'),(6,'2002-06-22 +00:00:00'),(7,'2002-07-22 00:00:00'); +delete a,b,c from a,b,c +where to_days(now())-to_days(c.mydate)>=30 +and c.id=a.id and c.id=b.id; +select * from c; +DROP TABLE IF EXISTS a,b,c; +drop table if exists parent, child; +CREATE TABLE IF NOT EXISTS `parent` ( + `id` int(11) NOT NULL auto_increment, + `tst` text, + `tst1` text, + PRIMARY KEY (`id`) +) TYPE=MyISAM; + +CREATE TABLE IF NOT EXISTS `child` ( + `ID` int(11) NOT NULL auto_increment, + `ParId` int(11) default NULL, + `tst` text, + `tst1` text, + PRIMARY KEY (`ID`), + KEY `IX_ParId_child` (`ParId`), + FOREIGN KEY (`ParId`) REFERENCES `test.parent` (`id`) +) TYPE=MyISAM; + +INSERT INTO parent(tst,tst1) +VALUES("MySQL","MySQL AB"), ("MSSQL","Microsoft"), ("ORACLE","ORACLE"); + +INSERT INTO child(ParId) +VALUES(1), (2), (3); + +select * from child; + +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; + +select * from child; + + +drop table parent, child;
\ No newline at end of file diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 42d1dd6d553..2479a68fc09 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -66,3 +66,30 @@ explain select a,b from t1 order by b; explain select a,b from t1; explain select a,b,c from t1; drop table t1; + +# +# Test of REPAIR that once failed +# +CREATE TABLE `t1` ( + `post_id` mediumint(8) unsigned NOT NULL auto_increment, + `topic_id` mediumint(8) unsigned NOT NULL default '0', + `post_time` datetime NOT NULL default '0000-00-00 00:00:00', + `post_text` text NOT NULL, + `icon_url` varchar(10) NOT NULL default '', + `sign` tinyint(1) unsigned NOT NULL default '0', + `post_edit` varchar(150) NOT NULL default '', + `poster_login` varchar(35) NOT NULL default '', + `ip` varchar(15) NOT NULL default '', + PRIMARY KEY (`post_id`), + KEY `post_time` (`post_time`), + KEY `ip` (`ip`), + KEY `poster_login` (`poster_login`), + KEY `topic_id` (`topic_id`), + FULLTEXT KEY `post_text` (`post_text`) +) TYPE=MyISAM; + +INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'); + +REPAIR TABLE t1; +CHECK TABLE t1; +drop table t1; diff --git a/mysql-test/t/olap.test b/mysql-test/t/olap.test new file mode 100644 index 00000000000..8fe547db615 --- /dev/null +++ b/mysql-test/t/olap.test @@ -0,0 +1,27 @@ +drop table if exists sales; +create table sales ( product varchar(32), country varchar(32), year int, profit int); +insert into sales values ( 'Computer', 'India',2000, 1200), +( 'TV', 'United States', 1999, 150), +( 'Calculator', 'United States', 1999,50), +( 'Computer', 'United States', 1999,1500), +( 'Computer', 'United States', 2000,1500), +( 'TV', 'United States', 2000, 150), +( 'TV', 'India', 2000, 100), +( 'TV', 'India', 2000, 100), +( 'Calculator', 'United States', 2000,75), +( 'Calculator', 'India', 2000,75), +( 'TV', 'India', 1999, 100), +( 'Computer', 'India', 1999,1200), +( 'Computer', 'United States', 2000,1500), +( 'Calculator', 'United States', 2000,75); +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with cube; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +explain select product, country , year, sum(profit) from sales group by product, country, year with rollup; +--error 1235 +select product, country , year, sum(profit) from sales group by product, country, year with cube union all select product, country , year, sum(profit) from sales group by product, country, year with rollup; +drop table sales; diff --git a/mysql-test/t/query_cache.test b/mysql-test/t/query_cache.test index a3227a5db1d..5dc6d1047f4 100644 --- a/mysql-test/t/query_cache.test +++ b/mysql-test/t/query_cache.test @@ -139,17 +139,17 @@ drop table t1, t2, t3, t11, t21; # # SELECT SQL_CACHE ... # -set sql_query_cache_type=demand; +set query_cache_type=demand; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; show status like "Qcache_queries_in_cache"; select sql_cache * from t1; -set sql_query_cache_type=2; +set query_cache_type=2; select sql_cache * from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; -set sql_query_cache_type=on; +set query_cache_type=on; # # RESET QUERY CACHE # @@ -336,3 +336,34 @@ select a from t1; show status like "Qcache_queries_in_cache"; drop table t1; show status like "Qcache_queries_in_cache"; + +# +# Test of query cache resizing +# +set GLOBAL query_cache_size=1000; +show global variables like "query_cache_size"; +set GLOBAL query_cache_size=1100; +set GLOBAL query_cache_size=1200; +set GLOBAL query_cache_size=1300; +set GLOBAL query_cache_size=1400; +set GLOBAL query_cache_size=1500; +set GLOBAL query_cache_size=1600; +set GLOBAL query_cache_size=1700; +set GLOBAL query_cache_size=1800; +set GLOBAL query_cache_size=1900; +show global variables like "query_cache_size"; + +# +# Temporary tables +# +set GLOBAL query_cache_size=1048576; +create table t1 (i int not null); +create table t2 (i int not null); +select * from t1; +show status like "Qcache_queries_in_cache"; +create temporary table t3 (i int not null); +select * from t2; +show status like "Qcache_queries_in_cache"; +select * from t3; +show status like "Qcache_queries_in_cache"; +drop table t1, t2, t3; diff --git a/mysql-test/t/rpl000001.test b/mysql-test/t/rpl000001.test index a5496d505f0..0f195c65fb0 100644 --- a/mysql-test/t/rpl000001.test +++ b/mysql-test/t/rpl000001.test @@ -1,21 +1,36 @@ -eval_result; source include/master-slave.inc; -connection master; -use test; -drop table if exists t1,t3; +drop table if exists t1,t2,t3; create table t1 (word char(20) not null); load data infile '../../std_data/words.dat' into table t1; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR eval load data local infile '$MYSQL_TEST_DIR/std_data/words.dat' into table t1; select * from t1; + +# +# Test slave with wrong password +# +save_master_pos; +connection slave; +sync_with_master; +slave stop; +connection master; set password for root@"localhost" = password('foo'); +connection slave; +slave start; +connection master; +# +# Give slave time to do at last one failed connect retry +# This one must be short so that the slave will not stop retrying +real_sleep 2; set password for root@"localhost" = password(''); +# Give slave time to connect (will retry every second) +sleep 2; + create table t3(n int); insert into t3 values(1),(2); save_master_pos; connection slave; sync_with_master; -use test; select * from t3; select sum(length(word)) from t1; connection master; @@ -32,7 +47,6 @@ slave stop; reset slave; connection master; -drop table if exists t1,t2; create table t1(n int); #we want the log to exceed 16K to test deal with the log that is bigger than #IO_SIZE @@ -45,12 +59,12 @@ while ($1) } enable_query_log; -#try to cause a large relay log lag on the slave +# Try to cause a large relay log lag on the slave connection slave; select get_lock("hold_slave",10); slave start; #hope this is long enough for I/O thread to fetch over 16K relay log data -sleep 1; +sleep 3; select release_lock("hold_slave"); unlock tables; @@ -59,24 +73,24 @@ create table t2(id int); insert into t2 values(connection_id()); save_master_pos; - connection master1; -#avoid generating result -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; +# Avoid generating result +create temporary table t3(n int); +insert into t3 select get_lock('crash_lock%20C', 1) from t2; connection master; send update t1 set n = n + get_lock('crash_lock%20C', 2); connection master1; -sleep 2; +sleep 3; select (@id := id) - id from t2; kill @id; +# We don't drop t3 as this is a temporary table drop table t2; connection master; --error 1053; reap; connection slave; -sync_with_master ; +sync_with_master; #give the slave a chance to exit wait_for_slave_to_stop; @@ -85,20 +99,18 @@ wait_for_slave_to_stop; # --replace_result 9306 9999 3334 9999 3335 9999 # show slave status; -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; select count(*) from t1; connection master1; drop table t1; create table t1 (n int); insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) +insert into mysql.user (Host, User, Password) VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); select select_priv,user from mysql.user where user = 'blafasel2'; -update user set Select_priv = "Y" where User="blafasel2"; +update mysql.user set Select_priv = "Y" where User="blafasel2"; select select_priv,user from mysql.user where user = 'blafasel2'; -use test; save_master_pos; connection slave; sync_with_master; @@ -109,5 +121,3 @@ drop table t1; save_master_pos; connection slave; sync_with_master; - - diff --git a/mysql-test/t/rpl000002.test b/mysql-test/t/rpl000002.test index 420ae27a337..caf0b4ef6d8 100644 --- a/mysql-test/t/rpl000002.test +++ b/mysql-test/t/rpl000002.test @@ -1,17 +1,14 @@ source include/master-slave.inc; -eval_result; -connection master; -use test; drop table if exists t1; create table t1 (n int auto_increment primary key); set insert_id = 2000; insert into t1 values (NULL),(NULL),(NULL); save_master_pos; connection slave; -use test; sync_with_master; select * from t1; connection master; +--replace_result $SLAVE_MYPORT 9999 show slave hosts; drop table t1; save_master_pos; diff --git a/mysql-test/t/rpl000003.test b/mysql-test/t/rpl000003.test index c4b120d31b0..f994ed94371 100644 --- a/mysql-test/t/rpl000003.test +++ b/mysql-test/t/rpl000003.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; drop table if exists t1; create table t1(n int primary key); !insert into t1 values (1),(2),(2); diff --git a/mysql-test/t/rpl000004.test b/mysql-test/t/rpl000004.test index 5b2c41293a2..705e0d51b7b 100644 --- a/mysql-test/t/rpl000004.test +++ b/mysql-test/t/rpl000004.test @@ -1,6 +1,4 @@ source include/master-slave.inc; -connection master; -use test; set SQL_LOG_BIN=0; drop table if exists t1; create table t1 (word char(20) not null, index(word)); @@ -10,7 +8,6 @@ create table t2 (word char(20) not null); load data infile '../../std_data/words.dat' into table t2; create table t3 (word char(20) not null primary key); connection slave; -use test; drop table if exists t1; load table t1 from master; drop table if exists t2; diff --git a/mysql-test/t/rpl000005.test b/mysql-test/t/rpl000005.test index 92d954a6182..1cfea242b69 100644 --- a/mysql-test/t/rpl000005.test +++ b/mysql-test/t/rpl000005.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; drop table if exists t1; CREATE TABLE t1 (name varchar(64), age smallint(3)); INSERT INTO t1 SET name='Andy', age=31; diff --git a/mysql-test/t/rpl000006.test b/mysql-test/t/rpl000006.test index 495f34bd5e7..ad8622c2a81 100644 --- a/mysql-test/t/rpl000006.test +++ b/mysql-test/t/rpl000006.test @@ -2,7 +2,6 @@ # Test forced timestamp # source include/master-slave.inc; -connection master; # Don't log table creating to the slave as we want to test LOAD TABLE set SQL_LOG_BIN=0,timestamp=200006; diff --git a/mysql-test/t/rpl000007.test b/mysql-test/t/rpl000007.test index 4425a5725e8..8ff1e1782cc 100644 --- a/mysql-test/t/rpl000007.test +++ b/mysql-test/t/rpl000007.test @@ -2,12 +2,10 @@ #the ones on bar source include/master-slave.inc; connection slave; -use test; drop table if exists foo; create table foo (n int); insert into foo values(4); connection master; -use test; drop table if exists foo; create table foo (s char(20)); load data infile '../../std_data/words.dat' into table foo; diff --git a/mysql-test/t/rpl000009.test b/mysql-test/t/rpl000009.test index 208e6f0b037..3d0827718ec 100644 --- a/mysql-test/t/rpl000009.test +++ b/mysql-test/t/rpl000009.test @@ -1,7 +1,7 @@ -#this one assumes we are ignoring updates on tables in database foo, but doing -#the ones in database bar +# This one assumes we are ignoring updates on tables in database foo, but doing +# the ones in database bar + source include/master-slave.inc; -connection master; drop database if exists foo; create database foo; drop database if exists bar; @@ -32,9 +32,10 @@ sync_with_master; drop database if exists bar; drop database if exists foo; -#now let's test load data from master +# Now let's test load data from master + +# First create some databases and tables on the master -#first create some databases and tables on the master connection master; set sql_log_bin = 0; create database foo; @@ -55,11 +56,11 @@ save_master_pos; connection slave; sync_with_master; -#this should show that the slave is empty at this point +# This should show that the slave is empty at this point show databases; load data from master; -#now let's check if we have the right tables and the right data in them +# Now let's check if we have the right tables and the right data in them show databases; use foo; show tables; @@ -68,7 +69,7 @@ show tables; select * from bar.t1; select * from bar.t2; -#now let's see if replication works +# Now let's see if replication works connection master; insert into bar.t1 values (4, 'four bar'); save_master_pos; @@ -76,7 +77,7 @@ connection slave; sync_with_master; select * from bar.t1; -#now time for cleanup +# Now time for cleanup connection master; drop database bar; drop database foo; diff --git a/mysql-test/t/rpl000011.test b/mysql-test/t/rpl000011.test index 7d99222b3c7..d75937e3f81 100644 --- a/mysql-test/t/rpl000011.test +++ b/mysql-test/t/rpl000011.test @@ -1,6 +1,4 @@ source include/master-slave.inc; -connection master; -use test; drop table if exists t1; create table t1 (n int); insert into t1 values(1); diff --git a/mysql-test/t/rpl000013.test b/mysql-test/t/rpl000013.test index a190d5324ae..14619796e01 100644 --- a/mysql-test/t/rpl000013.test +++ b/mysql-test/t/rpl000013.test @@ -1,5 +1,4 @@ source include/master-slave.inc; -connection master; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl000017.test b/mysql-test/t/rpl000017.test index 011b6507e49..f346ca39ca0 100644 --- a/mysql-test/t/rpl000017.test +++ b/mysql-test/t/rpl000017.test @@ -2,8 +2,8 @@ connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); connection master; reset master; -grant file on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; -grant file on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@localhost identified by 'aaaaaaaaaaaaaaab'; +grant replication slave on *.* to replicate@127.0.0.1 identified by 'aaaaaaaaaaaaaaab'; connection slave; slave start; connection master; diff --git a/mysql-test/t/rpl000018.test b/mysql-test/t/rpl000018.test index 30d601bb375..291b482b912 100644 --- a/mysql-test/t/rpl000018.test +++ b/mysql-test/t/rpl000018.test @@ -1,3 +1,8 @@ +# +# Running test with abort-slave-event-count=1 +# This will force slave to reconnect after every event +# + require_manager; connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); diff --git a/mysql-test/t/rpl_alter.test b/mysql-test/t/rpl_alter.test new file mode 100644 index 00000000000..710dd2d09d6 --- /dev/null +++ b/mysql-test/t/rpl_alter.test @@ -0,0 +1,19 @@ +source include/master-slave.inc; +drop database if exists d1; +create database d1; +create table d1.t1 ( n int); +alter table d1.t1 add m int; +insert into d1.t1 values (1,2); +create table d1.t2 (n int); +insert into d1.t2 values (45); +rename table d1.t2 to d1.t3, d1.t1 to d1.t2; +save_master_pos; +connection slave; +sync_with_master; +select * from d1.t2; +select * from d1.t3; +connection master; +drop database d1; +save_master_pos; +connection slave; +sync_with_master; diff --git a/mysql-test/t/rpl_compat.test b/mysql-test/t/rpl_compat.test deleted file mode 100644 index c9455c1e2d2..00000000000 --- a/mysql-test/t/rpl_compat.test +++ /dev/null @@ -1,86 +0,0 @@ -eval_result; -source include/master-slave.inc; -connection master; -require_version 3.23; -use test; -drop table if exists t1,t3; -create table t1 (word char(20) not null); -load data infile '../../std_data/words.dat' into table t1; -select * from t1; -set password for root@"localhost" = password('foo'); -set password for root@"localhost" = password(''); -create table t3(n int); -insert into t3 values(1),(2); -save_master_pos; -connection slave; -sync_with_master; -use test; -select * from t3; -select sum(length(word)) from t1; -connection master; -drop table t1,t3; -save_master_pos; -connection slave; -sync_with_master; - -#test handling of aborted connection in the middle of update -connection master; -reset master; -connection slave; -slave stop; -reset slave; - -connection master; -create table t1(n int); -insert into t1 values (1),(2),(3); -create table t2(id int); -insert into t2 values(connection_id()); -save_master_pos; - -connection master1; -#avoid generating result -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; - -connection master; -send update t1 set n = n + get_lock('crash_lock%20C', 2); -connection master1; -sleep 2; -select (@id := id) - id from t2; -kill @id; -drop table t2; -connection master; ---error 1053; -reap; -connection slave; -slave start; -sync_with_master ; -#now slave will hit an error -wait_for_slave_to_stop; - -set sql_slave_skip_counter=1; -slave start; -select count(*) from t1; -connection master1; -drop table t1; -create table t1 (n int); -insert into t1 values(3456); -use mysql; -insert into user (Host, User, Password) - VALUES ("10.10.10.%", "blafasel2", password("blafasel2")); -select select_priv,user from mysql.user where user = 'blafasel2'; -update user set Select_priv = "Y" where User="blafasel2"; -select select_priv,user from mysql.user where user = 'blafasel2'; -use test; -save_master_pos; -connection slave; -sync_with_master; -select n from t1; -select select_priv,user from mysql.user where user = 'blafasel2'; -connection master1; -drop table t1; -save_master_pos; -connection slave; -sync_with_master; - - diff --git a/mysql-test/t/rpl_empty_master_crash.test b/mysql-test/t/rpl_empty_master_crash.test index 158b59169ee..afa76ce10bb 100644 --- a/mysql-test/t/rpl_empty_master_crash.test +++ b/mysql-test/t/rpl_empty_master_crash.test @@ -1,7 +1,11 @@ source include/master-slave.inc; -connection master; -use test; drop table if exists t1; show slave status; +# +# Load table should not succeed on the master as this is not a slave +# --error 1218 load table t1 from master; +connection slave; +--error 1188 +load table t1 from master; diff --git a/mysql-test/t/rpl_get_lock.test b/mysql-test/t/rpl_get_lock.test index 3fe45eecf46..e93268e6074 100644 --- a/mysql-test/t/rpl_get_lock.test +++ b/mysql-test/t/rpl_get_lock.test @@ -1,5 +1,5 @@ source include/master-slave.inc; -connection master; + create table t1(n int); insert into t1 values(get_lock("lock",2)); dirty_close master; @@ -21,6 +21,10 @@ connection slave; sync_with_master; select get_lock("lock",3); select * from t1; +select is_free_lock("lock"); +# Check lock functions +select is_free_lock("lock2"); +select is_free_lock(NULL); connection master1; drop table t1; save_master_pos; diff --git a/mysql-test/t/rpl000014.test b/mysql-test/t/rpl_log_pos.test index df15fa6e600..f585fa233c5 100644 --- a/mysql-test/t/rpl000014.test +++ b/mysql-test/t/rpl_log_pos.test @@ -1,5 +1,7 @@ +# +# Testing of setting slave to wrong log position with master_log_pos +# source include/master-slave.inc; -connection master; show master status; save_master_pos; connection slave; @@ -7,30 +9,35 @@ sync_with_master; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=73; +sleep 5; slave stop; change master to master_log_pos=73; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; slave start; +sleep 5; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; change master to master_log_pos=173; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT +slave start; +sleep 2; show slave status; connection master; show master status; -create table if not exists foo(n int); -drop table if exists foo; -create table foo (n int); -insert into foo values (1),(2),(3); +create table if not exists t1 (n int); +drop table if exists t1; +create table t1 (n int); +insert into t1 values (1),(2),(3); save_master_pos; connection slave; change master to master_log_pos=79; +slave start; sync_with_master; -select * from foo; +select * from t1; connection master; -drop table foo; +drop table t1; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl_magic.test b/mysql-test/t/rpl_magic.test index 376edc301d7..c93add93d9d 100644 --- a/mysql-test/t/rpl_magic.test +++ b/mysql-test/t/rpl_magic.test @@ -1,7 +1,6 @@ source include/master-slave.inc; #first, make sure the slave has had enough time to register -connection master; save_master_pos; connection slave; sync_with_master; diff --git a/mysql-test/t/rpl_mystery22.test b/mysql-test/t/rpl_mystery22.test index e987074697b..371c5ba21ed 100644 --- a/mysql-test/t/rpl_mystery22.test +++ b/mysql-test/t/rpl_mystery22.test @@ -1,8 +1,7 @@ # test case to make slave thread get ahead by 22 bytes -drop table if exists t1; source include/master-slave.inc; -connection master; + # first, cause a duplicate key problem on the slave create table t1(n int auto_increment primary key); save_master_pos; diff --git a/mysql-test/t/rpl000016-master.opt b/mysql-test/t/rpl_rotate_logs-master.opt index f27601e0d7d..f27601e0d7d 100644 --- a/mysql-test/t/rpl000016-master.opt +++ b/mysql-test/t/rpl_rotate_logs-master.opt diff --git a/mysql-test/t/rpl000016-slave-master-info.opt b/mysql-test/t/rpl_rotate_logs-slave-master-info.opt index 80190bf6d29..80190bf6d29 100644 --- a/mysql-test/t/rpl000016-slave-master-info.opt +++ b/mysql-test/t/rpl_rotate_logs-slave-master-info.opt diff --git a/mysql-test/t/rpl000016-slave.sh b/mysql-test/t/rpl_rotate_logs-slave.sh index 9259f593e54..9259f593e54 100755 --- a/mysql-test/t/rpl000016-slave.sh +++ b/mysql-test/t/rpl_rotate_logs-slave.sh diff --git a/mysql-test/t/rpl000016.test b/mysql-test/t/rpl_rotate_logs.test index 7559b2d3515..cea2f9008dc 100644 --- a/mysql-test/t/rpl000016.test +++ b/mysql-test/t/rpl_rotate_logs.test @@ -1,11 +1,24 @@ +# +# Test are run with max_binlog_size=2048 to force automatic rotation of the +# binary log +# Tests done: +# - Check that slaves reports correct failures if master.info has strange +# modes/information +# - Automatic binary log rotation +# - Ensure that temporary tables works over flush logs and binary log +# changes +# - Test creating a duplicate key error and recover from it +# connect (master,localhost,root,,test,0,master.sock); connect (slave,localhost,root,,test,0,slave.sock); system cat /dev/null > var/slave-data/master.info; system chmod 000 var/slave-data/master.info; connection slave; -!slave start; +--error 1201 +slave start; system chmod 600 var/slave-data/master.info; -!slave start; +--error 1201 +slave start; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT !eval change master to master_host='127.0.0.1',master_port=$MASTER_MYPORT, master_user='root'; @@ -18,7 +31,14 @@ reset master; connection slave; slave start; connection master; -drop table if exists t1; + +drop table if exists t1, t2, t3, t4; + +# +# Test FLUSH LOGS +# +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables"); create table t1 (s text); insert into t1 values('Could not break slave'),('Tried hard'); save_master_pos; @@ -32,15 +52,24 @@ flush logs; drop table if exists t2; create table t2(m int not null auto_increment primary key); insert into t2 values (34),(67),(123); -save_master_pos; flush logs; show master logs; +create table t3 select * from temp_table; -#now lets make some duplicate key mess and see if we can recover from it - -#first insert a value on the slave +save_master_pos; connection slave; sync_with_master; + +select * from t3; +connection master; +drop table temp_table, t3; + +# +# Now lets make some duplicate key mess and see if we can recover from it +# + +# First insert a value on the slave +connection slave; insert into t2 values(1234); #same value on the master @@ -54,7 +83,7 @@ sync_with_master; wait_for_slave_to_stop; #restart slave skipping one event -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; connection master; @@ -73,7 +102,14 @@ sync_with_master; --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; select * from t2; + +# +# Test forcing the replication log to rotate +# + connection master; +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables part 2"); let $1=100; drop table if exists t3; create table t3 (n int); @@ -85,13 +121,16 @@ while ($1) dec $1; } enable_query_log; +create table t4 select * from temp_table; show master logs; show master status; save_master_pos; connection slave; -slave stop; -slave start; +#slave stop; +#slave start; sync_with_master; +select * from t4; + --replace_result 3306 MASTER_PORT 9306 MASTER_PORT 3334 MASTER_PORT 3336 MASTER_PORT show slave status; # because of concurrent insert, the table may not be up to date diff --git a/mysql-test/t/rpl_skip_error.test b/mysql-test/t/rpl_skip_error.test index 3303b45c1ca..d43c59ef024 100644 --- a/mysql-test/t/rpl_skip_error.test +++ b/mysql-test/t/rpl_skip_error.test @@ -1,6 +1,6 @@ source include/master-slave.inc; -connection master; drop table if exists t1; + create table t1 (n int not null primary key); save_master_pos; connection slave; diff --git a/mysql-test/t/rpl_sporadic_master.test b/mysql-test/t/rpl_sporadic_master.test index 0487f868436..e59b93b4475 100644 --- a/mysql-test/t/rpl_sporadic_master.test +++ b/mysql-test/t/rpl_sporadic_master.test @@ -2,8 +2,7 @@ # COM_BINLOG_DUMP and additionally limits the number of events per dump source include/master-slave.inc; -connection master; -drop table if exists t1,t2; + create table t2(n int); create table t1(n int not null auto_increment primary key); insert into t1 values (NULL),(NULL); diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test index ab4251fe536..4dd833616d5 100644 --- a/mysql-test/t/select.test +++ b/mysql-test/t/select.test @@ -1712,6 +1712,8 @@ show full columns from t2 from test like 'f%'; show full columns from t2 from test like 's%'; show keys from t2; +drop table t4, t3, t2, t1; + # # Test of DO @@ -1719,7 +1721,25 @@ DO 1; DO benchmark(100,1+1),1,1; # -# Drop the test tables +# Test of bug with SUM(CASE...) +# + +CREATE TABLE t1 (gvid int(10) unsigned default NULL, hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, mmid int(10) unsigned default NULL, hdid int(10) unsigned default NULL, fsid int(10) unsigned default NULL, ctid int(10) unsigned default NULL, dtid int(10) unsigned default NULL, cost int(10) unsigned default NULL, performance int(10) unsigned default NULL, serialnumber bigint(20) unsigned default NULL, monitored tinyint(3) unsigned default '1', removed tinyint(3) unsigned default '0', target tinyint(3) unsigned default '0', dt_modified timestamp(14) NOT NULL, name varchar(255) binary default NULL, description varchar(255) default NULL, UNIQUE KEY hmid (hmid,volid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (200001,2,1,1,100,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\E$',''),(200002,2,2,1,101,1,1,1,0,0,0,1,0,1,20020425060057,'\\\\ARKIVIO-TESTPDC\\C$',''),(200003,1,3,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,0,1,20020425060427,'c:',NULL); +CREATE TABLE t2 ( hmid int(10) unsigned default NULL, volid int(10) unsigned default NULL, sampletid smallint(5) unsigned default NULL, sampletime datetime default NULL, samplevalue bigint(20) unsigned default NULL, KEY idx1 (hmid,volid,sampletid,sampletime)) TYPE=MyISAM; +INSERT INTO t2 VALUES (1,3,10,'2002-06-01 08:00:00',35),(1,3,1010,'2002-06-01 12:00:01',35); +SELECT a.gvid, (SUM(CASE b.sampletid WHEN 140 THEN b.samplevalue ELSE 0 END)) as the_success,(SUM(CASE b.sampletid WHEN 141 THEN b.samplevalue ELSE 0 END)) as the_fail,(SUM(CASE b.sampletid WHEN 142 THEN b.samplevalue ELSE 0 END)) as the_size,(SUM(CASE b.sampletid WHEN 143 THEN b.samplevalue ELSE 0 END)) as the_time FROM t1 a, t2 b WHERE a.hmid = b.hmid AND a.volid = b.volid AND b.sampletime >= 'NULL' AND b.sampletime < 'NULL' AND b.sampletid IN (140, 141, 142, 143) GROUP BY a.gvid; +DROP TABLE t1,t2; + +# +# Test of bigint comparision # -drop table t4, t3,t2, t1; +create table t1 ( A_Id bigint(20) NOT NULL default '0', A_UpdateBy char(10) NOT NULL default '', A_UpdateDate bigint(20) NOT NULL default '0', A_UpdateSerial int(11) NOT NULL default '0', other_types bigint(20) NOT NULL default '0', wss_type bigint(20) NOT NULL default '0'); +INSERT INTO t1 VALUES (102935998719055004,'brade',1029359987,2,102935229116544068,102935229216544093); +select wss_type from t1 where wss_type ='102935229216544106'; +select wss_type from t1 where wss_type ='102935229216544105'; +select wss_type from t1 where wss_type ='102935229216544104'; +select wss_type from t1 where wss_type ='102935229216544093'; +select wss_type from t1 where wss_type =102935229216544093; +drop table t1; diff --git a/mysql-test/t/select_found.test b/mysql-test/t/select_found.test index c95d46b166c..0a483c860cb 100644 --- a/mysql-test/t/select_found.test +++ b/mysql-test/t/select_found.test @@ -68,3 +68,20 @@ SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id W SELECT FOUND_ROWS(); drop table t1,t2; + +# +# Test bug when using range optimization +# + +CREATE TABLE `t1` ( + `titre` char(80) NOT NULL default '', + `numeropost` mediumint(8) unsigned NOT NULL auto_increment, + `maxnumrep` int(10) unsigned NOT NULL default '0', + PRIMARY KEY (`numeropost`), + KEY `maxnumrep` (`maxnumrep`) +) TYPE=MyISAM ROW_FORMAT=FIXED; +INSERT INTO t1 (titre,maxnumrep) VALUES +('test1','1'),('test2','2'),('test3','3'); +SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; +SELECT FOUND_ROWS(); +drop table t1; diff --git a/mysql-test/t/truncate.test b/mysql-test/t/truncate.test index 1729ddc586a..841ce06351e 100644 --- a/mysql-test/t/truncate.test +++ b/mysql-test/t/truncate.test @@ -18,3 +18,5 @@ insert into t1 values (1),(2),(3); truncate table t1; select * from t1; drop table t1; +--error 1146 +truncate non_existing_table; diff --git a/mysql-test/t/type_decimal.test b/mysql-test/t/type_decimal.test index 21e06c0dbe9..95420539611 100644 --- a/mysql-test/t/type_decimal.test +++ b/mysql-test/t/type_decimal.test @@ -160,6 +160,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -169,6 +171,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -178,6 +182,8 @@ insert into t1 values ("-.1"),("+.1"),(".1"); insert into t1 values ("00000000000001"),("+0000000000001"),("-0000000000001"); insert into t1 values ("+111111111.11"),("111111111.11"),("-11111111.11"); insert into t1 values ("-111111111.11"),("+1111111111.11"),("1111111111.11"); +insert into t1 values ("1e+1000"),("1e-1000"),("-1e+1000"); +insert into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); select * from t1; drop table t1; @@ -189,6 +195,8 @@ insert into t1 values (-.1),(+.1),(.1); insert into t1 values (00000000000001),(+0000000000001),(-0000000000001); insert into t1 values (+111111111.11),(111111111.11),(-11111111.11); insert into t1 values (-111111111.11),(+1111111111.11),(1111111111.11); +insert into t1 values (1e+100),(1e-100),(-1e+100); +insert into t1 values (123.4e0),(123.4e+2),(123.4e-2),(123e1),(123e+0); select * from t1; drop table t1; diff --git a/mysql-test/t/union.test b/mysql-test/t/union.test index f5a92b05e0d..6e6b43ebe3b 100644 --- a/mysql-test/t/union.test +++ b/mysql-test/t/union.test @@ -23,7 +23,11 @@ select 't1',b,count(*) from t1 group by b UNION select 't2',b,count(*) from t2 g explain (select a,b from t1 limit 2) union all (select a,b from t2 order by a limit 1) order by b desc; (select sql_calc_found_rows a,b from t1 limit 2) union all (select a,b from t2 order by a) limit 2; select found_rows(); + +# # Test some error conditions with UNION +# + explain select a,b from t1 union all select a,b from t2; --error 1054 @@ -53,6 +57,9 @@ select * from t1 union select a from t2; --error 1222 select a from t1 union select * from t2; +--error 1234 +select * from t1 union select SQL_BUFFER_RESULT * from t2; + # Test CREATE, INSERT and REPLACE create table t3 select a,b from t1 union all select a,b from t2; insert into t3 select a,b from t1 union all select a,b from t2; @@ -88,6 +95,6 @@ insert into t2 values (11),(12),(13),(14),(15); (select * from t1 limit 2) union (select * from t2 limit 3); (select * from t1 limit 2) union (select * from t2 limit 20,3); set SQL_SELECT_LIMIT=2; -(select * from t1 limit 2) union (select * from t2 limit 3); +(select * from t1 limit 1) union (select * from t2 limit 3); set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; diff --git a/mysql-test/t/user_var.test b/mysql-test/t/user_var.test index 70283446fb3..bb1a9b59611 100644 --- a/mysql-test/t/user_var.test +++ b/mysql-test/t/user_var.test @@ -1,4 +1,4 @@ -error 1204; +error 1054; set @a := foo; set @a := connection_id() + 3; select @a - connection_id(); diff --git a/mysql-test/t/variables-master.opt b/mysql-test/t/variables-master.opt new file mode 100644 index 00000000000..5851d32ec31 --- /dev/null +++ b/mysql-test/t/variables-master.opt @@ -0,0 +1 @@ +max_join_size=10 diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test index b3127402238..7a1d01c2cb5 100644 --- a/mysql-test/t/variables.test +++ b/mysql-test/t/variables.test @@ -32,9 +32,207 @@ drop table t1; # Test system variables # +set max_join_size=100; +show variables like 'max_join_size'; +show global variables like 'max_join_size'; +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +set max_join_size=DEFAULT; +show variables like 'max_join_size'; +set GLOBAL max_join_size=DEFAULT; +show global variables like 'max_join_size'; +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +select @@identity, length(@@version)>0; select @@VERSION=version(); select last_insert_id(345); -select @@IDENTITY,last_insert_id(); -select @@identity; +select @@IDENTITY,last_insert_id(), @@identity; + +set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; + +set global concurrent_insert=ON; +show variables like 'concurrent_insert'; +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +set global concurrent_insert=OFF; +show variables like 'concurrent_insert'; +set global concurrent_insert=DEFAULT; +show variables like 'concurrent_insert'; + +set table_type=MYISAM, table_type="HEAP", global table_type="INNODB"; +show local variables like 'table_type'; +show global variables like 'table_type'; +set GLOBAL query_cache_size=100000; +set GLOBAL safe_show_database=0; + +set myisam_max_sort_file_size=10000, GLOBAL myisam_max_sort_file_size=20000; +show variables like 'myisam_max_sort_file_size'; +show global variables like 'myisam_max_sort_file_size'; +set myisam_max_sort_file_size=default; +show variables like 'myisam_max_sort_file_size'; + +set global net_retry_count=10, session net_retry_count=10; +set global net_buffer_length=1024, net_write_timeout=200, net_read_timeout=300; +set session net_buffer_length=2048, net_write_timeout=500, net_read_timeout=600; +show global variables like 'net_%'; +show session variables like 'net_%'; +set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +show session variables like 'net_%'; +set net_buffer_length=1; +show variables like 'net_buffer_length'; +set net_buffer_length=2000000000; +show variables like 'net_buffer_length'; + +set GLOBAL character set cp1251_koi8; +show global variables like "convert_character_set"; +set character set cp1251_koi8; +show variables like "convert_character_set"; +set global character set default, session character set default; +show variables like "convert_character_set"; +select @@timestamp>0; + +# The following should give errors + +--error 1231 +set big_tables=OFFF; +--error 1231 +set big_tables="OFFF"; --error 1193 -select @@unknown_variable; +set unknown_variable=1; +--error 1232 +set max_join_size="hello"; +--error 1231 +set table_type=UNKNOWN_TABLE_TYPE; +--error 1231 +set table_type=INNODB, big_tables=2; +show local variables like 'table_type'; +--error 1229 +set SESSION query_cache_size=10000; +--error 1230 +set GLOBAL table_type=DEFAULT; +--error 1115 +set convert_character_set=UNKNOWN_CHARACTER_SET; +--error 1115 +set character set unknown; +--error 1232 +set character set 0; +--error 1228 +set global autocommit=1; +--error 1228 +select @@global.timestamp; +--error 1193 +set @@version=''; +--error 1229 +set @@concurrent_insert=1; +--error 1228 +set @@global.sql_auto_is_null=1; +--error 1228 +select @@global.sql_auto_is_null; + +# Test setting all variables + +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +set global binlog_cache_size=100; +set bulk_insert_buffer_size=100; +set convert_character_set=cp1251_koi8; +set convert_character_set=default; +set @@global.concurrent_insert=1; +set global connect_timeout=100; +select @@delay_key_write; +set global delay_key_write="OFF"; +select @@delay_key_write; +set global delay_key_write=ALL; +select @@delay_key_write; +set global delay_key_write=1; +select @@delay_key_write; +set global delayed_insert_limit=100; +set global delayed_insert_timeout=100; +set global delayed_queue_size=100; +set global flush=1; +set global flush_time=100; +set insert_id=1; +set interactive_timeout=100; +set join_buffer_size=100; +set last_insert_id=1; +set global local_infile=1; +set long_query_time=100; +set low_priority_updates=1; +set max_allowed_packet=100; +set global max_binlog_cache_size=100; +set global max_binlog_size=100; +set global max_connect_errors=100; +set global max_connections=100; +set global max_delayed_threads=100; +set max_heap_table_size=100; +set max_join_size=100; +set max_sort_length=100; +set max_tmp_tables=100; +set global max_user_connections=100; +select @@max_user_connections; +set global max_write_lock_count=100; +set myisam_max_extra_sort_file_size=100; +select @@myisam_max_extra_sort_file_size; +set myisam_max_sort_file_size=100; +set myisam_sort_buffer_size=100; +set net_buffer_length=100; +set net_read_timeout=100; +set net_write_timeout=100; +set global query_cache_limit=100; +set global query_cache_size=100; +set global query_cache_type=demand; +set read_buffer_size=100; +set read_rnd_buffer_size=100; +set global rpl_recovery_rank=100; +set global safe_show_database=1; +set global server_id=100; +set global slave_net_timeout=100; +set global slow_launch_time=100; +set sort_buffer_size=100; +set sql_auto_is_null=1; +select @@sql_auto_is_null; +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +set sql_big_selects=1; +set sql_big_tables=1; +set sql_buffer_result=1; +set sql_log_bin=1; +set sql_log_off=1; +set sql_log_update=1; +set sql_low_priority_updates=1; +set sql_max_join_size=200; +select @@sql_max_join_size,@@max_join_size; +set sql_quote_show_create=1; +set sql_safe_updates=1; +set sql_select_limit=1; +set global sql_slave_skip_counter=100; +set sql_warnings=1; +set global table_cache=100; +set table_type=myisam; +set global thread_cache_size=100; +set timestamp=1, timestamp=default; +set tmp_table_size=100; +set tx_isolation="READ-COMMITTED"; +set wait_timeout=100; +set log_warnings=1; + +# +# key buffer +# + +DROP TABLE IF EXISTS t1,t2; +create table t1 (a int not null auto_increment, primary key(a)); +create table t2 (a int not null auto_increment, primary key(a)); +insert into t1 values(null),(null),(null); +insert into t2 values(null),(null),(null); +set global key_buffer_size=100000; +select @@key_buffer_size; +select * from t1 where a=2; +select * from t2 where a=3; +check table t1,t2; +drop table t1,t2; + |