KtqLnQyz

1create database test;
2use test;
3
4delimiter //
5CREATE DEFINER = 'root'@'localhost' FUNCTION `new_func`(in_id INTEGER(11))
6    RETURNS int(11)
7    NOT DETERMINISTIC
8    CONTAINS SQL
9    SQL SECURITY DEFINER
10    COMMENT ''
11BEGIN
12	RETURN (select ifnull(`farid`, 0) from `test_table` where `id` = in_id);
13END;
14//
15delimiter ;
16
17delimiter //
18CREATE DEFINER = 'root'@'localhost' PROCEDURE `new_proc`(IN in_id INTEGER(11))
19    NOT DETERMINISTIC
20    CONTAINS SQL
21    SQL SECURITY DEFINER
22    COMMENT ''
23BEGIN
24	create temporary table temp (id int(11), farid int(11));
25	set @_pid = in_id;
26    
27	while @_pid > 0 do                                      
28		insert into `temp` select * from `test_table` where id = @_pid;
29		set @_pid = new_func(@_pid);
30	end while;
31
32	select * from `temp`;
33	drop table if exists `temp`;
34END;
35//
36delimiter ;
37
38CREATE TABLE `test_table` (
39  `myID` INTEGER UNSIGNED NOT NULL,
40  `id` INTEGER UNSIGNED NOT NULL,
41  `CatName` VARCHAR(255) NOT NULL,
42  PRIMARY KEY(`ID`)
43);
44
45insert into test_table values (1,0,"root");
46
47insert into test_table values (2,1,"home");
48
49insert into test_table values (3,2,"argon");
50
51insert into test_table values (4,3,"docs");
52
53mysql> call new_proc(4);
54ERROR 1436 (HY000): Thread stack overrun:  6440 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld -O thread_stack=#' to specify
55 a bigger stack.