diff options
Diffstat (limited to 'mysql-test')
24 files changed, 293 insertions, 13 deletions
diff --git a/mysql-test/r/alias.result b/mysql-test/r/alias.result index 7f606367f39..587c21e9129 100644 --- a/mysql-test/r/alias.result +++ b/mysql-test/r/alias.result @@ -63,7 +63,7 @@ CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', PLNFL varchar(6) NOT NULL default '', VORNR varchar(4) NOT NULL default '', -xstatus_vor smallint(5) unsigned NOT NULL default '0', +xstatus_vor smallint(5) unsigned NOT NULL default '0' ); INSERT INTO t1 VALUES ('40004712','000001','0010',9); INSERT INTO t1 VALUES ('40004712','000001','0020',0); diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result index ce72c353d20..f2b33f2325e 100644 --- a/mysql-test/r/create.result +++ b/mysql-test/r/create.result @@ -274,6 +274,12 @@ create database `db1 `; ERROR 42000: Incorrect database name 'db1 ' create table t1(`a ` int); ERROR 42000: Incorrect column name 'a ' +create table t1 (a int,); +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 +create table t1 (a int,,b int); +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 'b int)' at line 1 +create table t1 (,b int); +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 'b int)' at line 1 create table t1 (a int, key(a)); create table t2 (b int, foreign key(b) references t1(a), key(b)); drop table if exists t1,t2; diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index bdba9e4c3e4..fb0cdbbb651 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -84,9 +84,9 @@ bbbb bb bbbbbbbb aaaa bbbb select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') this is a REAL test -select soundex(''),soundex('he'),soundex('hello all folks'); -soundex('') soundex('he') soundex('hello all folks') - H000 H4142 +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); +soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') + H000 H4142 I51231 select 'mood' sounds like 'mud'; 'mood' sounds like 'mud' 1 diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result index d2ea3c4a87c..b37df0aef2c 100644 --- a/mysql-test/r/func_time.result +++ b/mysql-test/r/func_time.result @@ -352,6 +352,36 @@ extract(SECOND FROM "1999-01-02 10:11:12") select extract(MONTH FROM "2001-02-00"); extract(MONTH FROM "2001-02-00") 2 +SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; +"1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND +1968-01-20 03:14:08 +SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND +1968-01-20 03:15:07 +SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND +8895-03-27 22:11:40 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; +"1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND +6255-04-08 15:04:32 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; +"1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE +5983-01-24 02:08:00 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; +"1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR +2019-08-15 16:00:00 +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; +"1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; +"1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; +"1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR +NULL +SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; +"1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND +NULL create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; diff --git a/mysql-test/r/innodb-lock.result b/mysql-test/r/innodb-lock.result new file mode 100644 index 00000000000..cf00adb30ae --- /dev/null +++ b/mysql-test/r/innodb-lock.result @@ -0,0 +1,22 @@ +drop table if exists t1; +create table t1 (id integer, x integer) engine=INNODB; +insert into t1 values(0, 0); +set autocommit=0; +SELECT * from t1 where id = 0 FOR UPDATE; +id x +0 0 +set autocommit=0; +lock table t1 write; +update t1 set x=1 where id = 0; +select * from t1; +id x +0 1 +commit; +update t1 set x=2 where id = 0; +commit; +unlock tables; +select * from t1; +id x +0 2 +commit; +drop table t1; diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result index 5e513061821..3c36571a8c2 100644 --- a/mysql-test/r/innodb.result +++ b/mysql-test/r/innodb.result @@ -467,7 +467,7 @@ ERROR 23000: Duplicate entry 'test2' for key 2 select * from t1; id ggid email passwd 1 this will work -3 test2 this will work +4 test2 this will work select * from t1 where id=1; id ggid email passwd 1 this will work diff --git a/mysql-test/r/raid.result b/mysql-test/r/raid.result index 690ef24d809..05013cc0d5d 100644 --- a/mysql-test/r/raid.result +++ b/mysql-test/r/raid.result @@ -4,6 +4,14 @@ create database test_$1; create table test_$1.r1 (i int) raid_type=1; create table test_$1.r2 (i int) raid_type=1 raid_chunks=32; drop database test_$1; +create database test_$1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=257; +show create table test_$1.r2; +Table Create Table +r2 CREATE TABLE `r2` ( + `i` int(11) default NULL +) TYPE=MyISAM RAID_TYPE=striped RAID_CHUNKS=255 RAID_CHUNKSIZE=256 +drop database test_$1; CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, c char(255) not null diff --git a/mysql-test/r/rpl_multi_delete2.result b/mysql-test/r/rpl_multi_delete2.result new file mode 100644 index 00000000000..8b6d87801fe --- /dev/null +++ b/mysql-test/r/rpl_multi_delete2.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; +create table t1 (a int); +create table t2 (a int); +insert into t1 values (1); +insert into t2 values (1); +delete t1.* from t1, t2 where t1.a = t2.a; +select * from t1; +a +select * from t2; +a +1 +select * from t1; +Table 'test.t1' doesn't exist +select * from t2; +Table 'test.t2' doesn't exist +drop table t1,t2; diff --git a/mysql-test/r/rpl_multi_update.result b/mysql-test/r/rpl_multi_update.result index 65587d25f63..34f99746c7d 100644 --- a/mysql-test/r/rpl_multi_update.result +++ b/mysql-test/r/rpl_multi_update.result @@ -6,7 +6,7 @@ drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, -b int unsigned, +b int unsigned ) ENGINE=MyISAM; CREATE TABLE t2 ( a int unsigned not null auto_increment primary key, diff --git a/mysql-test/r/rpl_server_id1.result b/mysql-test/r/rpl_server_id1.result new file mode 100644 index 00000000000..d8b23b9ca41 --- /dev/null +++ b/mysql-test/r/rpl_server_id1.result @@ -0,0 +1,23 @@ +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; +create table t1 (n int); +reset master; +stop slave; +change master to master_port=SLAVE_PORT; +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 1 4 slave-relay-bin.001 4 No No 0 0 0 # +start slave; +insert into t1 values (1); +show status like "slave_running"; +Variable_name Value +Slave_running ON +select * from t1; +n +1 +drop table t1; diff --git a/mysql-test/r/rpl_server_id2.result b/mysql-test/r/rpl_server_id2.result new file mode 100644 index 00000000000..5f4ec9df4c4 --- /dev/null +++ b/mysql-test/r/rpl_server_id2.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 table if exists t1; +create table t1 (n int); +reset master; +stop slave; +change master to master_port=SLAVE_PORT; +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 1 4 slave-relay-bin.001 4 No No 0 0 0 # +start slave; +insert into t1 values (1); +select * from t1; +n +1 +1 +drop table t1; diff --git a/mysql-test/t/alias.test b/mysql-test/t/alias.test index 852b78dd04e..986af339456 100644 --- a/mysql-test/t/alias.test +++ b/mysql-test/t/alias.test @@ -74,8 +74,7 @@ CREATE TABLE t1 ( AUFNR varchar(12) NOT NULL default '', PLNFL varchar(6) NOT NULL default '', VORNR varchar(4) NOT NULL default '', - xstatus_vor smallint(5) unsigned NOT NULL default '0', - + xstatus_vor smallint(5) unsigned NOT NULL default '0' ); INSERT INTO t1 VALUES ('40004712','000001','0010',9); diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test index 4bd92994530..30441fb9aae 100644 --- a/mysql-test/t/create.test +++ b/mysql-test/t/create.test @@ -242,6 +242,18 @@ create database `db1 `; create table t1(`a ` int); # +# Test for Bug #3481 +# "Parser permits multiple commas without syntax error" +# + +--error 1064 +create table t1 (a int,); +--error 1064 +create table t1 (a int,,b int); +--error 1064 +create table t1 (,b int); + +# # Test create with foreign keys # diff --git a/mysql-test/t/func_str.test b/mysql-test/t/func_str.test index 7efcac030af..24753c28073 100644 --- a/mysql-test/t/func_str.test +++ b/mysql-test/t/func_str.test @@ -39,7 +39,7 @@ SELECT CONCAT('"',CONCAT_WS('";"',repeat('a',60),repeat('b',60),repeat('c',60),r select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); select replace('aaaa','a','b'),replace('aaaa','aa','b'),replace('aaaa','a','bb'),replace('aaaa','','b'),replace('bbbb','a','c'); select replace(concat(lcase(concat('THIS',' ','IS',' ','A',' ')),ucase('false'),' ','test'),'FALSE','REAL') ; -select soundex(''),soundex('he'),soundex('hello all folks'); +select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); select 'mood' sounds like 'mud'; select 'Glazgo' sounds like 'Liverpool'; select null sounds like 'null'; @@ -146,7 +146,7 @@ CREATE TABLE t1 ( wid int(10) unsigned NOT NULL auto_increment, data_podp date default NULL, status_wnio enum('nowy','podp','real','arch') NOT NULL default 'nowy', - PRIMARY KEY(wid), + PRIMARY KEY(wid) ); INSERT INTO t1 VALUES (8,NULL,'real'); diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test index 4b8c26a00d8..a85f9d563e9 100644 --- a/mysql-test/t/func_time.test +++ b/mysql-test/t/func_time.test @@ -139,10 +139,27 @@ select extract(MINUTE_SECOND FROM "10:11:12"); select extract(SECOND FROM "1999-01-02 10:11:12"); select extract(MONTH FROM "2001-02-00"); +# +# Test big intervals (Bug #3498) +# +SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;SELECT "1900-01-01 00:00:00" + INTERVAL 1<<37 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<31 MINUTE; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<20 HOUR; + +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<38 SECOND; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<33 MINUTE; +SELECT "1900-01-01 00:00:00" + INTERVAL 1<<30 HOUR; +SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND; + +# +# Bug #614 (multiple extracts in where) +# + create table t1 (ctime varchar(20)); insert into t1 values ('2001-01-12 12:23:40'); select ctime, hour(ctime) from t1; -# test bug 614 (multiple extracts in where) select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001; drop table t1; diff --git a/mysql-test/t/innodb-lock.test b/mysql-test/t/innodb-lock.test new file mode 100644 index 00000000000..43a175508b4 --- /dev/null +++ b/mysql-test/t/innodb-lock.test @@ -0,0 +1,40 @@ +-- source include/have_innodb.inc + +connect (con1,localhost,root,,); +connect (con2,localhost,root,,); +drop table if exists t1; + +# +# Testing of explicit table locks +# + +connection con1; +create table t1 (id integer, x integer) engine=INNODB; +insert into t1 values(0, 0); +set autocommit=0; +SELECT * from t1 where id = 0 FOR UPDATE; + +connection con2; +set autocommit=0; + +# The following statement should hang because con1 is locking the page +--send +lock table t1 write; +--sleep 2; + +connection con1; +update t1 set x=1 where id = 0; +select * from t1; +commit; + +connection con2; +reap; +update t1 set x=2 where id = 0; +commit; +unlock tables; + +connection con1; +select * from t1; +commit; + +drop table t1; diff --git a/mysql-test/t/raid.test b/mysql-test/t/raid.test index 457262a8d75..14a55db0c34 100644 --- a/mysql-test/t/raid.test +++ b/mysql-test/t/raid.test @@ -21,6 +21,18 @@ create table test_$1.r1 (i int) raid_type=1; create table test_$1.r2 (i int) raid_type=1 raid_chunks=32; drop database test_$1; +# +# Bug #3182: Test using more than 257 raid chunks +# +create database test_$1; +create table test_$1.r2 (i int) raid_type=1 raid_chunks=257; +show create table test_$1.r2; +drop database test_$1; + +# +# Test that data is spread over different raid directories +# + CREATE TABLE t1 ( id int unsigned not null auto_increment primary key, c char(255) not null diff --git a/mysql-test/t/rpl_error_ignored_table.test b/mysql-test/t/rpl_error_ignored_table.test index f2ddaa273ed..4e4e79adf67 100644 --- a/mysql-test/t/rpl_error_ignored_table.test +++ b/mysql-test/t/rpl_error_ignored_table.test @@ -40,7 +40,7 @@ insert into t3 values(connection_id()); send update t2 set a = a + 1 + get_lock('crash_lock%20C', 10); connection master1; -sleep 2; +real_sleep 2; select (@id := id) - id from t3; kill @id; drop table t2,t3; diff --git a/mysql-test/t/rpl_multi_delete2-slave.opt b/mysql-test/t/rpl_multi_delete2-slave.opt new file mode 100644 index 00000000000..b828d03fafb --- /dev/null +++ b/mysql-test/t/rpl_multi_delete2-slave.opt @@ -0,0 +1 @@ +--replicate-wild-ignore-table=test.% diff --git a/mysql-test/t/rpl_multi_delete2.test b/mysql-test/t/rpl_multi_delete2.test new file mode 100644 index 00000000000..c5128833843 --- /dev/null +++ b/mysql-test/t/rpl_multi_delete2.test @@ -0,0 +1,23 @@ +source include/master-slave.inc; +create table t1 (a int); +create table t2 (a int); + +insert into t1 values (1); +insert into t2 values (1); + +delete t1.* from t1, t2 where t1.a = t2.a; + +save_master_pos; +select * from t1; +select * from t2; + +connection slave; +# BUG#3461 would cause sync to fail +sync_with_master; +error 1146; +select * from t1; +error 1146; +select * from t2; + +connection master; +drop table t1,t2; diff --git a/mysql-test/t/rpl_multi_update.test b/mysql-test/t/rpl_multi_update.test index 98a199ae412..88994aa66bd 100644 --- a/mysql-test/t/rpl_multi_update.test +++ b/mysql-test/t/rpl_multi_update.test @@ -2,7 +2,7 @@ source include/master-slave.inc; CREATE TABLE t1 ( a int unsigned not null auto_increment primary key, - b int unsigned, + b int unsigned ) ENGINE=MyISAM; CREATE TABLE t2 ( diff --git a/mysql-test/t/rpl_server_id1.test b/mysql-test/t/rpl_server_id1.test new file mode 100644 index 00000000000..1bd4d9547c7 --- /dev/null +++ b/mysql-test/t/rpl_server_id1.test @@ -0,0 +1,23 @@ +# This test checks that a slave does not execute queries originating +# from itself, by default. + +source include/master-slave.inc; +connection slave; +drop table if exists t1; +create table t1 (n int); +reset master; +# replicate ourselves +stop slave; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval change master to master_port=$SLAVE_MYPORT; +--replace_result $SLAVE_MYPORT SLAVE_PORT +--replace_column 18 # +show slave status; +start slave; +insert into t1 values (1); +# can't MASTER_POS_WAIT(), it does not work in this weird setup +# (when slave is its own master without --replicate-same-server-id) +sleep 2; # enough time for the event to be replicated (it should not) +show status like "slave_running"; +select * from t1; +drop table t1; diff --git a/mysql-test/t/rpl_server_id2-slave.opt b/mysql-test/t/rpl_server_id2-slave.opt new file mode 100644 index 00000000000..302889525dd --- /dev/null +++ b/mysql-test/t/rpl_server_id2-slave.opt @@ -0,0 +1 @@ +--disable-log-slave-updates --replicate-same-server-id diff --git a/mysql-test/t/rpl_server_id2.test b/mysql-test/t/rpl_server_id2.test new file mode 100644 index 00000000000..f022ab13188 --- /dev/null +++ b/mysql-test/t/rpl_server_id2.test @@ -0,0 +1,21 @@ +# This test checks that a slave DOES execute queries originating +# from itself, if running with --replicate-same-server-id. + +source include/master-slave.inc; +connection slave; +drop table if exists t1; +create table t1 (n int); +reset master; +# replicate ourselves +stop slave; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval change master to master_port=$SLAVE_MYPORT; +--replace_result $SLAVE_MYPORT SLAVE_PORT +--replace_column 18 # +show slave status; +start slave; +insert into t1 values (1); +save_master_pos; +sync_with_master; +select * from t1; # check that indeed 2 were inserted +drop table t1; |