diff options
Diffstat (limited to 'mysql-test')
36 files changed, 1320 insertions, 72 deletions
diff --git a/mysql-test/Makefile.am b/mysql-test/Makefile.am index 73074397086..39fc425bf06 100644 --- a/mysql-test/Makefile.am +++ b/mysql-test/Makefile.am @@ -101,15 +101,15 @@ uninstall-local: @RM@ -f -r $(DESTDIR)$(testdir) std_data/client-key.pem: $(top_srcdir)/SSL/$(@F) - @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data + @RM@ -f $@; @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data std_data/client-cert.pem: $(top_srcdir)/SSL/$(@F) - @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data + @RM@ -f $@; @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data std_data/cacert.pem: $(top_srcdir)/SSL/$(@F) - @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data + @RM@ -f $@; @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data std_data/server-cert.pem: $(top_srcdir)/SSL/$(@F) - @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data + @RM@ -f $@; @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data std_data/server-key.pem: $(top_srcdir)/SSL/$(@F) - @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data + @RM@ -f $@; @CP@ $(top_srcdir)/SSL/$(@F) $(srcdir)/std_data SUFFIXES = .sh diff --git a/mysql-test/include/ndb_default_cluster.inc b/mysql-test/include/ndb_default_cluster.inc index 2f900b6a0b4..de7eda3c596 100644 --- a/mysql-test/include/ndb_default_cluster.inc +++ b/mysql-test/include/ndb_default_cluster.inc @@ -1,4 +1,4 @@ -- require r/ndb_default_cluster.require disable_query_log; -show status like "Ndb_connected_host"; +show status like "Ndb_config_from_host"; enable_query_log; diff --git a/mysql-test/mysql-test-run.pl b/mysql-test/mysql-test-run.pl index 5226e6f80df..14bd9b198a2 100755 --- a/mysql-test/mysql-test-run.pl +++ b/mysql-test/mysql-test-run.pl @@ -534,6 +534,11 @@ sub command_line_setup () { "($opt_master_myport - $opt_master_myport + 10)"); } + # This is needed for test log evaluation in "gen-build-status-page" + # in all cases where the calling tool does not log the commands + # directly before it executes them, like "make test-force-pl" in RPM builds. + print "Logging: $0 ", join(" ", @ARGV), "\n"; + # Read the command line # Note: Keep list, and the order, in sync with usage at end of this file diff --git a/mysql-test/r/blackhole.result b/mysql-test/r/blackhole.result index a4c057f256c..140d7e73d48 100644 --- a/mysql-test/r/blackhole.result +++ b/mysql-test/r/blackhole.result @@ -123,11 +123,3 @@ master-bin.000001 # Query 1 # use `test`; create table t3 like t1 master-bin.000001 # Query 1 # use `test`; insert into t1 select * from t3 master-bin.000001 # Query 1 # use `test`; replace into t1 select * from t3 drop table t1,t2,t3; -drop table if exists t1; -Warnings: -Note 1051 Unknown table 't1' -create table t1 (c char(20)) engine=MyISAM; -insert into t1 values ("Monty"),("WAX"),("Walrus"); -alter table t1 engine=blackhole; -ERROR HY000: Table storage engine for 't1' doesn't have this option -drop table t1; diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result index 77a3473e9fe..45078800910 100644 --- a/mysql-test/r/date_formats.result +++ b/mysql-test/r/date_formats.result @@ -495,6 +495,22 @@ f1 f2 Warnings: Warning 1292 Truncated incorrect date value: '2003-04-05 g' Warning 1292 Truncated incorrect datetime value: '2003-04-05 10:11:12.101010234567' +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +date_format('2004-01-01','%W (%a), %e %M (%b) %Y') +Thursday (Thu), 1 January (Jan) 2004 +set lc_time_names=ru_RU; +set names koi8r; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +date_format('2004-01-01','%W (%a), %e %M (%b) %Y') +þÅÔ×ÅÒÇ (þÔ×), 1 ñÎ×ÁÒÑ (ñÎ×) 2004 +set lc_time_names=de_DE; +set names latin1; +select date_format('2004-01-01','%W (%a), %e %M (%b) %Y'); +date_format('2004-01-01','%W (%a), %e %M (%b) %Y') +Donnerstag (Do), 1 Januar (Jan) 2004 +set names latin1; +set lc_time_names=en_US; create table t1 (f1 datetime); insert into t1 (f1) values ("2005-01-01"); insert into t1 (f1) values ("2005-02-01"); diff --git a/mysql-test/r/federated.result b/mysql-test/r/federated.result index 2eb0c81ec2e..709e44579e2 100644 --- a/mysql-test/r/federated.result +++ b/mysql-test/r/federated.result @@ -1689,6 +1689,50 @@ id c1 c2 9 abc ppc drop table federated.t1, federated.t2; drop table federated.t1, federated.t2; +drop table if exists federated.t1; +create table federated.t1 (a int, b int, c int); +drop table if exists federated.t1; +drop table if exists federated.t2; +create table federated.t1 (a int, b int, c int) engine=federated connection='mysql://root@127.0.0.1:SLAVE_PORT/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); +insert into federated.t1 (a, b) values (1, 2), (3, 5), (7, 11); +select * from federated.t1; +a b c +1 2 2 +3 5 15 +7 11 77 +delete from federated.t1; +insert into federated.t1 (a, b) select * from federated.t2; +select * from federated.t1; +a b c +13 17 221 +19 23 437 +delete from federated.t1; +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; +a b c +3 4 12 +5 6 30 +drop tables federated.t1, federated.t2; +drop table federated.t1; +create table t1 (id int not null auto_increment primary key, val int); +create table t1 +(id int not null auto_increment primary key, val int) engine=federated +connection='mysql://root@127.0.0.1:SLAVE_PORT/test/t1'; +insert into t1 values (1,0),(2,0); +update t1 set val = NULL where id = 1; +select * from t1; +id val +1 NULL +2 0 +select * from t1; +id val +1 NULL +2 0 +drop table t1; +drop table t1; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; DROP TABLE IF EXISTS federated.t1; diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result index 5b49e9d108d..28c59053435 100644 --- a/mysql-test/r/gis-rtree.result +++ b/mysql-test/r/gis-rtree.result @@ -816,3 +816,43 @@ check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK 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))')); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +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))')); +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))')); +CHECK TABLE t1 EXTENDED; +Table Op Msg_type Msg_text +test.t1 check status OK +DROP TABLE t1; diff --git a/mysql-test/r/init_connect.result b/mysql-test/r/init_connect.result index eeae422edc4..f90ee5913a1 100644 --- a/mysql-test/r/init_connect.result +++ b/mysql-test/r/init_connect.result @@ -22,3 +22,117 @@ set GLOBAL init_connect="adsfsdfsdfs"; select @a; Got one of the listed errors drop table t1; +End of 4.1 tests +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; +set global init_connect="create procedure p1() select * from t1"; +call p1(); +x +3 +5 +7 +drop procedure p1; +set global init_connect="create procedure p1(x int)\ +begin\ + select count(*) from t1;\ + select * from t1;\ + set @x = x; +end"; +call p1(42); +count(*) +3 +x +3 +5 +7 +select @x; +@x +42 +set global init_connect="call p1(4711)"; +select @x; +@x +4711 +set global init_connect="drop procedure if exists p1"; +call p1(); +ERROR 42000: PROCEDURE test.p1 does not exist +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| +set global init_connect="call p1(@sum)"; +select @sum; +@sum +12 +drop procedure p1; +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| +set global init_connect="call p1('t1', 11)"; +select * from t1; +x +3 +5 +7 +11 +drop procedure p1; +create function f1() returns int +begin +declare n int; +select count(*) into n from t1; +return n; +end| +set global init_connect="set @x = f1()"; +select @x; +@x +4 +set global init_connect="create view v1 as select f1()"; +select * from v1; +f1() +4 +set global init_connect="drop view v1"; +select * from v1; +ERROR 42S02: Table 'test.v1' doesn't exist +drop function f1; +create trigger trg1 +after insert on t2 +for each row +insert into t1 values (new.y); +set global init_connect="insert into t2 values (13), (17), (19)"; +select * from t1; +x +3 +5 +7 +11 +13 +17 +19 +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/r/init_file.result b/mysql-test/r/init_file.result index 9766475a418..1569f2c3d68 100644 --- a/mysql-test/r/init_file.result +++ b/mysql-test/r/init_file.result @@ -1 +1,16 @@ ok +end of 4.1 tests +select * from t1; +x +3 +5 +7 +11 +13 +select * from t2; +y +30 +3 +11 +13 +drop table t1, t2; diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index 568f83b7d6d..b1abe16a091 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -768,14 +768,6 @@ Table Op Msg_type Msg_text test.t1 check status OK test.t2 check status OK drop table t1, t2, t3; -drop table if exists t1; -Warnings: -Note 1051 Unknown table 't1' -create table t1 (c char(20)) engine=MyISAM; -insert into t1 values ("Monty"),("WAX"),("Walrus"); -alter table t1 engine=MERGE; -ERROR HY000: Table storage engine for 't1' doesn't have this option -drop table t1; create table t1 (b bit(1)); create table t2 (b bit(1)); create table tm (b bit(1)) engine = merge union = (t1,t2); diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 780287fb807..c7d8f5c128d 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -1454,3 +1454,16 @@ create table t3 (c1 int) engine=myisam pack_keys=default; create table t4 (c1 int) engine=myisam pack_keys=2; ERROR 42000: 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 '2' at line 1 drop table t1, t2, t3; +show create table t1; +show create table t1; +create table t1 (a int) engine=myisam select 42 a; +select * from t1; +a +9 +select * from t1; +a +99 +select * from t1; +a +42 +drop table t1; diff --git a/mysql-test/r/ndb_default_cluster.require b/mysql-test/r/ndb_default_cluster.require index aa4988cdca3..3616ae0f343 100644 --- a/mysql-test/r/ndb_default_cluster.require +++ b/mysql-test/r/ndb_default_cluster.require @@ -1,2 +1,2 @@ Variable_name Value -Ndb_connected_host localhost +Ndb_config_from_host localhost diff --git a/mysql-test/r/ndb_replace.result b/mysql-test/r/ndb_replace.result index cdfcd6a7a43..4d63c397d60 100644 --- a/mysql-test/r/ndb_replace.result +++ b/mysql-test/r/ndb_replace.result @@ -30,7 +30,8 @@ REPLACE INTO t1 (i,j) VALUES (17,2); SELECT * from t1 ORDER BY i; i j k 3 1 42 -17 2 24 +17 2 NULL +DROP TABLE t1; CREATE TABLE t2 (a INT(11) NOT NULL, b INT(11) NOT NULL, c INT(11) NOT NULL, @@ -52,3 +53,47 @@ SELECT * FROM t2 ORDER BY id; a b c x y z id i 1 1 1 b b b 5 2 DROP TABLE t2; +drop table if exists t1; +create table t1 (pk int primary key, apk int unique, data int) engine=ndbcluster; +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; +pk apk data +3 3 3 +4 1 NULL +5 2 NULL +delete from t1; +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; +pk apk data +1 4 NULL +2 5 NULL +3 3 3 +delete from t1; +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; +pk apk data +1 1 1 +3 4 NULL +5 6 NULL +delete from t1; +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; +pk apk data +1 1 1 +3 4 NULL +5 6 NULL +delete from t1; +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; +pk apk data +2 4 NULL +3 3 3 +4 1 NULL +drop table t1; +End of 5.0 tests. diff --git a/mysql-test/r/ndb_restore.result b/mysql-test/r/ndb_restore.result index c78a4137468..e5bf4315e5c 100644 --- a/mysql-test/r/ndb_restore.result +++ b/mysql-test/r/ndb_restore.result @@ -1,6 +1,6 @@ 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; CREATE TABLE `t1` ( `capgoaledatta` smallint(5) unsigned NOT NULL auto_increment, `goaledatta` char(2) NOT NULL default '', @@ -116,6 +116,8 @@ CREATE TABLE `t9` ( PRIMARY KEY (`kattjame`,`hunderaaarbagefa`,`hassetistart`,`hassetino`) ) 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); +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; @@ -125,10 +127,12 @@ 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; -drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c; +create table t10_c engine=ndbcluster as select * from t10; +drop table t1_c,t2_c,t3_c,t4_c,t5_c,t6_c,t7_c,t8_c,t9_c, t10_c; show tables; Tables_in_test t1 +t10 t2 t3 t4 @@ -137,14 +141,15 @@ t6 t7 t8 t9 -t8_c +t3_c t9_c t1_c +t8_c t7_c t6_c t5_c t4_c -t3_c +t10_c t2_c select count(*) from t1; count(*) @@ -245,6 +250,11 @@ from (select * from t9 union select * from t9_c) a; count(*) 3 -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; +select * from t10_c order by a; +a +1 +2 +3 +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; 520093696,1 diff --git a/mysql-test/r/ndb_trigger.result b/mysql-test/r/ndb_trigger.result new file mode 100644 index 00000000000..27f83df70c9 --- /dev/null +++ b/mysql-test/r/ndb_trigger.result @@ -0,0 +1,119 @@ +drop table if exists t1, t2, t3; +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; +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;// +insert into t1 values (1, 1, 1.05), (2, 2, 2.05), (3, 3, 3.05), (4, 4, 4.05); +update t1 set a=5 where a != 3; +select * from t1 order by id; +id a b +1 5 11.050000000000000000000000000000 +2 5 12.050000000000000000000000000000 +3 3 3.050000000000000000000000000000 +4 5 14.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 1 1.050000000000000000000000000000 +u 2 2.050000000000000000000000000000 +u 4 4.050000000000000000000000000000 +delete from t2; +update t1, t3 set a=6 where a = 5; +select * from t1 order by id; +id a b +1 6 21.050000000000000000000000000000 +2 6 22.050000000000000000000000000000 +3 3 3.050000000000000000000000000000 +4 6 24.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 5 11.050000000000000000000000000000 +u 5 12.050000000000000000000000000000 +u 5 14.050000000000000000000000000000 +delete from t2; +delete from t1 where a != 3; +select * from t1 order by id; +id a b +3 3 3.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 6 21.050000000000000000000000000000 +d 6 22.050000000000000000000000000000 +d 6 24.050000000000000000000000000000 +delete from t2; +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; +id a b +3 3 3.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 2 2.050000000000000000000000000000 +d 4 4.050000000000000000000000000000 +delete from t2; +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; +id a b +3 4 13.050000000000000000000000000000 +4 5 14.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 3 3.050000000000000000000000000000 +u 4 4.050000000000000000000000000000 +delete from t2; +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; +id a b +3 5 23.050000000000000000000000000000 +4 6 24.050000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +u 4 13.050000000000000000000000000000 +u 5 14.050000000000000000000000000000 +delete from t2; +replace into t1 (id, a) values (4, 1), (3, 1); +select * from t1 order by id; +id a b +3 1 0.000000000000000000000000000000 +4 1 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 5 23.050000000000000000000000000000 +d 6 24.050000000000000000000000000000 +delete from t1; +delete from t2; +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; +id a b +3 2 0.000000000000000000000000000000 +4 2 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 1 2.050000000000000000000000000000 +delete from t1; +delete from t2; +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; +id a b +3 4 0.000000000000000000000000000000 +5 6 0.000000000000000000000000000000 +select * from t2 order by op, a, b; +op a b +d 1 1.050000000000000000000000000000 +d 2 2.050000000000000000000000000000 +drop tables t1, t2, t3; +End of 5.0 tests diff --git a/mysql-test/r/rpl_auto_increment.result b/mysql-test/r/rpl_auto_increment.result index 9eca51ad2d9..ea4815e5e64 100644 --- a/mysql-test/r/rpl_auto_increment.result +++ b/mysql-test/r/rpl_auto_increment.result @@ -183,3 +183,47 @@ a 32 42 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); +insert into t1 values(null); +ERROR 23000: Duplicate entry '125' for key 1 +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t1 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +103 0 +114 0 +125 0 +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); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select a, mod(a-@@auto_increment_offset,@@auto_increment_increment) from t2 order by a; +a mod(a-@@auto_increment_offset,@@auto_increment_increment) +251 0 +create table t3 like t1; +set auto_increment_increment=1000; +set auto_increment_offset=700; +insert into t3 values(null); +Warnings: +Warning 1264 Out of range value adjusted for column 'a' at row 1 +select * from t3 order by a; +a +127 +select * from t1 order by a; +a +103 +114 +125 +select * from t2 order by a; +a +251 +select * from t3 order by a; +a +127 +drop table t1,t2,t3; diff --git a/mysql-test/r/rpl_insert_id.result b/mysql-test/r/rpl_insert_id.result index 20538c705a0..3aa82bf1d63 100644 --- a/mysql-test/r/rpl_insert_id.result +++ b/mysql-test/r/rpl_insert_id.result @@ -145,3 +145,68 @@ id last_id drop function bug15728; drop function bug15728_insert; drop table t1, t2; +create table t1 (n int primary key auto_increment not null, +b int, unique(b)); +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; +n b +2 50 +3 100 +4 150 +truncate table t1; +set sql_log_bin=1; +insert into t1 values(null,100); +select * from t1 order by n; +n b +1 100 +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; +n b +1 100 +replace into t1 values(null,100),(null,350); +select * from t1 order by n; +n b +2 100 +3 350 +select * from t1 order by n; +n b +2 100 +3 350 +insert into t1 values (NULL,400),(3,500),(NULL,600) on duplicate key UPDATE n=1000; +select * from t1 order by n; +n b +2 100 +4 400 +1000 350 +1001 600 +select * from t1 order by n; +n b +2 100 +4 400 +1000 350 +1001 600 +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; +n b +1 100 +insert into t1 values(null,200),(null,300); +delete from t1 where b <> 100; +select * from t1 order by n; +n b +1 100 +insert into t1 values(null,100),(null,350) on duplicate key update n=2; +select * from t1 order by n; +n b +2 100 +3 350 +select * from t1 order by n; +n b +2 100 +3 350 +drop table t1; diff --git a/mysql-test/r/view_grant.result b/mysql-test/r/view_grant.result index 0431957f602..7f63d790fb8 100644 --- a/mysql-test/r/view_grant.result +++ b/mysql-test/r/view_grant.result @@ -659,3 +659,56 @@ DROP VIEW test2.t3; DROP TABLE test2.t1, test1.t0; DROP DATABASE test2; DROP DATABASE test1; +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; +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(); +CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER +BEGIN +DECLARE cu VARCHAR(77); +CALL p1(cu); +RETURN cu; +END| +CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu; +CREATE USER mysqltest_u1@localhost; +GRANT ALL ON test.* TO mysqltest_u1@localhost; + +The following tests should all return 1. + +SELECT CURRENT_USER() = 'mysqltest_u1@localhost'; +CURRENT_USER() = 'mysqltest_u1@localhost' +1 +SELECT f1() = 'mysqltest_u1@localhost'; +f1() = 'mysqltest_u1@localhost' +1 +CALL p1(@cu); +SELECT @cu = 'mysqltest_u1@localhost'; +@cu = 'mysqltest_u1@localhost' +1 +SELECT f2() = 'mysqltest_u1@localhost'; +f2() = 'mysqltest_u1@localhost' +1 +SELECT cu = 'root@localhost' FROM v1; +cu = 'root@localhost' +1 +SELECT cu = 'root@localhost' FROM v2; +cu = 'root@localhost' +1 +SELECT cu = 'root@localhost' FROM v3; +cu = 'root@localhost' +1 +DROP VIEW v3; +DROP FUNCTION f2; +DROP PROCEDURE p1; +DROP FUNCTION f1; +DROP VIEW v2; +DROP VIEW v1; +DROP USER mysqltest_u1@localhost; diff --git a/mysql-test/r/wait_timeout.result b/mysql-test/r/wait_timeout.result index 683986abf5d..b865a17454d 100644 --- a/mysql-test/r/wait_timeout.result +++ b/mysql-test/r/wait_timeout.result @@ -1,3 +1,7 @@ +select 0; +0 +0 +flush status; select 1; 1 1 diff --git a/mysql-test/std_data/init_file.dat b/mysql-test/std_data/init_file.dat index 6105ca2ac1b..814e968eb31 100644 --- a/mysql-test/std_data/init_file.dat +++ b/mysql-test/std_data/init_file.dat @@ -1 +1,29 @@ select * from mysql.user as t1, mysql.user as t2, mysql.user as t3; +use test; + +drop table if exists t1; +create table t1 (x int); +drop table if exists t2; +create table t2 (y int); + +drop procedure if exists p1; +create definer=root@localhost procedure p1() select * from t1; +call p1(); +drop procedure p1; + +create definer=root@localhost procedure p1() insert into t1 values (3),(5),(7); +call p1(); + +drop function if exists f1; +create definer=root@localhost function f1() returns int return (select count(*) from t1); +insert into t2 set y = f1()*10; + +drop view if exists v1; +create definer=root@localhost view v1 as select f1(); +insert into t2 (y) select * from v1; + +create trigger trg1 after insert on t2 for each row insert into t1 values (new.y); +insert into t2 values (11), (13); +drop procedure p1; +drop function f1; +drop view v1; 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 |