1# 2# Bug #11602: SP with very large body not handled well 3# 4source include/have_sequence.inc; 5 6create table t1 (a int); 7insert into t1 values (1),(2),(3); 8 9let $body=`select repeat('select count(*) into out1 from t1;\n', 3072)`; 10 11delimiter //; 12--disable_query_log 13eval select length('$body') as length// 14eval create procedure test.longprocedure (out out1 int) deterministic 15begin 16 $body 17end// 18--enable_query_log 19 20delimiter ;// 21 22# this is larger than the length above, because it includes the 'begin' and 23# 'end' bits and some whitespace 24select length(routine_definition) from information_schema.routines where routine_schema = 'test' and routine_name = 'longprocedure'; 25 26call test.longprocedure(@value); select @value; 27 28drop procedure test.longprocedure; 29drop table t1; 30# 31# Bug #9819 "Cursors: Mysql Server Crash while fetching from table with 5 32# million records.": 33# To really test the bug, increase the number of loop iterations ($1). 34# For 4 millions set $1 to 22. 35create table t1 (f1 char(100) , f2 mediumint , f3 int , f4 real, f5 numeric); 36insert into t1 (f1, f2, f3, f4, f5) values 37("This is a test case for for Bug#9819", 1, 2, 3.0, 4.598); 38create table t2 like t1; 39let $1=8; 40--disable_query_log 41--disable_result_log 42begin; 43while ($1) 44{ 45 eval insert into t1 select * from t1; 46 dec $1; 47} 48commit; 49--enable_result_log 50--enable_query_log 51select count(*) from t1; 52select count(*) from t2; 53--disable_warnings 54drop procedure if exists p1; 55--enable_warnings 56delimiter |; 57create procedure p1() 58begin 59 declare done integer default 0; 60 declare vf1 char(100) ; 61 declare vf2 mediumint; 62 declare vf3 int ; 63 declare vf4 real ; 64 declare vf5 numeric ; 65 declare cur1 cursor for select f1,f2,f3,f4,f5 from t1; 66 declare continue handler for sqlstate '02000' set done = 1; 67 open cur1; 68 while done <> 1 do 69 fetch cur1 into vf1, vf2, vf3, vf4, vf5; 70 if not done then 71 insert into t2 values (vf1, vf2, vf3, vf4, vf5); 72 end if; 73 end while; 74 close cur1; 75end| 76delimiter ;| 77call p1(); 78select count(*) from t1; 79select count(*) from t2; 80select f1 from t1 limit 1; 81select f1 from t2 limit 1; 82drop procedure p1; 83drop table t1, t2; 84 85# 86# Loops with many iterations 87# (Item_equal must be created in the execution arena) 88# 89create table t1 ( 90 `id1` int unsigned not null default '0', 91 `id2` int unsigned not null default '0', 92 `link_type` int unsigned not null default '0', 93 `visibility` tinyint not null default '0', 94 `data` varchar(255) not null default '', 95 `time` int unsigned not null default '0', 96 `version` int unsigned not null default '0', 97 primary key (id1, link_type, visibility, id2) 98) default collate=latin1_bin; 99 100delimiter //; 101create procedure select_test() 102begin 103 declare id1_cond int; 104 set id1_cond = 1; 105 while id1_cond <= 10000 do 106 select count(*) as cnt from (select id1 from t1 force index (primary) where id1 = id1_cond and link_type = 1 and visibility = 1 order by id2 desc) as t into @cnt; 107 set id1_cond = id1_cond + 1; 108 end while; 109end// 110delimiter ;// 111 112insert t1 select seq, seq, 1, 1, seq, seq, seq from seq_1_to_2000; 113set @before=unix_timestamp(); 114call select_test(); 115 116--let $time=60 117if ($VALGRIND_TEST) 118{ 119 --let $time=600 120} 121 122--disable_query_log 123--eval set @time=$time; 124--enable_query_log 125 126select unix_timestamp() - @before < @time; 127drop procedure select_test; 128drop table t1; 129