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