diff options
Diffstat (limited to 'mysql-test/r')
82 files changed, 1488 insertions, 300 deletions
diff --git a/mysql-test/r/bdb-alter-table-1.result b/mysql-test/r/bdb-alter-table-1.result new file mode 100644 index 00000000000..095d89355ad --- /dev/null +++ b/mysql-test/r/bdb-alter-table-1.result @@ -0,0 +1,11 @@ +drop table if exists t1; +create table t1(objid BIGINT not null, tablename varchar(64), oid BIGINT not null, test BIGINT, PRIMARY KEY (objid), UNIQUE(tablename)) type=BDB; +insert into t1 values(1, 't1',4,9); +insert into t1 values(2, 'metatable',1,9); +insert into t1 values(3, 'metaindex',1,9 ); +select * from t1; +objid tablename oid test +1 t1 4 9 +2 metatable 1 9 +3 metaindex 1 9 +alter table t1 drop column test; diff --git a/mysql-test/r/bdb-alter-table-2.result b/mysql-test/r/bdb-alter-table-2.result new file mode 100644 index 00000000000..c23b5ba0031 --- /dev/null +++ b/mysql-test/r/bdb-alter-table-2.result @@ -0,0 +1,6 @@ +select * from t1; +objid tablename oid +1 t1 4 +2 metatable 1 +3 metaindex 1 +drop table t1; diff --git a/mysql-test/r/bigint.result b/mysql-test/r/bigint.result index f2cb6437f8f..f666c5311b8 100644 --- a/mysql-test/r/bigint.result +++ b/mysql-test/r/bigint.result @@ -17,9 +17,6 @@ a select * from t1 where a=18446744073709551615; a 18446744073709551615 -select * from t1 where a='18446744073709551615'; -a -18446744073709551615 delete from t1 where a=18446744073709551615; select * from t1; a diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index 9f1c8ffac41..325accf00e7 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (name char(20) not null, primary key (name)); create table t2 (name char(20) binary not null, primary key (name)); insert into t1 values ("å"); @@ -53,3 +54,13 @@ select * from t1 where b="hello"; a b hello hello drop table t1; +create table t1 (b char(8)); +insert into t1 values(NULL); +select b from t1 where binary b like ''; +b +select b from t1 group by binary b like ''; +b +NULL +select b from t1 having binary b like ''; +b +drop table t1; diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index 625ef684698..e030698a396 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -150,5 +150,12 @@ t1 CREATE TABLE `t1` ( KEY `b_29` (`b`), KEY `b_30` (`b`), KEY `b_31` (`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; +create table t1 select if(1,'1','0'), month("2002-08-02"); +drop table t1; +create table t1 select if('2002'='2002','Y','N'); +select * from t1; +if('2002'='2002','Y','N') +Y +drop table if exists t1; diff --git a/mysql-test/r/ctype_latin1_de.result b/mysql-test/r/ctype_latin1_de.result index 58ed0dfdbf3..e5ae6f249ee 100644 --- a/mysql-test/r/ctype_latin1_de.result +++ b/mysql-test/r/ctype_latin1_de.result @@ -192,7 +192,7 @@ strcmp('ßa','ss') strcmp('ssa','ß') strcmp('sssb','sßa') strcmp('ß','s') select strcmp('u','öa'),strcmp('u','ö'); strcmp('u','öa') strcmp('u','ö') 1 1 -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%"; a @@ -204,4 +204,7 @@ test select * from t1 where a like "te_t"; a test +select * from t1 where match a against ("te*" in boolean mode)+0; +a +test drop table t1; diff --git a/mysql-test/r/delayed.result b/mysql-test/r/delayed.result index 44aef139ad2..9e375203e49 100644 --- a/mysql-test/r/delayed.result +++ b/mysql-test/r/delayed.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a char(10), tmsp timestamp); insert into t1 set a = 1; insert delayed into t1 set a = 2; diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result index 5848e583d7e..227b34f3691 100644 --- a/mysql-test/r/distinct.result +++ b/mysql-test/r/distinct.result @@ -350,3 +350,48 @@ select distinct a from t1 where a >= '1' order by a desc; a 1 drop table t1; +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; +email shipcode +test1@testdomain.com Z001 +test2@testdomain.com Z001 +test3@testdomain.com Z001 +test2@testdomain.com R002 +SELECT DISTINCTROW email FROM t1 ORDER BY dateentered DESC; +email +test1@testdomain.com +test2@testdomain.com +test3@testdomain.com +SELECT DISTINCTROW email, shipcode FROM t1, t2 WHERE t1.infoID=t2.infoID ORDER BY dateentered DESC; +email shipcode +test1@testdomain.com Z001 +test2@testdomain.com Z001 +test2@testdomain.com R002 +test3@testdomain.com Z001 +drop table t1,t2; +CREATE TABLE t1 (privatemessageid int(10) unsigned NOT NULL auto_increment, folderid smallint(6) NOT NULL default '0', userid int(10) unsigned NOT NULL default '0', touserid int(10) unsigned NOT NULL default '0', fromuserid int(10) unsigned NOT NULL default '0', title varchar(250) NOT NULL default '', message mediumtext NOT NULL, dateline int(10) unsigned NOT NULL default '0', showsignature smallint(6) NOT NULL default '0', iconid smallint(5) unsigned NOT NULL default '0', messageread smallint(6) NOT NULL default '0', readtime int(10) unsigned NOT NULL default '0', receipt smallint(6) unsigned NOT NULL default '0', deleteprompt smallint(6) unsigned NOT NULL default '0', multiplerecipients smallint(6) unsigned NOT NULL default '0', PRIMARY KEY (privatemessageid), KEY userid (userid)) TYPE=MyISAM; +INSERT INTO t1 VALUES (128,0,33,33,8,':D','',996121863,1,0,2,996122850,2,0,0); +CREATE TABLE t2 (userid int(10) unsigned NOT NULL auto_increment, usergroupid smallint(5) unsigned NOT NULL default '0', username varchar(50) NOT NULL default '', password varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', styleid smallint(5) unsigned NOT NULL default '0', parentemail varchar(50) NOT NULL default '', coppauser smallint(6) NOT NULL default '0', homepage varchar(100) NOT NULL default '', icq varchar(20) NOT NULL default '', aim varchar(20) NOT NULL default '', yahoo varchar(20) NOT NULL default '', signature mediumtext NOT NULL, adminemail smallint(6) NOT NULL default '0', showemail smallint(6) NOT NULL default '0', invisible smallint(6) NOT NULL default '0', usertitle varchar(250) NOT NULL default '', customtitle smallint(6) NOT NULL default '0', joindate int(10) unsigned NOT NULL default '0', cookieuser smallint(6) NOT NULL default '0', daysprune smallint(6) NOT NULL default '0', lastvisit int(10) unsigned NOT NULL default '0', lastactivity int(10) unsigned NOT NULL default '0', lastpost int(10) unsigned NOT NULL default '0', posts smallint(5) unsigned NOT NULL default '0', timezoneoffset varchar(4) NOT NULL default '', emailnotification smallint(6) NOT NULL default '0', buddylist mediumtext NOT NULL, ignorelist mediumtext NOT NULL, pmfolders mediumtext NOT NULL, receivepm smallint(6) NOT NULL default '0', emailonpm smallint(6) NOT NULL default '0', pmpopup smallint(6) NOT NULL default '0', avatarid smallint(6) NOT NULL default '0', avatarrevision int(6) unsigned NOT NULL default '0', options smallint(6) NOT NULL default '15', birthday date NOT NULL default '0000-00-00', maxposts smallint(6) NOT NULL default '-1', startofweek smallint(6) NOT NULL default '1', ipaddress varchar(20) NOT NULL default '', referrerid int(10) unsigned NOT NULL default '0', nosessionhash smallint(6) NOT NULL default '0', autorefresh smallint(6) NOT NULL default '-1', messagepopup tinyint(2) NOT NULL default '0', inforum smallint(5) unsigned NOT NULL default '0', ratenum smallint(5) unsigned NOT NULL default '0', ratetotal smallint(5) unsigned NOT NULL default '0', allowrate smallint(5) unsigned NOT NULL default '1', PRIMARY KEY (userid), KEY usergroupid (usergroupid), KEY username (username), KEY inforum (inforum)) TYPE=MyISAM; +INSERT INTO t2 VALUES (33,6,'Kevin','0','kevin@stileproject.com',1,'',0,'http://www.stileproject.com','','','','',1,1,0,'Administrator',0,996120694,1,-1,1030996168,1031027028,1030599436,36,'-6',0,'','','',1,0,1,0,0,15,'0000-00-00',-1,1,'64.0.0.0',0,1,-1,0,0,4,19,1); +SELECT DISTINCT t1.*, t2.* FROM t1 LEFT JOIN t2 ON (t2.userid = t1.touserid); +privatemessageid folderid userid touserid fromuserid title message dateline showsignature iconid messageread readtime receipt deleteprompt multiplerecipients userid usergroupid username password email styleid parentemail coppauser homepage icq aim yahoo signature adminemail showemail invisible usertitle customtitle joindate cookieuser daysprune lastvisit lastactivity lastpost posts timezoneoffset emailnotification buddylist ignorelist pmfolders receivepm emailonpm pmpopup avatarid avatarrevision options birthday maxposts startofweek ipaddress referrerid nosessionhash autorefresh messagepopup inforum ratenum ratetotal allowrate +128 0 33 33 8 :D 996121863 1 0 2 996122850 2 0 0 33 6 Kevin 0 kevin@stileproject.com 1 0 http://www.stileproject.com 1 1 0 Administrator 0 996120694 1 -1 1030996168 1031027028 1030599436 36 -6 0 1 0 1 0 0 15 0000-00-00 -1 1 64.0.0.0 0 1 -1 0 0 4 19 1 +DROP TABLE IF EXISTS t1,t2; +CREATE TABLE t1 (a int primary key, b int, c int); +INSERT t1 VALUES (1,2,3); +CREATE TABLE t2 (a int primary key, b int, c int); +INSERT t2 VALUES (3,4,5); +SELECT DISTINCT t1.a, t2.b FROM t1, t2 WHERE t1.a=1 ORDER BY t2.c; +a b +1 4 +DROP TABLE IF EXISTS t1,t2; diff --git a/mysql-test/r/fulltext.result b/mysql-test/r/fulltext.result index e6598ca9ca4..2b666b11362 100644 --- a/mysql-test/r/fulltext.result +++ b/mysql-test/r/fulltext.result @@ -70,6 +70,8 @@ Full-text search in MySQL implements vector space model 0 select * from t1 where MATCH a,b AGAINST ("+call* +coll*" IN BOOLEAN MODE); a b Full-text indexes are called collections +select * from t1 where MATCH a,b AGAINST ('"support now"' IN BOOLEAN MODE); +a b select * from t1 where MATCH a,b AGAINST ('"Now sUPPort"' IN BOOLEAN MODE); a b MySQL has now support for full-text search @@ -139,7 +141,7 @@ t2 CREATE TABLE `t2` ( `inhalt` text character set latin1, KEY `tig` (`ticket`), FULLTEXT KEY `tix` (`inhalt`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 select * from t2 where MATCH inhalt AGAINST (NULL); ticket inhalt select * from t2 where MATCH inhalt AGAINST ('foobar'); @@ -192,3 +194,12 @@ a b 2 fullaaa fullzzz 1 I wonder why the fulltext index doesnt work? 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; +8 +8 +8 +8 +8 +drop table t1; diff --git a/mysql-test/r/fulltext_left_join.result b/mysql-test/r/fulltext_left_join.result index 9f2ba6a5947..abc63358dbe 100644 --- a/mysql-test/r/fulltext_left_join.result +++ b/mysql-test/r/fulltext_left_join.result @@ -1,3 +1,4 @@ +drop table if exists t1, t2; CREATE TABLE t1 ( id VARCHAR(255) NOT NULL PRIMARY KEY, sujet VARCHAR(255), diff --git a/mysql-test/r/fulltext_order_by.result b/mysql-test/r/fulltext_order_by.result index 8d88b8d6ebf..c0e72ff789f 100644 --- a/mysql-test/r/fulltext_order_by.result +++ b/mysql-test/r/fulltext_order_by.result @@ -64,3 +64,18 @@ a rel 4 1 7 1 drop table t1; +CREATE TABLE t1 ( +a INT AUTO_INCREMENT PRIMARY KEY, +message CHAR(20), +FULLTEXT(message) +); +INSERT INTO t1 (message) VALUES ("testbug"),("testbug foobar"); +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1; +a rel +1 1 +2 2 +SELECT a, MATCH (message) AGAINST ('t* f*' IN BOOLEAN MODE) as rel FROM t1 ORDER BY rel,a; +a rel +1 1 +2 2 +drop table t1; diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result index 2d24b8ffd4e..c1f36283ab0 100644 --- a/mysql-test/r/func_group.result +++ b/mysql-test/r/func_group.result @@ -1,3 +1,4 @@ +drop table if exists t1,t2; create table t1 (grp int, a bigint unsigned, c char(10) not null); insert into t1 values (1,1,"a"); insert into t1 values (2,2,"b"); diff --git a/mysql-test/r/func_if.result b/mysql-test/r/func_if.result index 16fb0358d51..0ab41258091 100644 --- a/mysql-test/r/func_if.result +++ b/mysql-test/r/func_if.result @@ -46,3 +46,9 @@ select sum(if(num is null,0.00,num)) from t1; sum(if(num is null,0.00,num)) 144.54 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; +min(if(y -x > 5,y,NULL)) max(if(y - x > 5,y,NULL)) +6 56 +drop table t1; diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result index 8c991dee8d8..ba33ee0831d 100644 --- a/mysql-test/r/func_in.result +++ b/mysql-test/r/func_in.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 (field char(1)); INSERT INTO t1 VALUES ('A'),(NULL); SELECT * from t1 WHERE field IN (NULL); diff --git a/mysql-test/r/func_math.result b/mysql-test/r/func_math.result index f067d1f651e..f1c0de2f88a 100644 --- a/mysql-test/r/func_math.result +++ b/mysql-test/r/func_math.result @@ -16,9 +16,18 @@ 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); abs(-10) sign(-5) sign(5) sign(0) 10 -1 1 0 -select log(exp(10)),exp(log(sqrt(10))*2); -log(exp(10)) exp(log(sqrt(10))*2) -10.000000 10.000000 +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); +log(exp(10)) exp(log(sqrt(10))*2) log(-1) log(NULL) log(1,1) log(3,9) log(-1,2) log(NULL,2) +10.000000 10.000000 NULL NULL NULL 2.000000 NULL NULL +select ln(exp(10)),exp(ln(sqrt(10))*2),ln(-1),ln(0),ln(NULL); +ln(exp(10)) exp(ln(sqrt(10))*2) ln(-1) ln(0) ln(NULL) +10.000000 10.000000 NULL NULL NULL +select log2(8),log2(15),log2(-2),log2(0),log2(NULL); +log2(8) log2(15) log2(-2) log2(0) log2(NULL) +3.000000 3.906891 NULL NULL NULL +select log10(100),log10(18),log10(-4),log10(0),log10(NULL); +log10(100) log10(18) log10(-4) log10(0) log10(NULL) +2.000000 1.255273 NULL NULL NULL select pow(10,log10(10)),power(2,4); pow(10,log10(10)) power(2,4) 10.000000 16.000000 diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index a58f3c57169..69e37d7b911 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -83,6 +83,36 @@ soundex('') soundex('he') soundex('hello all folks') select md5('hello'); md5('hello') 5d41402abc4b2a76b9719d911017c592 +select sha('abc'); +sha('abc') +a9993e364706816aba3e25717850c26c9cd0d89d +select sha1('abc'); +sha1('abc') +a9993e364706816aba3e25717850c26c9cd0d89d +select aes_decrypt(aes_encrypt('abc','1'),'1'); +aes_decrypt(aes_encrypt('abc','1'),'1') +abc +select aes_decrypt(aes_encrypt('abc','1'),1); +aes_decrypt(aes_encrypt('abc','1'),1) +abc +select aes_encrypt(NULL,"a"); +aes_encrypt(NULL,"a") +NULL +select aes_encrypt("a",NULL); +aes_encrypt("a",NULL) +NULL +select aes_decrypt(NULL,"a"); +aes_decrypt(NULL,"a") +NULL +select aes_decrypt("a",NULL); +aes_decrypt("a",NULL) +NULL +select aes_decrypt("a","a"); +aes_decrypt("a","a") +NULL +select aes_decrypt(aes_encrypt("","a"),"a"); +aes_decrypt(aes_encrypt("","a"),"a") + select repeat('monty',5),concat('*',space(5),'*'); repeat('monty',5) concat('*',space(5),'*') montymontymontymontymonty * * @@ -116,6 +146,15 @@ decode(encode(repeat("a",100000),"monty"),"monty")=repeat("a",100000) select decode(encode("abcdef","monty"),"monty")="abcdef"; decode(encode("abcdef","monty"),"monty")="abcdef" 1 +select quote('\'\"\\test'); +quote('\'\"\\test') +'\'"\\test' +select quote(concat('abc\'', '\\cba')); +quote(concat('abc\'', '\\cba')) +'abc\'\\cba' +select quote(1/0), quote('\0\Z'); +quote(1/0) quote('\0\Z') +NULL '\0\Z' select reverse(""); reverse("") @@ -184,3 +223,8 @@ SELECT CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugs CONCAT_WS('";"',title,prio,category,program,bugdesc,created,modified,bugstatus,submitter) Link";"1";"1";"1";"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa";"2001-02-28 08:40:16";"20010228084016";"0";"4 drop table t1; +CREATE TABLE t1 (id int(11) NOT NULL auto_increment, tmp text NOT NULL, KEY id (id)) TYPE=MyISAM; +INSERT INTO t1 VALUES (1, 'a545f661efdd1fb66fdee3aab79945bf'); +SELECT 1 FROM t1 WHERE tmp=AES_DECRYPT(tmp,"password"); +1 +DROP TABLE t1; diff --git a/mysql-test/r/func_test.result b/mysql-test/r/func_test.result index deea5b8bb8b..8cfae44b9dd 100644 --- a/mysql-test/r/func_test.result +++ b/mysql-test/r/func_test.result @@ -40,6 +40,12 @@ select 2 in (3,2,5,9,5,1),"monty" in ("david","monty","allan"), 1.2 in (1.4,1.2, select -1.49 or -1.49,0.6 or 0.6; -1.49 or -1.49 0.6 or 0.6 1 1 +select 3 ^ 11, 1 ^ 1, 1 ^ 0, 1 ^ NULL, NULL ^ 1; +3 ^ 11 1 ^ 1 1 ^ 0 1 ^ NULL NULL ^ 1 +8 0 1 NULL NULL +select 1 XOR 1, 1 XOR 0, 0 XOR 1, 0 XOR 0, NULL XOR 1, 1 XOR NULL, 0 XOR NULL; +1 XOR 1 1 XOR 0 0 XOR 1 0 XOR 0 NULL XOR 1 1 XOR NULL 0 XOR NULL +0 1 1 0 NULL NULL NULL select 5 between 0 and 10 between 0 and 1,(5 between 0 and 10) between 0 and 1; 5 between 0 and 10 between 0 and 1 (5 between 0 and 10) between 0 and 1 0 1 diff --git a/mysql-test/r/grant.result b/mysql-test/r/grant.result new file mode 100644 index 00000000000..a1a0c91a575 --- /dev/null +++ b/mysql-test/r/grant.result @@ -0,0 +1,60 @@ +delete from mysql.user where user='mysqltest_1'; +delete from mysql.db where user='mysqltest_1'; +flush privileges; +grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant delete on mysqltest.* to mysqltest_1@localhost; +select * from mysql.user where user="mysqltest_1"; +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections +localhost mysqltest_1 N N N N N N N N N N N N N N N N N N N N N SPECIFIED EDH-RSA-DES-CBC3-SHA 0 0 0 +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke delete on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant select on mysqltest.* to mysqltest_1@localhost require NONE; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB"; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA' +delete from mysql.user where user='mysqltest_1'; +flush privileges; +grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' +grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION; +flush privileges; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost; +show grants for mysqltest_1@localhost; +Grants for mysqltest_1@localhost +GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION +revoke all privileges on mysqltest.* from mysqltest_1@localhost; +delete from mysql.user where user='mysqltest_1'; +flush privileges; diff --git a/mysql-test/r/grant_cache.result b/mysql-test/r/grant_cache.result index d236c26d71a..3892765f587 100644 --- a/mysql-test/r/grant_cache.result +++ b/mysql-test/r/grant_cache.result @@ -104,11 +104,11 @@ a 1 2 select c from t1; -select command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' +SELECT command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' select * from t2; select command denied to user: 'mysqltest_3@localhost' for table 't2' select mysqltest.t1.c from test.t1,mysqltest.t1; -select command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' +SELECT command denied to user: 'mysqltest_3@localhost' for column 'c' in table 't1' show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 6 diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result index 54b4e3fba6d..449d7bcb818 100644 --- a/mysql-test/r/group_by.result +++ b/mysql-test/r/group_by.result @@ -375,21 +375,45 @@ hijklm 2 DROP TABLE t1; drop table if exists t1; 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; One Two sum(Four) 1 1 16 1 2 16 1 3 16 -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; +gender dist_count percentage +F 3 60.00 +M 1 20.00 +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; +gender dist_count percentage +M 1 20.00 +F 3 60.00 +drop table t1,t2; +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; +xID xID1 +1 1 +2 2 +2 2 +3 134 +3 134 +3 134 +4 185 +4 185 +4 185 +4 185 +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; +xID xID1 Level +1 1 * +2 2 ** +3 134 *** +4 185 **** +drop table t1; diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result index d495dde75bf..35765c48049 100644 --- a/mysql-test/r/handler.result +++ b/mysql-test/r/handler.result @@ -134,6 +134,6 @@ handler t2 read next; a b 19 fff handler t2 read last; -You have an error in your SQL syntax near '' at line 1 +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 handler t2 close; drop table if exists t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index de08465a2a0..8f139799577 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -455,7 +455,7 @@ Duplicate entry 'test2' for key 2 select * from t1; id ggid email passwd 1 this will work -4 test2 this will work +3 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work @@ -807,7 +807,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` char(20) character set latin1 default NULL, KEY `a` (`a`) -) TYPE=InnoDB +) TYPE=InnoDB CHARSET=latin1 drop table t1; create temporary table t1 (a int not null auto_increment, primary key(a)) type=innodb; insert into t1 values (NULL),(NULL),(NULL); @@ -920,12 +920,6 @@ create table t1 (t int not null default 1, key (t)) type=innodb; desc t1; Field Type Null Key Default Extra t int(11) MUL 1 -handler t1 open t1; -Table handler for 't1' doesn't have this option -handler t1 read t first; -Unknown table 't1' in HANDLER -handler t1 close; -Unknown table 't1' in HANDLER drop table t1; CREATE TABLE t1 ( number bigint(20) NOT NULL default '0', @@ -989,3 +983,41 @@ select * from t2; number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status 333 tubs 99 2 20020109113453 501 20020109113453 500 3 10 0 drop table t1,t2; +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; +@@tx_isolation @@global.tx_isolation +SERIALIZABLE READ-COMMITTED +insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); +select id, code, name from t1 order by id; +id code name +1 1 Tim +2 1 Monty +3 2 David +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; +id code name +1 1 Tim +2 1 Monty +3 2 David +4 2 Erik +5 3 Sasha +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; +id code name +1 1 Tim +2 1 Monty +3 2 David +4 2 Erik +5 3 Sasha +6 3 Jeremy +7 4 Matt +COMMIT; +DROP TABLE t1; diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result new file mode 100644 index 00000000000..a55114a0e3d --- /dev/null +++ b/mysql-test/r/innodb_handler.result @@ -0,0 +1,139 @@ +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; +a b +14 aaa +handler t2 read a next; +a b +15 bbb +handler t2 read a next; +a b +16 ccc +handler t2 read a prev; +a b +15 bbb +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a prev; +a b +20 ggg +handler t2 read a first; +a b +14 aaa +handler t2 read a prev; +a b +handler t2 read a last; +a b +22 iii +handler t2 read a prev; +a b +21 hhh +handler t2 read a next; +a b +22 iii +handler t2 read a next; +a b +handler t2 read a=(15); +a b +15 bbb +handler t2 read a=(16); +a b +16 ccc +handler t2 read a=(19,"fff"); +Too many key parts specified. Max 1 parts allowed +handler t2 read b=(19,"fff"); +a b +19 fff +handler t2 read b=(19,"yyy"); +a b +19 yyy +handler t2 read b=(19); +a b +19 fff +handler t1 read a last; +Unknown table 't1' in HANDLER +handler t2 read a=(11); +a b +handler t2 read a>=(11); +a b +14 aaa +handler t2 read a=(18); +a b +18 eee +handler t2 read a>=(18); +a b +18 eee +handler t2 read a>(18); +a b +19 fff +handler t2 read a<=(18); +a b +18 eee +handler t2 read a<(18); +a b +17 ddd +handler t2 read a first limit 5; +a b +14 aaa +15 bbb +16 ccc +16 xxx +17 ddd +handler t2 read a next limit 3; +a b +18 eee +19 fff +19 yyy +handler t2 read a prev limit 10; +a b +19 fff +18 eee +17 ddd +16 xxx +16 ccc +15 bbb +14 aaa +handler t2 read a>=(16) limit 4; +a b +16 ccc +16 xxx +17 ddd +18 eee +handler t2 read a>=(16) limit 2,2; +a b +17 ddd +18 eee +handler t2 read a last limit 3; +a b +22 iii +21 hhh +20 ggg +handler t2 read a=(19); +a b +19 fff +handler t2 read a=(19) where b="yyy"; +a b +19 yyy +handler t2 read first; +a b +17 ddd +handler t2 read next; +a b +18 eee +alter table t1 type=innodb; +handler t2 read next; +a b +19 fff +handler t2 read last; +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 +handler t2 close; +drop table if exists t1; diff --git a/mysql-test/r/insert.result b/mysql-test/r/insert.result index f57efa33020..2ffa9d88618 100644 --- a/mysql-test/r/insert.result +++ b/mysql-test/r/insert.result @@ -19,3 +19,39 @@ insert into t1 values (0,"mysql a"); insert into t1 values (0,"r1manic"); insert into t1 values (0,"r1man"); drop table t1; +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; +a t>0 c i +1 1 hello NULL +2 1 hello NULL +4 0 a 5 +5 1 hello NULL +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; +a t>0 c i +1 1 hello NULL +2 1 hello NULL +4 0 a 5 +5 0 a NULL +6 1 hello NULL +drop table t1; +create table t1 (sid char(20), id int(2) NOT NULL auto_increment, key(sid, id)); +insert into t1 values ('skr',NULL),('skr',NULL),('test',NULL); +select * from t1; +sid id +skr 1 +skr 2 +test 1 +drop table t1; +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/r/insert_select.result b/mysql-test/r/insert_select.result index d61b6c67030..e24c3179a0c 100644 --- a/mysql-test/r/insert_select.result +++ b/mysql-test/r/insert_select.result @@ -61,7 +61,7 @@ SELECT 1618,icone,contenu,pseudo,date,signature,ip FROM crash2 WHERE numeropost=9 ORDER BY numreponse ASC; show variables like '%bulk%'; Variable_name Value -myisam_bulk_insert_tree_size 8388608 +bulk_insert_buffer_size 8388608 INSERT INTO crash1 (numeropost,icone,contenu,pseudo,date,signature,ip) SELECT 1718,icone,contenu,pseudo,date,signature,ip FROM crash2 WHERE numeropost=9 ORDER BY numreponse ASC; diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result index 6dfe0809e7f..1cd9c9dfe79 100644 --- a/mysql-test/r/key.result +++ b/mysql-test/r/key.result @@ -147,3 +147,34 @@ t1 0 e 1 e A 0 NULL NULL BTREE t1 0 b 1 b A NULL NULL NULL YES BTREE t1 1 c 1 c A NULL NULL NULL YES BTREE drop table t1; +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; +c i + 1 + 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i + 1 + 2 +a 1 +a 2 +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; +c i +NULL 1 +NULL 2 +INSERT INTO t1 (c) VALUES ('a'),('a'); +SELECT * FROM t1; +c i +NULL 1 +NULL 2 +a 1 +a 2 +drop table t1; diff --git a/mysql-test/r/key_primary.result b/mysql-test/r/key_primary.result index df9d962b3ac..e148548b721 100644 --- a/mysql-test/r/key_primary.result +++ b/mysql-test/r/key_primary.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t1 char(3) primary key); insert into t1 values("ABC"); insert into t1 values("ABA"); diff --git a/mysql-test/r/lock_multi.result b/mysql-test/r/lock_multi.result index 6fe8cc71185..20bc9b9572f 100644 --- a/mysql-test/r/lock_multi.result +++ b/mysql-test/r/lock_multi.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1(n int); insert into t1 values (1); lock tables t1 write; diff --git a/mysql-test/r/lowercase_table.result b/mysql-test/r/lowercase_table.result new file mode 100644 index 00000000000..1caaf317c96 --- /dev/null +++ b/mysql-test/r/lowercase_table.result @@ -0,0 +1,15 @@ +drop table if exists t1,t2,t3; +create table T1 (id int primary key, Word varchar(40) not null, Index(Word)); +INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3, 'c'); +SELECT * FROM t1; +id Word +1 a +2 b +3 c +RENAME TABLE T1 TO T2; +ALTER TABLE T2 ADD new_col int not null; +ALTER TABLE T2 RENAME T3; +show tables like 't_'; +Tables_in_test (t_) +t3 +drop table t3; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 590169338a6..27ce8e1d915 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -174,7 +174,7 @@ t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` char(20) character set latin1 default NULL, KEY `a` (`a`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) create table t4 (a int not null, b char(10), key(a)) type=MERGE UNION=(t1,t2); select * from t4; Can't open file: 't4.MRG'. (errno: 143) @@ -249,14 +249,14 @@ t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0', PRIMARY KEY (`incr`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) alter table t3 drop primary key; show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `incr` int(11) NOT NULL default '0', `othr` int(11) NOT NULL default '0' -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) drop table t3,t2,t1; create table t1 (a int not null) type=merge; select * from t1; @@ -275,42 +275,42 @@ a b 1 2 drop table t3,t1,t2; drop table if exists t6, t5, t4, t3, t2, t1; -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; Table Create Table t3 CREATE TABLE `t3` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 show create table t4; Table Create Table t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM UNION=(t1,t2) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2) show create table t5; Table Create Table t5 CREATE TABLE `t5` ( `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', - KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2) + `b` int(11) NOT NULL auto_increment, + PRIMARY KEY (`a`,`b`) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2) show create table t6; Table Create Table t6 CREATE TABLE `t6` ( `a` int(11) NOT NULL default '0', - `b` int(11) NOT NULL default '0', - KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1,t2) -insert into t1 values (1,1),(1,2),(1,3),(1,4); -insert into t2 values (2,1),(2,2),(2,3),(2,4); + `b` int(11) NOT NULL auto_increment, + PRIMARY KEY (`a`,`b`) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1,t2) +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; a b select * from t4 order by b,a limit 3; @@ -373,7 +373,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 UNION=(t1,t2,t3) select * from t4 order by a,b; a b 1 1 @@ -399,7 +399,7 @@ t4 CREATE TABLE `t4` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', KEY `a` (`a`,`b`) -) TYPE=MRG_MyISAM INSERT_METHOD=FIRST UNION=(t1,t2,t3) +) TYPE=MRG_MyISAM CHARSET=latin1 INSERT_METHOD=FIRST UNION=(t1,t2,t3) insert into t4 values (4,1),(4,2); select * from t1 order by a,b; a b @@ -461,4 +461,83 @@ a b 5 2 6 1 6 2 +select 1; +1 +1 +insert into t5 values (1,NULL),(5,NULL); +insert into t6 values (2,NULL),(6,NULL); +select * from t1 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +4 1 +4 2 +5 1 +5 2 +5 3 +select * from t2 order by a,b; +a b +2 1 +2 2 +2 3 +2 4 +2 5 +6 1 +6 2 +6 3 +select * from t5 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +2 1 +2 2 +2 3 +2 4 +2 5 +4 1 +4 2 +5 1 +5 2 +5 3 +6 1 +6 2 +6 3 +select * from t6 order by a,b; +a b +1 1 +1 2 +1 3 +1 4 +1 5 +2 1 +2 2 +2 3 +2 4 +2 5 +4 1 +4 2 +5 1 +5 2 +5 3 +6 1 +6 2 +6 3 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; +max(b) +NULL +select max(b) from t1 where a = 2; +max(b) +1 +drop table if exists t,t1,t2; diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result index 85819d9571d..328eb9b394e 100644 --- a/mysql-test/r/multi_update.result +++ b/mysql-test/r/multi_update.result @@ -66,3 +66,72 @@ select count(*) from t3 where id3; count(*) 0 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; +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; +id mydate +1 2002-02-04 00:00:00 +5 2002-05-12 00:00:00 +6 2002-06-22 00:00:00 +7 2002-07-22 00:00:00 +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; +ID ParId tst tst1 +1 1 NULL NULL +2 2 NULL NULL +3 3 NULL NULL +UPDATE child, parent +SET child.tst = parent.tst, +child.tst1 = parent.tst1 +WHERE child.ParId = parent.Id; +select * from child; +ID ParId tst tst1 +1 1 MySQL MySQL AB +2 2 MSSQL Microsoft +3 3 ORACLE ORACLE +drop table parent, child; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 4b4a88a54f7..29818f28c45 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -75,3 +75,42 @@ explain select a,b,c from t1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 drop table t1; +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; +Table Op Msg_type Msg_text +test.t1 repair status OK +CHECK TABLE t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2), (3); +LOCK TABLES t1 WRITE; +INSERT INTO t1 VALUES (1), (2), (3); +OPTIMIZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 optimize status OK +DROP TABLE t1; +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), KEY t1 (a, b, c)); +Specified key was too long. Max key length is 500 +CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255)); +ALTER TABLE t1 ADD INDEX t1 (a, b, c); +Specified key was too long. Max key length is 500 +DROP TABLE t1; diff --git a/mysql-test/r/odbc.result b/mysql-test/r/odbc.result index f377d54e1a0..498147704a3 100644 --- a/mysql-test/r/odbc.result +++ b/mysql-test/r/odbc.result @@ -1,6 +1,7 @@ select {fn length("hello")}, { date "1997-10-20" }; {fn length("hello")} 1997-10-20 5 1997-10-20 +drop table if exists t1; create table t1 (a int not null auto_increment,b int not null,primary key (a,b)); insert into t1 SET A=NULL,B=1; insert into t1 SET a=null,b=2; diff --git a/mysql-test/r/olap.result b/mysql-test/r/olap.result new file mode 100644 index 00000000000..52bd83df5ed --- /dev/null +++ b/mysql-test/r/olap.result @@ -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); +select product, country , year, sum(profit) from sales group by product, country, year with cube; +This version of MySQL doesn't yet support 'CUBE' +explain select product, country , year, sum(profit) from sales group by product, country, year with cube; +This version of MySQL doesn't yet support 'CUBE' +select product, country , year, sum(profit) from sales group by product, country, year with rollup; +This version of MySQL doesn't yet support 'ROLLUP' +explain select product, country , year, sum(profit) from sales group by product, country, year with rollup; +This version of MySQL doesn't yet support 'ROLLUP' +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; +This version of MySQL doesn't yet support 'CUBE' +drop table sales; diff --git a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result index 563aa178c87..64e2c9f5240 100644 --- a/mysql-test/r/order_by.result +++ b/mysql-test/r/order_by.result @@ -471,3 +471,38 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 2 t1.skr 1 where used drop table t1,t2,t3; +CREATE TABLE t1 ( +`titre` char(80) NOT NULL default '', +`numeropost` mediumint(8) unsigned NOT NULL auto_increment, +`date` datetime NOT NULL default '0000-00-00 00:00:00', +`auteur` char(35) NOT NULL default '', +`icone` tinyint(2) unsigned NOT NULL default '0', +`lastauteur` char(35) NOT NULL default '', +`nbrep` smallint(6) unsigned NOT NULL default '0', +`dest` char(35) NOT NULL default '', +`lu` tinyint(1) unsigned NOT NULL default '0', +`vue` mediumint(8) unsigned NOT NULL default '0', +`ludest` tinyint(1) unsigned NOT NULL default '0', +`ouvert` tinyint(1) unsigned NOT NULL default '1', +PRIMARY KEY (`numeropost`), +KEY `date` (`date`), +KEY `dest` (`dest`,`ludest`), +KEY `auteur` (`auteur`,`lu`), +KEY `auteur_2` (`auteur`,`date`), +KEY `dest_2` (`dest`,`date`) +) CHECKSUM=1; +CREATE TABLE t2 ( +`numeropost` mediumint(8) unsigned NOT NULL default '0', +`pseudo` char(35) NOT NULL default '', +PRIMARY KEY (`numeropost`,`pseudo`), +KEY `pseudo` (`pseudo`) +); +INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug'); +INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug'); +SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30; +titre numeropost auteur icone nbrep 0 date vue ouvert lastauteur dest +test 1 joce 0 0 0 0000-00-00 00:00:00 0 1 bug +drop table t1,t2; diff --git a/mysql-test/r/query_cache.result b/mysql-test/r/query_cache.result index af14575a812..7c53074b6da 100644 --- a/mysql-test/r/query_cache.result +++ b/mysql-test/r/query_cache.result @@ -226,7 +226,7 @@ show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2, t3, t11, t21; -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; @@ -242,7 +242,7 @@ a 1 2 3 -set sql_query_cache_type=2; +set query_cache_type=2; select sql_cache * from t1; a 1 @@ -254,7 +254,7 @@ Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 -set sql_query_cache_type=on; +set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; Variable_name Value @@ -490,3 +490,39 @@ drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +set GLOBAL query_cache_size=1000; +show global variables like "query_cache_size"; +Variable_name Value +query_cache_size 0 +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"; +Variable_name Value +query_cache_size 1024 +set GLOBAL query_cache_size=1048576; +create table t1 (i int not null); +create table t2 (i int not null); +select * from t1; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +create temporary table t3 (i int not null); +select * from t2; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +select * from t3; +i +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +drop table t1, t2, t3; diff --git a/mysql-test/r/repair.result b/mysql-test/r/repair.result new file mode 100644 index 00000000000..8b50f9a92e8 --- /dev/null +++ b/mysql-test/r/repair.result @@ -0,0 +1,7 @@ +drop table if exists t1; +create table t1 SELECT 1,"table 1"; +repair table t1 use_frm; +Table Op Msg_type Msg_text +test.t1 repair warning Number of rows changed from 0 to 1 +test.t1 repair status OK +drop table if exists t1; diff --git a/mysql-test/r/rollback.result b/mysql-test/r/rollback.result index a5eb6f8729f..d87aa68dce4 100644 --- a/mysql-test/r/rollback.result +++ b/mysql-test/r/rollback.result @@ -4,9 +4,26 @@ begin work; insert into t1 values (4); insert into t1 values (5); rollback; -Warning: Some non-transactional changed tables couldn't be rolled back +Warnings: +Warning 1196 Some non-transactional changed tables couldn't be rolled back +select @@warning_count; +@@warning_count +1 +select @@error_count; +@@error_count +0 +show warnings; +Level Code Message +Warning 1196 Some non-transactional changed tables couldn't be rolled back +show errors; +Level Code Message select * from t1; n 4 5 +select @@warning_count; +@@warning_count +0 +show warnings; +Level Code Message drop table t1; diff --git a/mysql-test/r/rpl000001.result b/mysql-test/r/rpl000001.result index 60a5db1de3a..2dc21e86152 100644 --- a/mysql-test/r/rpl000001.result +++ b/mysql-test/r/rpl000001.result @@ -1,10 +1,10 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -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; load data local infile 'MYSQL_TEST_DIR/std_data/words.dat' into table t1; @@ -30,11 +30,12 @@ abandoned abandoning abandonment abandons +slave stop; set password for root@"localhost" = password('foo'); +slave start; set password for root@"localhost" = password(''); create table t3(n int); insert into t3 values(1),(2); -use test; select * from t3; n 1 @@ -46,7 +47,6 @@ drop table t1,t3; reset master; slave stop; reset slave; -drop table if exists t1,t2; create table t1(n int); select get_lock("hold_slave",10); get_lock("hold_slave",10) @@ -58,8 +58,8 @@ release_lock("hold_slave") unlock tables; create table t2(id int); insert into t2 values(connection_id()); -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; +create temporary table t3(n int); +insert into t3 select get_lock('crash_lock%20C', 1) from t2; update t1 set n = n + get_lock('crash_lock%20C', 2); select (@id := id) - id from t2; (@id := id) - id @@ -67,7 +67,7 @@ select (@id := id) - id from t2; kill @id; drop table t2; Server shutdown in progress -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; select count(*) from t1; count(*) @@ -75,17 +75,15 @@ count(*) 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'; select_priv user N 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'; select_priv user Y blafasel2 -use test; select n from t1; n 3456 diff --git a/mysql-test/r/rpl000002.result b/mysql-test/r/rpl000002.result index 88228321897..4c2b3bdfde6 100644 --- a/mysql-test/r/rpl000002.result +++ b/mysql-test/r/rpl000002.result @@ -1,14 +1,13 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -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); -use test; select * from t1; n 2000 @@ -16,7 +15,7 @@ n 2002 show slave hosts; Server_id Host Port Rpl_recovery_rank Master_id -2 127.0.0.1 $SLAVE_MYPORT 2 1 +2 127.0.0.1 9999 2 1 drop table t1; slave stop; drop table if exists t2; diff --git a/mysql-test/r/rpl000003.result b/mysql-test/r/rpl000003.result index eb64d1855b1..b123b3d98c5 100644 --- a/mysql-test/r/rpl000003.result +++ b/mysql-test/r/rpl000003.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000004.result b/mysql-test/r/rpl000004.result index e5dee880f73..82b208d0b58 100644 --- a/mysql-test/r/rpl000004.result +++ b/mysql-test/r/rpl000004.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; set SQL_LOG_BIN=0; drop table if exists t1; create table t1 (word char(20) not null, index(word)); @@ -12,7 +12,6 @@ drop table if exists t2; 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); -use test; drop table if exists t1; load table t1 from master; drop table if exists t2; diff --git a/mysql-test/r/rpl000005.result b/mysql-test/r/rpl000005.result index 0c91f984db1..3e9028bf2cf 100644 --- a/mysql-test/r/rpl000005.result +++ b/mysql-test/r/rpl000005.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000006.result b/mysql-test/r/rpl000006.result index f4e965236af..e7fc5151ac4 100644 --- a/mysql-test/r/rpl000006.result +++ b/mysql-test/r/rpl000006.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000007.result b/mysql-test/r/rpl000007.result index 7b2b9939228..c2823bf1203 100644 --- a/mysql-test/r/rpl000007.result +++ b/mysql-test/r/rpl000007.result @@ -1,13 +1,12 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists foo; create table foo (n int); insert into foo values(4); -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/r/rpl000008.result b/mysql-test/r/rpl000008.result index 5ca156fecf0..a0230d55702 100644 --- a/mysql-test/r/rpl000008.result +++ b/mysql-test/r/rpl000008.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000009.result b/mysql-test/r/rpl000009.result index 5246dadc06b..afd566c366c 100644 --- a/mysql-test/r/rpl000009.result +++ b/mysql-test/r/rpl000009.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000010.result b/mysql-test/r/rpl000010.result index 95792a72a6d..49538ed148e 100644 --- a/mysql-test/r/rpl000010.result +++ b/mysql-test/r/rpl000010.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000011.result b/mysql-test/r/rpl000011.result index 1a46f479677..5d22c29bdba 100644 --- a/mysql-test/r/rpl000011.result +++ b/mysql-test/r/rpl000011.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists t1; create table t1 (n int); insert into t1 values(1); diff --git a/mysql-test/r/rpl000012.result b/mysql-test/r/rpl000012.result index c7f3ab8f578..45de6502bbd 100644 --- a/mysql-test/r/rpl000012.result +++ b/mysql-test/r/rpl000012.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000013.result b/mysql-test/r/rpl000013.result index 5e4b4159e3a..9e802da59c5 100644 --- a/mysql-test/r/rpl000013.result +++ b/mysql-test/r/rpl000013.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl000015.result b/mysql-test/r/rpl000015.result index a35004298e0..7010349e5ff 100644 --- a/mysql-test/r/rpl000015.result +++ b/mysql-test/r/rpl000015.result @@ -5,7 +5,6 @@ master-bin.001 79 reset slave; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space - 0 0 0 0 No No 0 0 0 0 change master to master_host='127.0.0.1'; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space diff --git a/mysql-test/r/rpl000017.result b/mysql-test/r/rpl000017.result index e2c3c089378..bac0573165d 100644 --- a/mysql-test/r/rpl000017.result +++ b/mysql-test/r/rpl000017.result @@ -1,6 +1,6 @@ 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'; slave start; drop table if exists t1; create table t1(n int); diff --git a/mysql-test/r/rpl_alter.result b/mysql-test/r/rpl_alter.result new file mode 100644 index 00000000000..1dc73c6524a --- /dev/null +++ b/mysql-test/r/rpl_alter.result @@ -0,0 +1,21 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +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; +select * from d1.t2; +n m +1 2 +select * from d1.t3; +n +45 +drop database d1; diff --git a/mysql-test/r/rpl_compat.result b/mysql-test/r/rpl_compat.result deleted file mode 100644 index 5e497791771..00000000000 --- a/mysql-test/r/rpl_compat.result +++ /dev/null @@ -1,77 +0,0 @@ -slave stop; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -slave start; -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; -word -Aarhus -Aaron -Ababa -aback -abaft -abandon -abandoned -abandoning -abandonment -abandons -set password for root@"localhost" = password('foo'); -set password for root@"localhost" = password(''); -create table t3(n int); -insert into t3 values(1),(2); -use test; -select * from t3; -n -1 -2 -select sum(length(word)) from t1; -sum(length(word)) -71 -drop table t1,t3; -reset master; -slave stop; -reset slave; -create table t1(n int); -insert into t1 values (1),(2),(3); -create table t2(id int); -insert into t2 values(connection_id()); -create temporary table t1_temp(n int); -insert into t1_temp select get_lock('crash_lock%20C', 1) from t2; - update t1 set n = n + get_lock('crash_lock%20C', 2); -select (@id := id) - id from t2; -(@id := id) - id -0 -kill @id; -drop table t2; -Server shutdown in progress -slave start; -set sql_slave_skip_counter=1; -slave start; -select count(*) from t1; -count(*) -3 -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'; -select_priv user -N blafasel2 -update user set Select_priv = "Y" where User="blafasel2"; -select select_priv,user from mysql.user where user = 'blafasel2'; -select_priv user -Y blafasel2 -use test; -select n from t1; -n -3456 -select select_priv,user from mysql.user where user = 'blafasel2'; -select_priv user -Y blafasel2 -drop table t1; diff --git a/mysql-test/r/rpl_empty_master_crash.result b/mysql-test/r/rpl_empty_master_crash.result index 6ae5d15031b..19e2bf28dcd 100644 --- a/mysql-test/r/rpl_empty_master_crash.result +++ b/mysql-test/r/rpl_empty_master_crash.result @@ -1,12 +1,13 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -use test; drop table if exists t1; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space - 0 0 0 0 No No 0 0 0 0 load table t1 from master; -Error in fetch_master_table +Error connecting to master: Master is not configured +load table t1 from master; +Error from master: 'Table 'test.t1' doesn't exist' diff --git a/mysql-test/r/rpl_flush_log_loop.result b/mysql-test/r/rpl_flush_log_loop.result new file mode 100644 index 00000000000..da2930f30c3 --- /dev/null +++ b/mysql-test/r/rpl_flush_log_loop.result @@ -0,0 +1,17 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=MASTER_PORT; +slave start; +slave stop; +change master to master_host='127.0.0.1',master_user='root', +master_password='',master_port=SLAVE_PORT; +slave start; +flush logs; +show slave status; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +127.0.0.1 root SLAVE_PORT 60 slave-bin.001 79 relay-log.001 119 slave-bin.001 Yes Yes 0 0 79 119 diff --git a/mysql-test/r/rpl_get_lock.result b/mysql-test/r/rpl_get_lock.result index c86b8f830a5..a8e602be03f 100644 --- a/mysql-test/r/rpl_get_lock.result +++ b/mysql-test/r/rpl_get_lock.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -17,4 +18,13 @@ get_lock("lock",3) select * from t1; n 1 +select is_free_lock("lock"); +is_free_lock("lock") +0 +select is_free_lock("lock2"); +is_free_lock("lock2") +1 +select is_free_lock(NULL); +is_free_lock(NULL) +NULL drop table t1; diff --git a/mysql-test/r/rpl_log.result b/mysql-test/r/rpl_log.result index f5885085dee..b6e1419b466 100644 --- a/mysql-test/r/rpl_log.result +++ b/mysql-test/r/rpl_log.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -7,7 +8,6 @@ slave stop; reset master; reset slave; reset master; -drop table if exists t1; create table t1(n int not null auto_increment primary key); insert into t1 values (NULL); drop table t1; @@ -17,46 +17,48 @@ drop table t1; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) -master-bin.001 263 Query 1 263 use test; drop table t1 -master-bin.001 311 Query 1 311 use test; create table t1 (word char(20) not null) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +master-bin.001 263 Query 1 263 use `test`; drop table t1 +master-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 master-bin.001 556 Exec_load 1 556 ;file_id=1 -master-bin.001 579 Query 1 579 use test; drop table t1 +master-bin.001 579 Query 1 579 use `test`; drop table t1 show binlog events from 79 limit 1; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) show binlog events from 79 limit 2; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 show binlog events from 79 limit 2,1; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +flush logs; +slave start; flush logs; +slave stop; create table t1 (n int); insert into t1 values (1); drop table t1; show binlog events; Log_name Pos Event_type Server_id Orig_log_pos Info master-bin.001 4 Start 1 4 Server ver: VERSION, Binlog ver: 3 -master-bin.001 79 Query 1 79 use test; create table t1(n int not null auto_increment primary key) +master-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) master-bin.001 172 Intvar 1 172 INSERT_ID=1 -master-bin.001 200 Query 1 200 use test; insert into t1 values (NULL) -master-bin.001 263 Query 1 263 use test; drop table t1 -master-bin.001 311 Query 1 311 use test; create table t1 (word char(20) not null) +master-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +master-bin.001 263 Query 1 263 use `test`; drop table t1 +master-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) master-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 master-bin.001 556 Exec_load 1 556 ;file_id=1 -master-bin.001 579 Query 1 579 use test; drop table t1 +master-bin.001 579 Query 1 579 use `test`; drop table t1 master-bin.001 627 Rotate 1 627 master-bin.002;pos=4 -master-bin.001 668 Stop 1 668 show binlog events in 'master-bin.002'; Log_name Pos Event_type Server_id Orig_log_pos Info -master-bin.002 4 Query 1 4 use test; create table t1 (n int) -master-bin.002 62 Query 1 62 use test; insert into t1 values (1) -master-bin.002 122 Query 1 122 use test; drop table t1 +master-bin.002 4 Query 1 4 use `test`; create table t1 (n int) +master-bin.002 62 Query 1 62 use `test`; insert into t1 values (1) +master-bin.002 122 Query 1 122 use `test`; drop table t1 show master logs; Log_name master-bin.001 @@ -67,32 +69,24 @@ Log_name slave-bin.001 slave-bin.002 show binlog events in 'slave-bin.001' from 4; +Log_name Pos Event_type Server_id Orig_log_pos Info +slave-bin.001 4 Start 2 4 Server ver: VERSION, Binlog ver: 3 +slave-bin.001 79 Query 1 79 use `test`; create table t1(n int not null auto_increment primary key) +slave-bin.001 172 Intvar 1 200 INSERT_ID=1 +slave-bin.001 200 Query 1 200 use `test`; insert into t1 values (NULL) +slave-bin.001 263 Query 1 263 use `test`; drop table t1 +slave-bin.001 311 Query 1 311 use `test`; create table t1 (word char(20) not null) +slave-bin.001 386 Create_file 1 386 db=test;table=t1;file_id=1;block_len=81 +slave-bin.001 565 Exec_load 1 556 ;file_id=1 +slave-bin.001 588 Query 1 579 use `test`; drop table t1 +slave-bin.001 636 Rotate 2 636 slave-bin.002;pos=4 show binlog events in 'slave-bin.002' from 4; Log_name Pos Event_type Server_id Orig_log_pos Info -slave-bin.002 4 Slave 2 627 host=127.0.0.1,port=MASTER_PORT,log=master-bin.002,pos=4 -slave-bin.002 57 Query 1 4 use test; create table t1 (n int) -slave-bin.002 115 Query 1 62 use test; insert into t1 values (1) -slave-bin.002 175 Query 1 122 use test; drop table t1 +slave-bin.002 4 Query 1 4 use `test`; create table t1 (n int) +slave-bin.002 62 Query 1 62 use `test`; insert into t1 values (1) +slave-bin.002 122 Query 1 122 use `test`; drop table t1 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 916 master-bin.002 Yes Yes 0 0 170 924 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.001 132 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=79 and master_server_id=1; -Log_name Log_pos -slave-bin.001 225 -show new master for slave with master_log_file='master-bin.001' and -master_log_pos=311 and master_server_id=1; -Log_name Log_pos -slave-bin.001 439 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=4 and master_server_id=1; -Log_name Log_pos -slave-bin.002 57 -show new master for slave with master_log_file='master-bin.002' and -master_log_pos=122 and master_server_id=1; -Log_name Log_pos -slave-bin.002 223 +127.0.0.1 root MASTER_PORT 1 master-bin.002 170 slave-relay-bin.002 957 master-bin.002 Yes Yes 0 0 170 961 +show binlog events in 'slave-bin.005' from 4; +Error when executing command SHOW BINLOG EVENTS: Could not find target log diff --git a/mysql-test/r/rpl000014.result b/mysql-test/r/rpl_log_pos.result index c9926c92a4f..6883da76180 100644 --- a/mysql-test/r/rpl000014.result +++ b/mysql-test/r/rpl_log_pos.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; @@ -8,7 +9,7 @@ File Position Binlog_do_db Binlog_ignore_db master-bin.001 79 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 128 +127.0.0.1 root MASTER_PORT 1 master-bin.001 79 slave-relay-bin.002 120 master-bin.001 Yes Yes 0 0 79 124 change master to master_log_pos=73; slave stop; change master to master_log_pos=73; @@ -18,22 +19,24 @@ Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Lo slave start; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 4 master-bin.001 Yes Yes 0 0 73 4 +127.0.0.1 root MASTER_PORT 1 master-bin.001 73 slave-relay-bin.001 4 master-bin.001 No Yes 0 0 73 4 change master to master_log_pos=173; +slave start; show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 1 master-bin.001 173 slave-relay-bin.001 4 master-bin.001 Yes Yes 0 0 173 4 +127.0.0.1 root MASTER_PORT 1 master-bin.001 173 slave-relay-bin.001 4 master-bin.001 No Yes 0 0 173 4 show master status; File Position Binlog_do_db Binlog_ignore_db master-bin.001 79 -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); change master to master_log_pos=79; -select * from foo; +slave start; +select * from t1; n 1 2 3 -drop table foo; +drop table t1; diff --git a/mysql-test/r/rpl_magic.result b/mysql-test/r/rpl_magic.result deleted file mode 100644 index b30d40d04eb..00000000000 --- a/mysql-test/r/rpl_magic.result +++ /dev/null @@ -1,36 +0,0 @@ -slave stop; -reset master; -reset slave; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -slave start; -drop table if exists t1; -create table t1 ( n int); -insert into t1 values (1),(2),(3),(4); -insert into t1 values(5); -select * from t1; -n -1 -2 -3 -4 -5 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -select * from t1; -n -1 -2 -3 -4 -5 -drop table t1; diff --git a/mysql-test/r/rpl_mystery22.result b/mysql-test/r/rpl_mystery22.result index e798d774380..5dd665fe9d5 100644 --- a/mysql-test/r/rpl_mystery22.result +++ b/mysql-test/r/rpl_mystery22.result @@ -1,5 +1,5 @@ -drop table if exists t1; slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl_redirect.result b/mysql-test/r/rpl_redirect.result new file mode 100644 index 00000000000..6103a075684 --- /dev/null +++ b/mysql-test/r/rpl_redirect.result @@ -0,0 +1,43 @@ +slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +reset master; +reset slave; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; +slave start; +SHOW SLAVE STATUS; +Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space +SHOW SLAVE HOSTS; +Server_id Host Port Rpl_recovery_rank Master_id +2 127.0.0.1 SLAVE_PORT 2 1 +drop table if exists t1; +create table t1 ( n int); +insert into t1 values (1),(2),(3),(4); +insert into t1 values(5); +select * from t1; +n +1 +2 +3 +4 +5 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +select * from t1; +n +1 +2 +3 +4 +5 +drop table t1; +drop table t1; diff --git a/mysql-test/r/rpl000016.result b/mysql-test/r/rpl_rotate_logs.result index 9d6ba071200..63d5b0b63e1 100644 --- a/mysql-test/r/rpl000016.result +++ b/mysql-test/r/rpl_rotate_logs.result @@ -2,20 +2,20 @@ slave start; Could not initialize master info structure, check permisions on master.info slave start; Could not initialize master info structure, check permisions on master.info -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; Could not initialize master info reset slave; -change master to master_host='127.0.0.1',master_port=MASTER_PORT, -master_user='root'; +change master to master_host='127.0.0.1',master_port=MASTER_PORT, master_user='root'; reset master; slave start; -drop table if exists t1; +drop table if exists t1, t2, t3, t4; +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'); show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.001 234 slave-relay-bin.001 275 master-bin.001 Yes Yes 0 0 234 275 +127.0.0.1 root MASTER_PORT 60 master-bin.001 417 slave-relay-bin.001 458 master-bin.001 Yes Yes 0 0 417 458 select * from t1; s Could not break slave @@ -30,10 +30,15 @@ Log_name master-bin.001 master-bin.002 master-bin.003 +create table t3 select * from temp_table; +select * from t3; +a +testing temporary tables +drop table temp_table, t3; insert into t2 values(1234); set insert_id=1234; insert into t2 values(NULL); -set sql_slave_skip_counter=1; +set global sql_slave_skip_counter=1; slave start; purge master logs to 'master-bin.003'; show master logs; @@ -42,7 +47,7 @@ master-bin.003 insert into t2 values (65); show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.003 155 slave-relay-bin.001 755 master-bin.003 Yes Yes 0 0 155 755 +127.0.0.1 root MASTER_PORT 60 master-bin.003 290 slave-relay-bin.001 1073 master-bin.003 Yes Yes 0 0 290 1073 select * from t2; m 34 @@ -50,8 +55,11 @@ m 67 123 1234 +create temporary table temp_table (a char(80) not null); +insert into temp_table values ("testing temporary tables part 2"); drop table if exists t3; create table t3 (n int); +create table t4 select * from temp_table; show master logs; Log_name master-bin.003 @@ -60,15 +68,16 @@ master-bin.005 master-bin.006 show master status; File Position Binlog_do_db Binlog_ignore_db -master-bin.006 445 -slave stop; -slave start; +master-bin.006 838 +select * from t4; +a +testing temporary tables part 2 show slave status; Master_Host Master_User Master_Port Connect_retry Master_Log_File Read_Master_Log_Pos Relay_Log_File Relay_Log_Pos Relay_Master_Log_File Slave_IO_Running Slave_SQL_Running Replicate_do_db Replicate_ignore_db Last_errno Last_error Skip_counter Exec_master_log_pos Relay_log_space -127.0.0.1 root MASTER_PORT 60 master-bin.006 445 slave-relay-bin.004 1229 master-bin.006 Yes Yes 0 0 445 1229 +127.0.0.1 root MASTER_PORT 60 master-bin.006 838 slave-relay-bin.001 8034 master-bin.006 Yes Yes 0 0 838 8034 lock tables t3 read; select count(*) from t3 where n >= 4; count(*) 100 unlock tables; -drop table if exists t1,t2,t3; +drop table if exists t1,t2,t3,t4; diff --git a/mysql-test/r/rpl_skip_error.result b/mysql-test/r/rpl_skip_error.result index 9fe92292178..946d64ad7c5 100644 --- a/mysql-test/r/rpl_skip_error.result +++ b/mysql-test/r/rpl_skip_error.result @@ -1,4 +1,5 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; diff --git a/mysql-test/r/rpl_sporadic_master.result b/mysql-test/r/rpl_sporadic_master.result index ca74904889b..a6a58515f0a 100644 --- a/mysql-test/r/rpl_sporadic_master.result +++ b/mysql-test/r/rpl_sporadic_master.result @@ -1,9 +1,9 @@ slave stop; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; slave start; -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/r/select.result b/mysql-test/r/select.result index 8c7f3326912..fc918012d1f 100644 --- a/mysql-test/r/select.result +++ b/mysql-test/r/select.result @@ -1718,7 +1718,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -1731,7 +1731,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -1768,7 +1768,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=1; +SET SQL_BIG_TABLES=1; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -1781,7 +1781,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET OPTION SQL_BIG_TABLES=0; +SET SQL_BIG_TABLES=0; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 @@ -1809,6 +1809,18 @@ explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL fld1 NULL NULL NULL 1199 where used; Using temporary; Using filesort 1 SIMPLE t3 eq_ref PRIMARY PRIMARY 4 t2.fld1 1 where used; Using index +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL period NULL NULL NULL 41810 Using temporary; Using filesort +1 SIMPLE t3 ref period period 4 t1.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t3 index period period 4 NULL 41810 +1 SIMPLE t1 ref period period 4 t3.period 4181 +explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index period period 4 NULL 41810 +1 SIMPLE t3 ref period period 4 t1.period 4181 select period from t1; period 9410 @@ -3229,6 +3241,28 @@ Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_par t2 0 PRIMARY 1 auto A 1199 NULL NULL BTREE t2 0 fld1 1 fld1 A 1199 NULL NULL BTREE t2 1 fld3 1 fld3 A NULL NULL NULL BTREE +drop table t4, t3, t2, t1; DO 1; DO benchmark(100,1+1),1,1; -drop table t4, t3,t2, t1; +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; +gvid the_success the_fail the_size the_time +DROP TABLE t1,t2; +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'; +wss_type +select wss_type from t1 where wss_type ='102935229216544105'; +wss_type +select wss_type from t1 where wss_type ='102935229216544104'; +wss_type +select wss_type from t1 where wss_type ='102935229216544093'; +wss_type +102935229216544093 +select wss_type from t1 where wss_type =102935229216544093; +wss_type +102935229216544093 +drop table t1; diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result index 3d2f2154b37..1c2cd7da894 100644 --- a/mysql-test/r/select_found.result +++ b/mysql-test/r/select_found.result @@ -66,7 +66,7 @@ a 2 select FOUND_ROWS(); FOUND_ROWS() -5 +3 drop table t1; CREATE TABLE t1 ( `id` smallint(5) unsigned NOT NULL auto_increment, @@ -153,3 +153,19 @@ SELECT FOUND_ROWS(); FOUND_ROWS() 199 drop table t1,t2; +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; +titre numeropost maxnumrep +test2 2 2 +SELECT FOUND_ROWS(); +FOUND_ROWS() +2 +drop table t1; diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result index 1f83bc61cdc..462abb29979 100644 --- a/mysql-test/r/show_check.result +++ b/mysql-test/r/show_check.result @@ -43,7 +43,7 @@ wait_timeout 28800 show variables like "this_doesn't_exists%"; Variable_name Value show table status from test like "this_doesn't_exists%"; -Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Create_options Comment +Name Type Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Charset Create_options Comment show databases; Database mysql @@ -77,13 +77,13 @@ show create table t1; Table Create Table t1 CREATE TEMPORARY TABLE `t1` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 alter table t1 rename t2; show create table t2; Table Create Table t2 CREATE TEMPORARY TABLE `t2` ( `a` int(11) NOT NULL default '0' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t2; create table t1 ( test_set set( 'val1', 'val2', 'val3' ) not null default '', @@ -96,7 +96,7 @@ t1 CREATE TABLE `t1` ( `test_set` set('val1','val2','val3') NOT NULL default '', `name` char(20) character set latin1 default 'O''Brien' COMMENT 'O''Brien as default', `c` int(11) NOT NULL default '0' COMMENT 'int column' -) TYPE=MyISAM COMMENT='it''s a table' +) TYPE=MyISAM CHARSET=latin1 COMMENT='it''s a table' show full columns from t1; Field Type Null Key Default Extra Privileges Comment test_set set('val1','val2','val3') select,insert,update,references @@ -109,7 +109,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', UNIQUE KEY `aa` (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a int not null, primary key (a)); show create table t1; @@ -117,7 +117,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; flush tables; show open tables; @@ -135,7 +135,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` char(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' +) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=100 AVG_ROW_LENGTH=10 PACK_KEYS=1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=FIXED COMMENT='test' alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0; show create table t1; Table Create Table @@ -143,7 +143,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' +) TYPE=MyISAM CHARSET=latin1 MIN_ROWS=10 MAX_ROWS=200 AVG_ROW_LENGTH=10 PACK_KEYS=0 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC COMMENT='test' ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default; show create table t1; Table Create Table @@ -151,7 +151,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL default '0', `b` varchar(10) character set latin1 default NULL, KEY `b` (`b`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0)); show columns from t1; diff --git a/mysql-test/r/symlink.result b/mysql-test/r/symlink.result index b5b5b774ee5..1eb595eb2a9 100644 --- a/mysql-test/r/symlink.result +++ b/mysql-test/r/symlink.result @@ -39,7 +39,7 @@ t9 CREATE TABLE `t9` ( `b` char(16) character set latin1 NOT NULL default '', `c` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 alter table t9 rename t8, add column d int not null; alter table t8 rename t7; rename table t7 to t9; @@ -61,5 +61,5 @@ t9 CREATE TABLE `t9` ( `c` int(11) NOT NULL default '0', `d` int(11) NOT NULL default '0', PRIMARY KEY (`a`) -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop database test_mysqltest; diff --git a/mysql-test/r/truncate.result b/mysql-test/r/truncate.result index e02797cc8e5..ad390c9fa92 100644 --- a/mysql-test/r/truncate.result +++ b/mysql-test/r/truncate.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a integer, b integer,c1 CHAR(10)); insert into t1 (a) values (1),(2); truncate table t1; @@ -20,3 +21,5 @@ truncate table t1; select * from t1; n drop table t1; +truncate non_existing_table; +Table 'test.non_existing_table' doesn't exist diff --git a/mysql-test/r/type_decimal.result b/mysql-test/r/type_decimal.result index f4dfc3c2891..6550e981f5e 100644 --- a/mysql-test/r/type_decimal.result +++ b/mysql-test/r/type_decimal.result @@ -156,6 +156,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; a 0.00 @@ -176,6 +178,14 @@ a -99999999.99 999999999.99 999999999.99 +999999999.99 +0.00 +-99999999.99 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal(10,2) unsigned); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); @@ -183,19 +193,21 @@ 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; a 0.00 0.00 -0.00 -01.00 ++0.00 01.00 ++01.00 0.00 0.00 -0.10 ++0.10 0.10 00000001.00 -00000001.00 ++0000001.00 0.00 99999999.99 99999999.99 @@ -203,6 +215,14 @@ a 0.00 99999999.99 99999999.99 +99999999.99 +0.00 +0.00 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal(10,2) zerofill); insert into t1 values ("0.0"),("-0.0"),("+0.0"),("01.0"),("+01.0"),("-01.0"); @@ -210,6 +230,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; a 00000000.00 @@ -230,6 +252,14 @@ a 00000000.00 99999999.99 99999999.99 +99999999.99 +00000000.00 +00000000.00 +00000123.40 +00012340.00 +00000001.23 +00001230.00 +00000123.00 drop table t1; create table t1 (a decimal(10,2)); insert into t1 values (0.0),("-0.0"),(+0.0),(01.0),(+01.0),(-01.0); @@ -237,6 +267,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; a 0.00 @@ -257,6 +289,14 @@ a -99999999.99 999999999.99 999999999.99 +999999999.99 +0.00 +-99999999.99 +123.40 +12340.00 +1.23 +1230.00 +123.00 drop table t1; create table t1 (a decimal); insert into t1 values (-99999999999999),(-1),('+1'),('01'),('+00000000000001'),('+12345678901'),(99999999999999); @@ -276,9 +316,9 @@ select * from t1; a 0 0 -1 ++1 01 -0000000001 ++000000001 1234567890 9999999999 drop table t1; diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index ee3bd077798..c0f0be246c9 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1627,12 +1627,12 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('','a','b') NOT NULL default '' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a enum (' ','a','b ') not null default 'b '); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('','a','b') NOT NULL default 'b' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/type_set.result b/mysql-test/r/type_set.result index 858bf5f3ba2..b0ea1b69e59 100644 --- a/mysql-test/r/type_set.result +++ b/mysql-test/r/type_set.result @@ -1,14 +1,15 @@ +drop table if exists t1; create table t1 (a set (' ','a','b') not null); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('','a','b') NOT NULL default '' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; create table t1 (a set (' ','a','b ') not null default 'b '); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('','a','b') NOT NULL default 'b' -) TYPE=MyISAM +) TYPE=MyISAM CHARSET=latin1 drop table t1; diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result index ff700f456ef..bacc3ec0176 100644 --- a/mysql-test/r/type_time.result +++ b/mysql-test/r/type_time.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (t time); insert into t1 values("10:22:33"),("12:34:56.78"),(10),(1234),(123456.78),(1234559.99),("1"),("1:23"),("1:23:45"), ("10.22"), ("-10 1:22:33.45"),("20 10:22:33"),("1999-02-03 20:33:34"); insert t1 values (30),(1230),("1230"),("12:30"),("12:30:35"),("1 12:30:31.32"); diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index ab53d27e8f3..bd5e9f04992 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -1,3 +1,4 @@ +drop table if exists t1; CREATE TABLE t1 ( t timestamp); SET TIMESTAMP=1234; insert into t1 values(NULL); diff --git a/mysql-test/r/type_year.result b/mysql-test/r/type_year.result index c892d575ca0..25ddda88f48 100644 --- a/mysql-test/r/type_year.result +++ b/mysql-test/r/type_year.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (y year,y2 year(2)); insert into t1 values (0,0),(1999,1999),(2000,2000),(2001,2001),(70,70),(69,69); select * from t1; diff --git a/mysql-test/r/union.result b/mysql-test/r/union.result index c64ee2ecd78..8ad0d8c6589 100644 --- a/mysql-test/r/union.result +++ b/mysql-test/r/union.result @@ -124,6 +124,8 @@ select * from t1 union select a from t2; The used SELECT statements have a different number of columns select a from t1 union select * from t2; The used SELECT statements have a different number of columns +select * from t1 union select SQL_BUFFER_RESULT * from t2; +Wrong usage/placement of 'SQL_BUFFER_RESULT' 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; replace into t3 select a,b as c from t1 union all select a,b from t2; @@ -189,9 +191,9 @@ a 1 2 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); a 1 -2 +11 set SQL_SELECT_LIMIT=DEFAULT; drop table t1,t2; diff --git a/mysql-test/r/user_var.result b/mysql-test/r/user_var.result index 9b2c0316c18..7421c040766 100644 --- a/mysql-test/r/user_var.result +++ b/mysql-test/r/user_var.result @@ -1,5 +1,5 @@ set @a := foo; -You may only use constant expressions with SET +Unknown column 'foo' in 'field list' set @a := connection_id() + 3; select @a - connection_id(); @a - connection_id() diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result index 1b6cf5e708e..7ce0f78da76 100644 --- a/mysql-test/r/varbinary.result +++ b/mysql-test/r/varbinary.result @@ -7,6 +7,7 @@ select 0x31+1,concat(0x31)+1,-0xf; select x'31',X'ffff'+0; x'31' X'ffff'+0 1 65535 +drop table if exists t1; create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) ); insert into t1 set UNIQ=0x38afba1d73e6a18a; insert into t1 set UNIQ=123; @@ -15,7 +16,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 const UNIQ UNIQ 8 const 1 drop table t1; select x'hello'; -You have an error in your SQL syntax near 'x'hello'' at line 1 +You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1 select 0xfg; Unknown column '0xfg' in 'field list' create table t1 select 1 as x, 2 as xx; diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result index 52c2c026bd3..f708ddd2ee7 100644 --- a/mysql-test/r/variables.result +++ b/mysql-test/r/variables.result @@ -44,17 +44,292 @@ c_id c_name c_country 1 Bozo USA 4 Mr. Floppy GB drop table t1; +set max_join_size=100; +show variables like 'max_join_size'; +Variable_name Value +max_join_size 100 +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 4294967295 +set GLOBAL max_join_size=2000; +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 2000 +set max_join_size=DEFAULT; +show variables like 'max_join_size'; +Variable_name Value +max_join_size 2000 +set GLOBAL max_join_size=DEFAULT; +show global variables like 'max_join_size'; +Variable_name Value +max_join_size 4294967295 +set @@max_join_size=1000, @@global.max_join_size=2000; +select @@local.max_join_size, @@global.max_join_size; +@@session.max_join_size @@global.max_join_size +1000 2000 +select @@identity, length(@@version)>0; +@@identity length(@@version)>0 +0 1 select @@VERSION=version(); @@VERSION=version() 1 select last_insert_id(345); last_insert_id(345) 345 -select @@IDENTITY,last_insert_id(); -@@IDENTITY last_insert_id() -345 345 -select @@identity; -@@IDENTITY -345 -select @@unknown_variable; +select @@IDENTITY,last_insert_id(), @@identity; +@@identity last_insert_id() @@identity +345 345 345 +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'; +Variable_name Value +concurrent_insert ON +set global concurrent_insert=1; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert ON +set global concurrent_insert=0; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert OFF +set global concurrent_insert=OFF; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert OFF +set global concurrent_insert=DEFAULT; +show variables like 'concurrent_insert'; +Variable_name Value +concurrent_insert ON +set table_type=MYISAM, table_type="HEAP", global table_type="INNODB"; +show local variables like 'table_type'; +Variable_name Value +table_type HEAP +show global variables like 'table_type'; +Variable_name Value +table_type INNODB +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'; +Variable_name Value +myisam_max_sort_file_size 10000 +show global variables like 'myisam_max_sort_file_size'; +Variable_name Value +myisam_max_sort_file_size 20000 +set myisam_max_sort_file_size=default; +show variables like 'myisam_max_sort_file_size'; +Variable_name Value +myisam_max_sort_file_size 20000 +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_%'; +Variable_name Value +net_buffer_length 1024 +net_read_timeout 300 +net_retry_count 10 +net_write_timeout 200 +show session variables like 'net_%'; +Variable_name Value +net_buffer_length 2048 +net_read_timeout 600 +net_retry_count 10 +net_write_timeout 500 +set session net_buffer_length=8000, global net_read_timeout=900, net_write_timeout=1000; +show global variables like 'net_%'; +Variable_name Value +net_buffer_length 1024 +net_read_timeout 900 +net_retry_count 10 +net_write_timeout 1000 +show session variables like 'net_%'; +Variable_name Value +net_buffer_length 7168 +net_read_timeout 600 +net_retry_count 10 +net_write_timeout 500 +set net_buffer_length=1; +show variables like 'net_buffer_length'; +Variable_name Value +net_buffer_length 1024 +set net_buffer_length=2000000000; +show variables like 'net_buffer_length'; +Variable_name Value +net_buffer_length 1048576 +set GLOBAL character set cp1251_koi8; +show global variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +set character set cp1251_koi8; +show variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +set global character set default, session character set default; +show variables like "convert_character_set"; +Variable_name Value +convert_character_set cp1251_koi8 +select @@timestamp>0; +@@timestamp>0 +1 +set big_tables=OFFF; +Variable 'big_tables' can't be set to the value of 'OFFF' +set big_tables="OFFF"; +Variable 'big_tables' can't be set to the value of 'OFFF' +set unknown_variable=1; Unknown system variable 'unknown_variable' +set max_join_size="hello"; +Wrong argument type to variable 'max_join_size' +set table_type=UNKNOWN_TABLE_TYPE; +Variable 'table_type' can't be set to the value of 'UNKNOWN_TABLE_TYPE' +set table_type=INNODB, big_tables=2; +Variable 'big_tables' can't be set to the value of '2' +show local variables like 'table_type'; +Variable_name Value +table_type HEAP +set SESSION query_cache_size=10000; +Variable 'query_cache_size' is a GLOBAL variable and should be set with SET GLOBAL +set GLOBAL table_type=DEFAULT; +Variable 'table_type' doesn't have a default value +set convert_character_set=UNKNOWN_CHARACTER_SET; +Unknown character set: 'UNKNOWN_CHARACTER_SET' +set character set unknown; +Unknown character set: 'unknown' +set character set 0; +Wrong argument type to variable 'convert_character_set' +set global autocommit=1; +Variable 'autocommit' is a LOCAL variable and can't be used with SET GLOBAL +select @@global.timestamp; +Variable 'timestamp' is a LOCAL variable and can't be used with SET GLOBAL +set @@version=''; +Unknown system variable 'version' +set @@concurrent_insert=1; +Variable 'concurrent_insert' is a GLOBAL variable and should be set with SET GLOBAL +set @@global.sql_auto_is_null=1; +Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +select @@global.sql_auto_is_null; +Variable 'sql_auto_is_null' is a LOCAL variable and can't be used with SET GLOBAL +set autocommit=1; +set big_tables=1; +select @@autocommit, @@big_tables; +@@autocommit @@big_tables +1 1 +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; +@@delay_key_write +ON +set global delay_key_write="OFF"; +select @@delay_key_write; +@@delay_key_write +OFF +set global delay_key_write=ALL; +select @@delay_key_write; +@@delay_key_write +ALL +set global delay_key_write=1; +select @@delay_key_write; +@@delay_key_write +ON +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; +@@max_user_connections +100 +set global max_write_lock_count=100; +set myisam_max_extra_sort_file_size=100; +select @@myisam_max_extra_sort_file_size; +@@myisam_max_extra_sort_file_size +100 +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; +@@sql_auto_is_null +1 +set @@sql_auto_is_null=0; +select @@sql_auto_is_null; +@@sql_auto_is_null +0 +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; +@@sql_max_join_size @@max_join_size +200 200 +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; +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; +@@key_buffer_size +98304 +select * from t1 where a=2; +a +2 +select * from t2 where a=3; +a +3 +check table t1,t2; +Table Op Msg_type Msg_text +test.t1 check status OK +test.t2 check status OK +drop table t1,t2; diff --git a/mysql-test/r/warnings.result b/mysql-test/r/warnings.result index d77e98cc635..e5713718db0 100644 --- a/mysql-test/r/warnings.result +++ b/mysql-test/r/warnings.result @@ -1,3 +1,4 @@ +drop table if exists t1; create table t1 (a int); insert into t1 values (1); insert into t1 values ("hej"); |