From a7abddeffa6a760ce948c2dfb007cdf3f1a369d5 Mon Sep 17 00:00:00 2001 From: Michael Widenius Date: Fri, 9 Mar 2018 14:05:35 +0200 Subject: Create 'main' test directory and move 't' and 'r' there --- mysql-test/t/sp_trans.test | 707 --------------------------------------------- 1 file changed, 707 deletions(-) delete mode 100644 mysql-test/t/sp_trans.test (limited to 'mysql-test/t/sp_trans.test') diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test deleted file mode 100644 index a9759b99269..00000000000 --- a/mysql-test/t/sp_trans.test +++ /dev/null @@ -1,707 +0,0 @@ -# -# tests that require InnoDB... -# - --- source include/have_log_bin.inc --- source include/have_innodb.inc - -set SQL_MODE=""; - -disable_query_log; -call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); -enable_query_log; - ---disable_warnings -drop table if exists t1, t2, t3; ---enable_warnings - -delimiter |; - -# -# BUG#8850: Truncate table in a stored procedure locks the tables -# ---disable_warnings -drop procedure if exists bug8850| ---enable_warnings -create table t1 (a int) engine=innodb| -create procedure bug8850() -begin - truncate table t1; insert t1 values (1); rollback; -end| - -set autocommit=0| -insert t1 values (2)| -call bug8850()| -commit| -select * from t1| - -call bug8850()| -set autocommit=1| -select * from t1| -drop table t1| -drop procedure bug8850| - - -# -# BUG#10015: Crash in InnoDB if stored routines are used -# (crash happens in auto-commit mode) -# ---disable_warnings -drop function if exists bug10015_1| -drop function if exists bug10015_2| -drop function if exists bug10015_3| -drop function if exists bug10015_4| -drop function if exists bug10015_5| -drop function if exists bug10015_6| -drop function if exists bug10015_7| -drop procedure if exists bug10015_8| ---enable_warnings -create table t1 (id int) engine=innodb| -create table t2 (id int primary key, j int) engine=innodb| -insert into t1 values (1),(2),(3)| -create function bug10015_1() returns int return (select count(*) from t1)| -select *, bug10015_1() from t1| -drop function bug10015_1| -# Test couple of a bit more complex cases -create function bug10015_2() returns int - begin - declare i, s int; - set i:= (select min(id) from t1); - set s:= (select max(id) from t1); - return (s - i); - end| -select *, bug10015_2() from t1| -drop function bug10015_2| -create function bug10015_3() returns int - return (select max(a.id - b.id) from t1 as a, t1 as b where a.id >= b.id)| -select *, bug10015_3() from t1| -drop function bug10015_3| -create function bug10015_4(i int) returns int - begin - declare m int; - set m:= (select max(id) from t2); - insert into t2 values (i, m); - return m; - end| -select *, bug10015_4(id) from t1| -select * from t2| -drop function bug10015_4| -# Now let us test how statement rollback works -# This function will cause the whole stmt to be rolled back, -# there should not be any traces left. -create function bug10015_5(i int) returns int - begin - if (i = 5) then - insert into t2 values (1, 0); - end if; - return i; - end| ---error ER_DUP_ENTRY -insert into t1 values (bug10015_5(4)), (bug10015_5(5))| -select * from t1| -drop function bug10015_5| -# Thanks to error-handler this function should not cause rollback -# of statement calling it. But insert statement in it should be -# rolled back completely and don't leave any traces in t2. -# Unfortunately we can't implement such behavior in 5.0, so it -# is something to be fixed in later 5.* releases (TODO). -create function bug10015_6(i int) returns int - begin - declare continue handler for sqlexception set @error_in_func:= 1; - if (i = 5) then - insert into t2 values (4, 0), (1, 0); - end if; - return i; - end| -set @error_in_func:= 0| -insert into t1 values (bug10015_6(5)), (bug10015_6(6))| -select @error_in_func| -select * from t1| -select * from t2| -drop function bug10015_6| -# Let us test that we don't allow any statements causing transaction -# commit in stored functions (we test only most interesting cases here). -# Cases which can be caught at creation time: ---error 1422 -create function bug10015_7() returns int - begin - alter table t1 add k int; - return 1; - end| ---error 1422 -create function bug10015_7() returns int - begin - start transaction; - return 1; - end| ---error 1422 -create function bug10015_7() returns int - begin - drop table t1; - return 1; - end| -# It should be OK to drop temporary table. -create function bug10015_7() returns int - begin - drop temporary table t1; - return 1; - end| -drop function bug10015_7| ---error 1422 -create function bug10015_7() returns int - begin - commit; - return 1; - end| -# Now let us test cases which we can catch only at run-time: -create function bug10015_7() returns int - begin - call bug10015_8(); - return 1; - end| -create procedure bug10015_8() alter table t1 add k int| ---error 1422 -select *, bug10015_7() from t1| -drop procedure bug10015_8| -create procedure bug10015_8() start transaction| ---error 1422 -select *, bug10015_7() from t1| -drop procedure bug10015_8| -# Again it is OK to drop temporary table -# We are surpressing warnings since they are not essential -create procedure bug10015_8() drop temporary table if exists t1_temp| ---disable_warnings -select *, bug10015_7() from t1| ---enable_warnings -drop procedure bug10015_8| -create procedure bug10015_8() commit| ---error 1422 -select *, bug10015_7() from t1| -drop procedure bug10015_8| -drop function bug10015_7| -drop table t1, t2| - - -# -# BUG#13825 "Triggers: crash if release savepoint". -# Also general test for handling of savepoints in stored routines. -# -# According to SQL standard we should establish new savepoint -# level before executing stored function/trigger and destroy -# this savepoint level after execution. Stored procedures by -# default should be executed using the same savepoint level -# as their caller (to execute stored procedure using new -# savepoint level one should explicitly specify NEW SAVEPOINT -# LEVEL clause in procedure creation statement which MySQL -# does not support yet). ---disable_warnings -drop function if exists bug13825_0| -drop function if exists bug13825_1| -drop function if exists bug13825_2| -drop function if exists bug13825_3| -drop function if exists bug13825_4| -drop function if exists bug13825_5| -drop procedure if exists bug13825_0| -drop procedure if exists bug13825_1| -drop procedure if exists bug13825_2| -drop table if exists t1| ---enable_warnings -create table t1 (i int) engine=innodb| -create table t2 (i int) engine=innodb| -create function bug13825_0() returns int -begin - rollback to savepoint x; - return 1; -end| -create function bug13825_1() returns int -begin - release savepoint x; - return 1; -end| -create function bug13825_2() returns int -begin - insert into t1 values (2); - savepoint x; - insert into t1 values (3); - rollback to savepoint x; - insert into t1 values (4); - return 1; -end| -create procedure bug13825_0() -begin - rollback to savepoint x; -end| -create procedure bug13825_1() -begin - release savepoint x; -end| -create procedure bug13825_2() -begin - savepoint x; -end| -insert into t2 values (1)| -create trigger t2_bi before insert on t2 for each row - rollback to savepoint x| -create trigger t2_bu before update on t2 for each row - release savepoint x| -create trigger t2_bd before delete on t2 for each row -begin - insert into t1 values (2); - savepoint x; - insert into t1 values (3); - rollback to savepoint x; - insert into t1 values (4); -end| -create function bug13825_3(rb int) returns int -begin - insert into t1 values(1); - savepoint x; - insert into t1 values(2); - if rb then - rollback to savepoint x; - end if; - insert into t1 values(3); - return rb; -end| -create function bug13825_4() returns int -begin - savepoint x; - insert into t1 values(2); - rollback to savepoint x; - return 0; -end| -create function bug13825_5(p int) returns int -begin - savepoint x; - insert into t2 values(p); - rollback to savepoint x; - insert into t2 values(p+1); - return p; -end| -set autocommit= 0| -# Test of savepoint level handling for stored functions and triggers -begin | -insert into t1 values (1)| -savepoint x| ---error ER_SP_DOES_NOT_EXIST -set @a:= bug13825_0()| ---error ER_SP_DOES_NOT_EXIST -insert into t2 values (2)| ---error ER_SP_DOES_NOT_EXIST -set @a:= bug13825_1()| ---error ER_SP_DOES_NOT_EXIST -update t2 set i = 2| -set @a:= bug13825_2()| -select * from t1| -rollback to savepoint x| -select * from t1| -delete from t2| -select * from t1| -rollback to savepoint x| -select * from t1| -# Of course savepoints set in function should not be visible from its caller -release savepoint x| -set @a:= bug13825_2()| -select * from t1| ---error ER_SP_DOES_NOT_EXIST -rollback to savepoint x| -delete from t1| -commit| -# Test of savepoint level handling for stored procedures -begin| -insert into t1 values (5)| -savepoint x| -insert into t1 values (6)| -call bug13825_0()| -select * from t1| -call bug13825_1()| ---error ER_SP_DOES_NOT_EXIST -rollback to savepoint x| -savepoint x| -insert into t1 values (7)| -call bug13825_2()| -rollback to savepoint x| -select * from t1| -delete from t1| -commit| -set autocommit= 1| -# Let us test that savepoints work inside of functions -# even in auto-commit mode -select bug13825_3(0)| -select * from t1| -delete from t1| -select bug13825_3(1)| -select * from t1| -delete from t1| -# Curious case: rolling back to savepoint which is set by first -# statement in function should not rollback whole transaction. -set autocommit= 0| -begin| -insert into t1 values (1)| -set @a:= bug13825_4()| -select * from t1| -delete from t1| -commit| -set autocommit= 1| -# Other curious case: savepoint in the middle of statement -drop table t2| -create table t2 (i int) engine=innodb| -insert into t1 values (1), (bug13825_5(2)), (3)| -select * from t1| -select * from t2| -# Cleanup -drop function bug13825_0| -drop function bug13825_1| -drop function bug13825_2| -drop function bug13825_3| -drop function bug13825_4| -drop function bug13825_5| -drop procedure bug13825_0| -drop procedure bug13825_1| -drop procedure bug13825_2| -drop table t1, t2| - - -# -# BUG#14840: CONTINUE handler problem -# ---disable_warnings -drop table if exists t3| -drop procedure if exists bug14840_1| -drop procedure if exists bug14840_2| ---enable_warnings - -create table t3 -( - x int, - y int, - primary key (x) -) engine=InnoDB| - -# This used to hang the client since the insert returned with an -# error status (left over from the update) even though it succeeded, -# which caused the execution to end at that point. -create procedure bug14840_1() -begin - declare err int default 0; - declare continue handler for sqlexception - set err = err + 1; - - start transaction; - update t3 set x = 1, y = 42 where x = 2; - insert into t3 values (3, 4711); - if err > 0 then - rollback; - else - commit; - end if; - select * from t3; -end| - -# A simpler (non-transactional) case: insert at select should be done -create procedure bug14840_2() -begin - declare err int default 0; - declare continue handler for sqlexception - begin - set err = err + 1; - select err as 'Ping'; - end; - - update t3 set x = 1, y = 42 where x = 2; - update t3 set x = 1, y = 42 where x = 2; - insert into t3 values (3, 4711); - select * from t3; -end| - -insert into t3 values (1, 3), (2, 5)| -call bug14840_1()| - -delete from t3| -insert into t3 values (1, 3), (2, 5)| -call bug14840_2()| - -drop procedure bug14840_1| -drop procedure bug14840_2| -drop table t3| - - -# -# BUG#10656: Stored Procedure - Create index and Truncate table command error -# ---disable_warnings -drop procedure if exists bug10656_create_index| -drop procedure if exists bug10656_myjoin| -drop procedure if exists bug10656_truncate_table| ---enable_warnings - -CREATE TABLE t3 ( - `ID` int(11) default NULL, - `txt` char(5) default NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1| - -INSERT INTO t3 (`ID`,`txt`) VALUES - (1,'a'), (2,'b'), (3,'c'), (4,'d')| - -CREATE TABLE t4 ( - `ID` int(11) default NULL, - `txt` char(5) default NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1| - -INSERT INTO t4 (`ID`,`txt`) VALUES - (1,'a'), (2,'b'), (3,'c'), (4,'d')| - -create procedure bug10656_create_index() -begin - create index bug10656_my_index on t3 (ID); -end| -call bug10656_create_index()| - -create procedure bug10656_myjoin() -begin - update t3, t4 set t3.txt = t4.txt where t3.id = t4.id; -end| -call bug10656_myjoin()| - -create procedure bug10656_truncate_table() -begin - truncate table t3; -end| -call bug10656_truncate_table()| - - -drop procedure bug10656_create_index| -drop procedure bug10656_myjoin| -drop procedure bug10656_truncate_table| -drop table t3, t4| - -# -# BUG#3448 -# ---disable_warnings -create table t3 ( - a int primary key, - ach char(1) -) engine = innodb| - -create table t4 ( - b int primary key, - bch char(1) -) engine = innodb| ---enable_warnings - -insert into t3 values (1 , 'aCh1' ) , ('2' , 'aCh2')| -insert into t4 values (1 , 'bCh1' )| - ---disable_warnings -drop procedure if exists bug3448| ---enable_warnings -create procedure bug3448() - select * from t3 inner join t4 on t3.a = t4.b| - -select * from t3 inner join t4 on t3.a = t4.b| -call bug3448()| -call bug3448()| - -drop procedure bug3448| -drop table t3, t4| - -# -# BUG#14210: "Simple query with > operator on large table gives server -# crash" -# Check that cursors work in case when HEAP tables are converted to -# MyISAM -# ---disable_warnings -drop procedure if exists bug14210| ---enable_warnings -set @@session.max_heap_table_size=16384| -select @@session.max_heap_table_size| -# To trigger the memory corruption the original table must be InnoDB. -# No harm if it's not, so don't warn if the suite is run with --skip-innodb ---disable_warnings -create table t3 (a char(255)) engine=InnoDB| ---enable_warnings -create procedure bug14210_fill_table() -begin - declare table_size, max_table_size int default 0; - select @@session.max_heap_table_size into max_table_size; - delete from t3; - insert into t3 (a) values (repeat('a', 255)); - repeat - insert into t3 select a from t3; - select count(*)*255 from t3 into table_size; - until table_size > max_table_size*2 end repeat; -end| ---disable_warnings -call bug14210_fill_table()| ---enable_warnings -drop procedure bug14210_fill_table| -create table t4 like t3| - -create procedure bug14210() -begin - declare a char(255); - declare done int default 0; - declare c cursor for select * from t3; - declare continue handler for sqlstate '02000' set done = 1; - open c; - repeat - fetch c into a; - if not done then - insert into t4 values (upper(a)); - end if; - until done end repeat; - close c; -end| -call bug14210()| -select count(*) from t4| - -drop table t3, t4| -drop procedure bug14210| -set @@session.max_heap_table_size=default| - -# -# BUG#7787: Stored procedures: improper warning for "grant execute" statement -# - -# Prepare. - -CREATE DATABASE db_bug7787| -use db_bug7787| - -# Test. - -CREATE PROCEDURE p1() - SHOW ENGINE INNODB STATUS; | - -GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost| - -# Cleanup. - -DROP DATABASE db_bug7787| -drop user user_bug7787@localhost| -use test| - -# -# Bug#13575 SP funcs in select with distinct/group and order by can -# produce bad data -# -create table t3 (f1 int, f2 varchar(3), primary key(f1)) engine=innodb| -insert into t3 values (1,'aaa'),(2,'bbb'),(3,'ccc')| -CREATE FUNCTION bug13575 ( p1 integer ) -returns varchar(3) -BEGIN -DECLARE v1 VARCHAR(10) DEFAULT null; -SELECT f2 INTO v1 FROM t3 WHERE f1 = p1; -RETURN v1; -END| -select distinct f1, bug13575(f1) from t3 order by f1| -drop function bug13575| -drop table t3| - -# -# BUG#11758414: Default storage_engine not honored when set -# from within a stored procedure -# -SELECT @@GLOBAL.storage_engine INTO @old_engine| -SET @@GLOBAL.storage_engine=InnoDB| -SET @@SESSION.storage_engine=InnoDB| -# show defaults at define-time -SHOW GLOBAL VARIABLES LIKE 'storage_engine'| -SHOW SESSION VARIABLES LIKE 'storage_engine'| -CREATE PROCEDURE bug11758414() -BEGIN - SET @@GLOBAL.storage_engine="MyISAM"; - SET @@SESSION.storage_engine="MyISAM"; - # show defaults at execution time / that setting them worked - SHOW GLOBAL VARIABLES LIKE 'storage_engine'; - SHOW SESSION VARIABLES LIKE 'storage_engine'; - CREATE TABLE t1 (id int); - CREATE TABLE t2 (id int) ENGINE=InnoDB; - # show we're heeding the default (at run-time, not parse-time!) - SHOW CREATE TABLE t1; - # show that we didn't break explicit override with ENGINE=... - SHOW CREATE TABLE t2; -END; -| -CALL bug11758414| -# show that changing defaults within SP stuck -SHOW GLOBAL VARIABLES LIKE 'storage_engine'| -SHOW SESSION VARIABLES LIKE 'storage_engine'| -DROP PROCEDURE bug11758414| -DROP TABLE t1, t2| -SET @@GLOBAL.storage_engine=@old_engine| - ---echo # ---echo # End of 5.1 tests ---echo # - ---echo # ---echo # Bug #35877 Update .. WHERE with function, constraint violation, crash ---echo # - ---disable_warnings -DROP TABLE IF EXISTS t1_aux| -DROP TABLE IF EXISTS t1_not_null| -DROP FUNCTION IF EXISTS f1_two_inserts| ---enable_warnings - --- echo # MyISAM test -CREATE TABLE t1_not_null (f1 BIGINT, f2 BIGINT NOT NULL)| -CREATE TABLE t1_aux (f1 BIGINT, f2 BIGINT)| -INSERT INTO t1_aux VALUES (1,1)| - -CREATE FUNCTION f1_two_inserts() returns INTEGER -BEGIN - INSERT INTO t1_not_null SET f1 = 10, f2 = NULL; - RETURN 1; -END| - --- error ER_BAD_NULL_ERROR -UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()| - --- echo # InnoDB test -ALTER TABLE t1_not_null ENGINE = InnoDB| -ALTER TABLE t1_aux ENGINE = InnoDB| - --- error ER_BAD_NULL_ERROR -UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()| - -DROP TABLE t1_aux, t1_not_null| -DROP FUNCTION f1_two_inserts| - ---echo # ---echo # Bug#49938: Failing assertion: inode or deadlock in fsp/fsp0fsp.c ---echo # - ---disable_warnings -DROP PROCEDURE IF EXISTS p1| -DROP TABLE IF EXISTS t1| ---enable_warnings - -CREATE TABLE t1 (a INT) ENGINE=INNODB| - -CREATE PROCEDURE p1() -BEGIN - TRUNCATE TABLE t1; -END| - -LOCK TABLES t1 WRITE| -CALL p1()| -FLUSH TABLES; -UNLOCK TABLES| -CALL p1()| - -DROP PROCEDURE p1| -DROP TABLE t1| - -# -# BUG#NNNN: New bug synopsis -# -#--disable_warnings -#drop procedure if exists bugNNNN| -#--enable_warnings -#create procedure bugNNNN... - -delimiter ;| -- cgit v1.2.1