diff options
Diffstat (limited to 'mysql-test/t')
-rw-r--r-- | mysql-test/t/blackhole.test | 12 | ||||
-rw-r--r-- | mysql-test/t/date_formats.test | 14 | ||||
-rw-r--r-- | mysql-test/t/federated.test | 60 | ||||
-rw-r--r-- | mysql-test/t/gis-rtree.test | 44 | ||||
-rw-r--r-- | mysql-test/t/init_connect.test | 203 | ||||
-rw-r--r-- | mysql-test/t/init_file.test | 14 | ||||
-rw-r--r-- | mysql-test/t/merge.test | 10 | ||||
-rw-r--r-- | mysql-test/t/myisam.test | 38 | ||||
-rw-r--r-- | mysql-test/t/mysqldump.test | 7 | ||||
-rw-r--r-- | mysql-test/t/ndb_replace.test | 37 | ||||
-rw-r--r-- | mysql-test/t/ndb_restore.test | 23 | ||||
-rw-r--r-- | mysql-test/t/ndb_trigger.test | 92 | ||||
-rw-r--r-- | mysql-test/t/rpl_auto_increment.test | 40 | ||||
-rw-r--r-- | mysql-test/t/rpl_insert_id.test | 63 | ||||
-rw-r--r-- | mysql-test/t/view_grant.test | 62 | ||||
-rw-r--r-- | mysql-test/t/wait_timeout.test | 12 |
16 files changed, 690 insertions, 41 deletions
diff --git a/mysql-test/t/blackhole.test b/mysql-test/t/blackhole.test index 493f74ded3e..e40b84eb5cd 100644 --- a/mysql-test/t/blackhole.test +++ b/mysql-test/t/blackhole.test @@ -128,15 +128,3 @@ show binlog events; drop table t1,t2,t3; # End of 4.1 tests - -# -# BUG#10952 - alter table ... lost data without errors and warnings -# -drop table if exists t1; -create table t1 (c char(20)) engine=MyISAM; -insert into t1 values ("Monty"),("WAX"),("Walrus"); ---error 1031 -alter table t1 engine=blackhole; -drop table t1; - -# End of 5.0 tests diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test index c3dd68abb86..21c228145ac 100644 --- a/mysql-test/t/date_formats.test +++ b/mysql-test/t/date_formats.test @@ -262,6 +262,20 @@ select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1, --enable_ps_protocol # +# Test of locale dependent date format (WL#2928 Date Translation NRE) +# +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set lc_time_names=ru_RU; +set names koi8r; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set lc_time_names=de_DE; +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +set names latin1; +set lc_time_names=en_US; + +# # Bug #14016 # create table t1 (f1 datetime); diff --git a/mysql-test/t/federated.test b/mysql-test/t/federated.test index a8b16edc80a..773c9121af0 100644 --- a/mysql-test/t/federated.test +++ b/mysql-test/t/federated.test @@ -1365,4 +1365,64 @@ drop table federated.t1, federated.t2; connection slave; drop table federated.t1, federated.t2; +# +# Additional test for bug#18437 "Wrong values inserted with a before +# update trigger on NDB table". SQL-layer didn't properly inform +# handler about fields which were read and set in triggers. In some +# cases this resulted in incorrect (garbage) values of OLD variables +# and lost changes to NEW variables. +# Since for federated engine only operation which is affected by wrong +# fields mark-up is handler::write_row() this file constains coverage +# for ON INSERT triggers only. Tests for other types of triggers reside +# in ndb_trigger.test. +# +--disable_warnings +drop table if exists federated.t1; +--enable_warnings +create table federated.t1 (a int, b int, c int); +connection master; +--disable_warnings +drop table if exists federated.t1; +drop table if exists federated.t2; +--enable_warnings +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table federated.t1 (a int, b int, c int) engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; +create trigger federated.t1_bi before insert on federated.t1 for each row set new.c= new.a * new.b; +create table federated.t2 (a int, b int); +insert into federated.t2 values (13, 17), (19, 23); +# Each of three statements should correctly set values for all three fields +# insert +insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); +select * from federated.t1; +delete from federated.t1; +# insert ... select +insert into federated.t1 (a, b) select * from federated.t2; +select * from federated.t1; +delete from federated.t1; +# load +load data infile '../std_data_ln/loaddata5.dat' into table federated.t1 fields terminated by '' enclosed by '' ignore 1 lines (a, b); +select * from federated.t1; +drop tables federated.t1, federated.t2; + +connection slave; +drop table federated.t1; +# +# Bug #16494: Updates that set a column to NULL fail sometimes +# +connection slave; +create table t1 (id int not null auto_increment primary key, val int); +connection master; +--replace_result $SLAVE_MYPORT SLAVE_PORT +eval create table t1 + (id int not null auto_increment primary key, val int) engine=federated + connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; +insert into t1 values (1,0),(2,0); +update t1 set val = NULL where id = 1; +select * from t1; +connection slave; +select * from t1; +drop table t1; +connection master; +drop table t1; + source include/federated_cleanup.inc; diff --git a/mysql-test/t/gis-rtree.test b/mysql-test/t/gis-rtree.test index 02e45861706..163f2806ad2 100644 --- a/mysql-test/t/gis-rtree.test +++ b/mysql-test/t/gis-rtree.test @@ -187,4 +187,48 @@ check table t1 extended; drop table t1; +# +# Bug#17877 - Corrupted spatial index +# +CREATE TABLE t1 ( + c1 geometry NOT NULL default '', + SPATIAL KEY i1 (c1(32)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 (c1) VALUES ( + PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, + -18.6055555000 -66.8158332999, + -18.7186111000 -66.8102777000, + -18.7211111000 -66.9269443999, + -18.6086111000 -66.9327777000))')); +# This showed a missing key. +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; +# +CREATE TABLE t1 ( + c1 geometry NOT NULL default '', + SPATIAL KEY i1 (c1(32)) +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 (c1) VALUES ( + PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, + -18.6055555000 -66.8158332999, + -18.7186111000 -66.8102777000, + -18.7211111000 -66.9269443999, + -18.6086111000 -66.9327777000))')); +INSERT INTO t1 (c1) VALUES ( + PolygonFromText('POLYGON((-65.7402776999 -96.6686111000, + -65.7372222000 -96.5516666000, + -65.8502777000 -96.5461111000, + -65.8527777000 -96.6627777000, + -65.7402776999 -96.6686111000))')); +# This is the same as the first insert to get a non-unique key. +INSERT INTO t1 (c1) VALUES ( + PolygonFromText('POLYGON((-18.6086111000 -66.9327777000, + -18.6055555000 -66.8158332999, + -18.7186111000 -66.8102777000, + -18.7211111000 -66.9269443999, + -18.6086111000 -66.9327777000))')); +# This showed (and still shows) OK. +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; + # End of 4.1 tests diff --git a/mysql-test/t/init_connect.test b/mysql-test/t/init_connect.test index 0ee6387d985..31a98df33df 100644 --- a/mysql-test/t/init_connect.test +++ b/mysql-test/t/init_connect.test @@ -35,4 +35,205 @@ select @a; connection con0; drop table t1; -# End of 4.1 tests +disconnect con1; +disconnect con2; +disconnect con3; +disconnect con4; +disconnect con5; + +--echo End of 4.1 tests +# +# Test 5.* features +# + +create table t1 (x int); +insert into t1 values (3), (5), (7); +create table t2 (y int); + +create user mysqltest1@localhost; +grant all privileges on test.* to mysqltest1@localhost; +# +# Create a simple procedure +# +set global init_connect="create procedure p1() select * from t1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +call p1(); +drop procedure p1; + +connection con0; +disconnect con1; +# +# Create a multi-result set procedure +# +set global init_connect="create procedure p1(x int)\ +begin\ + select count(*) from t1;\ + select * from t1;\ + set @x = x; +end"; +connect (con1,localhost,mysqltest1,,); +connection con1; +call p1(42); +select @x; + +connection con0; +disconnect con1; +# +# Just call it - this will not generate any output +# +set global init_connect="call p1(4711)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @x; + +connection con0; +disconnect con1; +# +# Drop the procedure +# +set global init_connect="drop procedure if exists p1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +--error ER_SP_DOES_NOT_EXIST +call p1(); + +connection con0; +disconnect con1; +# +# Execution of a more complex procedure +# +delimiter |; +create procedure p1(out sum int) +begin + declare n int default 0; + declare c cursor for select * from t1; + declare exit handler for not found + begin + close c; + set sum = n; + end; + + open c; + loop + begin + declare x int; + + fetch c into x; + if x > 3 then + set n = n + x; + end if; + end; + end loop; +end| +delimiter ;| +# Call the procedure with a cursor +set global init_connect="call p1(@sum)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @sum; + +connection con0; +disconnect con1; +drop procedure p1; +# +# Test Dynamic SQL +# +delimiter |; +create procedure p1(tbl char(10), v int) +begin + set @s = concat('insert into ', tbl, ' values (?)'); + set @v = v; + prepare stmt1 from @s; + execute stmt1 using @v; + deallocate prepare stmt1; +end| +delimiter ;| +# Call the procedure with prepared statements +set global init_connect="call p1('t1', 11)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from t1; + +connection con0; +disconnect con1; +drop procedure p1; +# +# Stored functions +# +delimiter |; +create function f1() returns int +begin + declare n int; + + select count(*) into n from t1; + return n; +end| +delimiter ;| +# Invoke a function +set global init_connect="set @x = f1()"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select @x; + +connection con0; +disconnect con1; +# +# Create a view +# +set global init_connect="create view v1 as select f1()"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from v1; + +connection con0; +disconnect con1; +# +# Drop the view +# +set global init_connect="drop view v1"; +connect (con1,localhost,mysqltest1,,); +connection con1; +--error ER_NO_SUCH_TABLE +select * from v1; + +connection con0; +disconnect con1; +drop function f1; + +# We can't test "create trigger", since this requires super privileges +# in 5.0, but with super privileges, init_connect is not executed. +# (However, this can be tested in 5.1) +# +#set global init_connect="create trigger trg1\ +# after insert on t2\ +# for each row\ +# insert into t1 values (new.y)"; +#connect (con1,localhost,mysqltest1,,); +#connection con1; +#insert into t2 values (2), (4); +#select * from t1; +# +#connection con0; +#disconnect con1; + +create trigger trg1 + after insert on t2 + for each row + insert into t1 values (new.y); + +# Invoke trigger +set global init_connect="insert into t2 values (13), (17), (19)"; +connect (con1,localhost,mysqltest1,,); +connection con1; +select * from t1; + +connection con0; +disconnect con1; + +drop trigger trg1; +set global init_connect=default; + +revoke all privileges, grant option from mysqltest1@localhost; +drop user mysqltest1@localhost; +drop table t1, t2; diff --git a/mysql-test/t/init_file.test b/mysql-test/t/init_file.test index 8b4b788777b..6b5e032fd99 100644 --- a/mysql-test/t/init_file.test +++ b/mysql-test/t/init_file.test @@ -6,5 +6,15 @@ # mysql-test/t/init_file-master.opt for the actual test # -# End of 4.1 tests -echo ok; +--echo ok +--echo end of 4.1 tests +# +# Chec 5.x features +# +# Expected: +# 3, 5, 7, 11, 13 +select * from t1; +# Expected: +# 30, 3, 11, 13 +select * from t2; +drop table t1, t2; diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index 400279a826b..639129e1393 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -381,16 +381,6 @@ drop table t1, t2, t3; # End of 4.1 tests # -# BUG#10952 - alter table ... lost data without errors and warnings -# -drop table if exists t1; -create table t1 (c char(20)) engine=MyISAM; -insert into t1 values ("Monty"),("WAX"),("Walrus"); ---error 1031 -alter table t1 engine=MERGE; -drop table t1; - -# # BUG#19648 - Merge table does not work with bit types # create table t1 (b bit(1)); diff --git a/mysql-test/t/myisam.test b/mysql-test/t/myisam.test index 56e799f00b6..7dee5ebdf41 100644 --- a/mysql-test/t/myisam.test +++ b/mysql-test/t/myisam.test @@ -718,8 +718,6 @@ UPDATE t1 AS ta1,t1 AS ta2 SET ta1.b='aaaaaa',ta2.b='bbbbbb'; SELECT * FROM t1; DROP TABLE t1; -# End of 4.1 tests - # # Test varchar # @@ -844,3 +842,39 @@ create table t3 (c1 int) engine=myisam pack_keys=default; --error 1064 create table t4 (c1 int) engine=myisam pack_keys=2; drop table t1, t2, t3; +# +# Bug#8706 - temporary table with data directory option fails +# +connect (session1,localhost,root,,); +connect (session2,localhost,root,,); + +connection session1; +disable_query_log; +eval create temporary table t1 (a int) engine=myisam data directory="$MYSQLTEST_VARDIR/tmp" select 9 a; +enable_query_log; +disable_result_log; +show create table t1; +enable_result_log; + +connection session2; +disable_query_log; +eval create temporary table t1 (a int) engine=myisam data directory="$MYSQLTEST_VARDIR/tmp" select 99 a; +enable_query_log; +disable_result_log; +show create table t1; +enable_result_log; + +connection default; +create table t1 (a int) engine=myisam select 42 a; + +connection session1; +select * from t1; +disconnect session1; +connection session2; +select * from t1; +disconnect session2; +connection default; +select * from t1; +drop table t1; + +# End of 4.1 tests diff --git a/mysql-test/t/mysqldump.test b/mysql-test/t/mysqldump.test index a091242171e..4a355897adb 100644 --- a/mysql-test/t/mysqldump.test +++ b/mysql-test/t/mysqldump.test @@ -1155,12 +1155,11 @@ insert into t values(5, 51); create view v1 as select qty, price, qty*price as value from t; create view v2 as select qty from v1; --echo mysqldump { ---exec $MYSQL_DUMP --compact -F --tab . test ---exec cat v1.sql +--exec $MYSQL_DUMP --compact -F --tab $MYSQLTEST_VARDIR/tmp test +--exec cat $MYSQLTEST_VARDIR/tmp/v1.sql --echo } mysqldump { ---exec cat v2.sql +--exec cat $MYSQLTEST_VARDIR/tmp/v2.sql --echo } mysqldump ---rm v.sql t.sql t.txt drop view v1; drop view v2; drop table t; diff --git a/mysql-test/t/ndb_replace.test b/mysql-test/t/ndb_replace.test index 94a11f7dfb2..476a607ed44 100644 --- a/mysql-test/t/ndb_replace.test +++ b/mysql-test/t/ndb_replace.test @@ -39,6 +39,7 @@ INSERT INTO t1 VALUES (1,1,23),(2,2,24); REPLACE INTO t1 (j,k) VALUES (1,42); REPLACE INTO t1 (i,j) VALUES (17,2); SELECT * from t1 ORDER BY i; +DROP TABLE t1; # bug#19906 CREATE TABLE t2 (a INT(11) NOT NULL, @@ -64,4 +65,40 @@ SELECT * FROM t2 ORDER BY id; DROP TABLE t2; +# +# Bug #20728 "REPLACE does not work correctly for NDB table with PK and +# unique index" +# +--disable_warnings +drop table if exists t1; +--enable_warnings +create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster; +# Test for plain replace which updates pk +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (4, 1), (5, 2); +select * from t1 order by pk; +delete from t1; +# Another test for plain replace which doesn't touch pk +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) values (1, 4), (2, 5); +select * from t1 order by pk; +delete from t1; +# Test for load data replace which updates pk +insert into t1 values (1, 1, 1), (4, 4, 4), (6, 6, 6); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +delete from t1; +# Now test for load data replace which doesn't touch pk +insert into t1 values (1, 1, 1), (3, 3, 3), (5, 5, 5); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (pk, apk); +select * from t1 order by pk; +delete from t1; +# Finally test for both types of replace ... select +insert into t1 values (1, 1, 1), (2, 2, 2), (3, 3, 3); +replace into t1 (pk, apk) select 4, 1; +replace into t1 (pk, apk) select 2, 4; +select * from t1 order by pk; +# Clean-up +drop table t1; +--echo End of 5.0 tests. diff --git a/mysql-test/t/ndb_restore.test b/mysql-test/t/ndb_restore.test index 049b07d5a8b..39c7ab67efb 100644 --- a/mysql-test/t/ndb_restore.test +++ b/mysql-test/t/ndb_restore.test @@ -4,8 +4,8 @@ --disable_warnings use test; -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -drop table if exists t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; +drop table if exists t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c,t10_c; --enable_warnings CREATE TABLE `t1` ( @@ -132,6 +132,13 @@ CREATE TABLE `t9` ( ) ENGINE=myisam DEFAULT CHARSET=latin1; INSERT INTO `t9` VALUES ('3g4jh8gar2t','joe','q3.net','elredun.com','q3.net','436643316120','436643316939','91341234568968','695595699','1.1.1.1','2.2.6.2','3','86989','34','x','x','2012-03-12 18:35:04','2012-12-05 12:35:04',3123123,9569,6565,1),('4tt45345235','pap','q3plus.qt','q3plus.qt','q3.net','436643316120','436643316939','8956234534568968','5254595969','1.1.1.1','8.6.2.2','4','86989','34','x','x','2012-03-12 12:55:34','2012-12-05 11:20:04',3223433,3369,9565,2),('4545435545','john','q3.net','q3.net','acne.li','436643316120','436643316939','45345234568968','995696699','1.1.1.1','2.9.9.2','2','86998','34','x','x','2012-03-12 11:35:03','2012-12-05 08:50:04',8823123,169,3565,3); +# Bug #20820 +# auto inc table not handled correctly when restored from cluster backup +# - before fix ndb_restore would not set auto inc value correct, +# seen by select below +create table t10 (a int auto_increment key); +insert into t10 values (1),(2),(3); + create table t1_c engine=ndbcluster as select * from t1; create table t2_c engine=ndbcluster as select * from t2; create table t3_c engine=ndbcluster as select * from t3; @@ -141,10 +148,11 @@ create table t6_c engine=ndbcluster as select * from t6; create table t7_c engine=ndbcluster as select * from t7; create table t8_c engine=ndbcluster as select * from t8; create table t9_c engine=ndbcluster as select * from t9; +create table t10_c engine=ndbcluster as select * from t10; --exec $NDB_MGM --no-defaults -e "start backup" >> $NDB_TOOLS_OUTPUT -drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; +drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c, t10_c; --exec $NDB_TOOLS_DIR/ndb_restore --no-defaults -b 1 -n 1 -m -r --print --print_meta $NDB_BACKUP_DIR/BACKUP/BACKUP-1 >> $NDB_TOOLS_OUTPUT --exec $NDB_TOOLS_DIR/ndb_restore --no-defaults -b 1 -n 2 -r --print --print_meta $NDB_BACKUP_DIR/BACKUP/BACKUP-1 >> $NDB_TOOLS_OUTPUT @@ -205,9 +213,12 @@ select count(*) from (select * from t9 union select * from t9_c) a; +# Bug #20820 cont'd +select * from t10_c order by a; + --disable_warnings -drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; -drop table if exists t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; +drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9, t10; +drop table if exists t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c, t10_c; --enable_warnings # @@ -216,4 +227,4 @@ drop table if exists t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; --exec $NDB_TOOLS_DIR/ndb_select_all --no-defaults -d sys -D , SYSTAB_0 | grep 520093696 -# End of 4.1 tests +# End of 5.0 tests (4.1 test intermixed to save test time) diff --git a/mysql-test/t/ndb_trigger.test b/mysql-test/t/ndb_trigger.test new file mode 100644 index 00000000000..2521ef17842 --- /dev/null +++ b/mysql-test/t/ndb_trigger.test @@ -0,0 +1,92 @@ +# Tests which involve triggers and NDB storage engine +--source include/have_ndb.inc +--source include/not_embedded.inc + +# +# Test for bug#18437 "Wrong values inserted with a before update +# trigger on NDB table". SQL-layer didn't properly inform handler +# about fields which were read and set in triggers. In some cases +# this resulted in incorrect (garbage) values of OLD variables and +# lost changes to NEW variables. +# You can find similar tests for ON INSERT triggers in federated.test +# since this engine so far is the only engine in MySQL which cares +# about field mark-up during handler::write_row() operation. +# + +--disable_warnings +drop table if exists t1, t2, t3; +--enable_warnings + +create table t1 (id int primary key, a int not null, b decimal (63,30) default 0) engine=ndb; +create table t2 (op char(1), a int not null, b decimal (63,30)); +create table t3 select 1 as i; + +delimiter //; +create trigger t1_bu before update on t1 for each row +begin + insert into t2 values ("u", old.a, old.b); + set new.b = old.b + 10; +end;// +create trigger t1_bd before delete on t1 for each row +begin + insert into t2 values ("d", old.a, old.b); +end;// +delimiter ;// +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); + +# Check that usual update works as it should +update t1 set a=5 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check that everything works for multi-update +update t1, t3 set a=6 where a = 5; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for delete +delete from t1 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for multi-delete +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (4, 4, 4.05); +delete t1 from t1, t3 where a != 3; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for insert ... on duplicate key update +insert into t1 values (4, 4, 4.05); +insert into t1 (id, a) values (4, 1), (3, 1) on duplicate key update a= a + 1; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for insert ... select ... on duplicate key update +delete from t3; +insert into t3 values (4), (3); +insert into t1 (id, a) (select i, 1 from t3) on duplicate key update a= a + 1; +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t2; +# Check for replace +replace into t1 (id, a) values (4, 1), (3, 1); +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t1; +delete from t2; +# Check for replace ... select ... +insert into t1 values (3, 1, 1.05), (4, 1, 2.05); +replace into t1 (id, a) (select i, 2 from t3); +select * from t1 order by id; +select * from t2 order by op, a, b; +delete from t1; +delete from t2; +# Check for load data replace +insert into t1 values (3, 1, 1.05), (5, 2, 2.05); +load data infile '../std_data_ln/loaddata5.dat' replace into table t1 fields terminated by '' enclosed by '' ignore 1 lines (id, a); +select * from t1 order by id; +select * from t2 order by op, a, b; + +drop tables t1, t2, t3; + +--echo End of 5.0 tests diff --git a/mysql-test/t/rpl_auto_increment.test b/mysql-test/t/rpl_auto_increment.test index 71032404307..caa2b79feb5 100644 --- a/mysql-test/t/rpl_auto_increment.test +++ b/mysql-test/t/rpl_auto_increment.test @@ -96,9 +96,47 @@ select * from t1; sync_slave_with_master; select * from t1; -connection master; +# Test for BUG#20524 "auto_increment_* not observed when inserting +# a too large value". When an autogenerated value was bigger than the +# maximum possible value of the field, it was truncated to that max +# possible value, without being "rounded down" to still honour +# auto_increment_* variables. + +connection master; drop table t1; +create table t1 (a tinyint not null auto_increment primary key) engine=myisam; +insert into t1 values(103); +set auto_increment_increment=11; +set auto_increment_offset=4; +insert into t1 values(null); +insert into t1 values(null); +--error 1062 +insert into t1 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; + +# same but with a larger value +create table t2 (a tinyint unsigned not null auto_increment primary key) engine=myisam; +set auto_increment_increment=10; +set auto_increment_offset=1; +set insert_id=1000; +insert into t2 values(null); +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; + +# An offset so big that even first value does not fit +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t3 values(null); +select * from t3 order by a; +sync_slave_with_master; +select * from t1 order by a; +select * from t2 order by a; +select * from t3 order by a; + +connection master; + +drop table t1,t2,t3; # End cleanup sync_slave_with_master; diff --git a/mysql-test/t/rpl_insert_id.test b/mysql-test/t/rpl_insert_id.test index d2866bca60f..fa66306aaa6 100644 --- a/mysql-test/t/rpl_insert_id.test +++ b/mysql-test/t/rpl_insert_id.test @@ -165,6 +165,69 @@ drop function bug15728; drop function bug15728_insert; drop table t1, t2; +# test of BUG#20188 REPLACE or ON DUPLICATE KEY UPDATE in +# auto_increment breaks binlog + +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); + +# First, test that we do not call restore_auto_increment() too early +# in write_record(): +set sql_log_bin=0; +insert into t1 values(null,100); +replace into t1 values(null,50),(null,100),(null,150); +select * from t1 order by n; +truncate table t1; +set sql_log_bin=1; + +insert into t1 values(null,100); +select * from t1 order by n; +sync_slave_with_master; +# make slave's table autoinc counter bigger +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +# check that slave's table content is identical to master +select * from t1 order by n; +# only the auto_inc counter differs. + +connection master; +replace into t1 values(null,100),(null,350); +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +# Same test as for REPLACE, but for ON DUPLICATE KEY UPDATE + +# We first check that if we update a row using a value larger than the +# table's counter, the counter for next row is bigger than the +# after-value of the updated row. +connection master; +insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000; +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +# and now test for the bug: +connection master; +drop table t1; +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); +insert into t1 values(null,100); +select * from t1 order by n; +sync_slave_with_master; +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; + +connection master; +insert into t1 values(null,100),(null,350) on duplicate key update n=2; +select * from t1 order by n; +sync_slave_with_master; +select * from t1 order by n; + +connection master; +drop table t1; + # End of 5.0 tests sync_slave_with_master; diff --git a/mysql-test/t/view_grant.test b/mysql-test/t/view_grant.test index 429a2af6bac..c75e5422de7 100644 --- a/mysql-test/t/view_grant.test +++ b/mysql-test/t/view_grant.test @@ -866,3 +866,65 @@ DROP VIEW test2.t3; DROP TABLE test2.t1, test1.t0; DROP DATABASE test2; DROP DATABASE test1; + + +# +# BUG#20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER +# returns invoker name +# +--disable_warnings +DROP VIEW IF EXISTS v1; +DROP VIEW IF EXISTS v2; +DROP VIEW IF EXISTS v3; +DROP FUNCTION IF EXISTS f1; +DROP FUNCTION IF EXISTS f2; +DROP PROCEDURE IF EXISTS p1; +--enable_warnings + +CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu; + +CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER + RETURN CURRENT_USER(); +CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu; + +CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER + SET cu= CURRENT_USER(); +delimiter |; +CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER +BEGIN + DECLARE cu VARCHAR(77); + CALL p1(cu); + RETURN cu; +END| +delimiter ;| +CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; + +CREATE USER mysqltest_u1@localhost; +GRANT ALL ON test.* TO mysqltest_u1@localhost; + +connect (conn1, localhost, mysqltest_u1,,); + +--echo +--echo The following tests should all return 1. +--echo +SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; +SELECT f1() = 'mysqltest_u1@localhost'; +CALL p1(@cu); +SELECT @cu = 'mysqltest_u1@localhost'; +SELECT f2() = 'mysqltest_u1@localhost'; +SELECT cu = 'root@localhost' FROM v1; +SELECT cu = 'root@localhost' FROM v2; +SELECT cu = 'root@localhost' FROM v3; + +disconnect conn1; +connection default; + +DROP VIEW v3; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP VIEW v2; +DROP VIEW v1; +DROP USER mysqltest_u1@localhost; + +# End of 5.0 tests. diff --git a/mysql-test/t/wait_timeout.test b/mysql-test/t/wait_timeout.test index 8387c08c902..195d1a5d3f2 100644 --- a/mysql-test/t/wait_timeout.test +++ b/mysql-test/t/wait_timeout.test @@ -9,16 +9,20 @@ # Connect with another connection and reset counters --disable_query_log connect (wait_con,localhost,root,,test,,); -flush status; # Reset counters connection wait_con; set session wait_timeout=100; let $retries=300; -let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; set @aborted_clients= 0; --enable_query_log # Disable reconnect and do the query connection default; +# If slow host (Valgrind...), we may have already timed out here. +# So force a reconnect if necessary, using a dummy query. And issue a +# 'flush status' to reset the 'aborted_clients' counter. +--enable_reconnect +select 0; +flush status; --disable_reconnect select 1; @@ -46,6 +50,9 @@ connection default; select 2; --enable_reconnect select 3; +# Disconnect so that we will not be confused by a future abort from this +# connection. +disconnect default # # Do the same test as above on a TCP connection @@ -56,7 +63,6 @@ select 3; connection wait_con; flush status; # Reset counters let $retries=300; -let $aborted_clients = `SHOW STATUS LIKE 'aborted_clients'`; set @aborted_clients= 0; --enable_query_log |