1
############# mysql-test\t\max_sp_recursion_depth_func.test ###################
3
# Variable Name: max_sp_recursion_depth #
5
# Access Type: Dynamic #
11
# Creation Date: 2008-03-02 #
12
# Author: Sharique Abdullah #
14
# Description: Test Cases of Dynamic System Variable "max_sp_recursion_depth #
15
# that checks behavior of this variable in the following ways #
16
# * Functionality based on different values #
18
#Reference:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19
# option_mysqld_max_sp_recursion_depth #
21
###############################################################################
28
SET @session_max_recursion_depth = @@SESSION.max_sp_recursion_depth;
31
--echo '#--------------------FN_DYNVARS_099_01-------------------------#'
32
##########################################################
33
# Test behavior of variable on new connection # 01 #
34
##########################################################
35
--echo ** Connecting test_con1 using username 'root' **
36
CONNECT (test_con1,localhost,root,,);
37
--echo ** Connection test_con1 **
41
# Setting session value of variable
42
SET @@session.max_sp_recursion_depth = 10;
43
SELECT @@session.max_sp_recursion_depth;
44
# create procedure to add rows
47
DROP PROCEDURE IF EXISTS sp_addRecords;
52
CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
56
CALL sp_addRecords(var1+1,var2);
64
CALL sp_addRecords(0,8);
66
--echo '#--------------------FN_DYNVARS_099_02-------------------------#'
67
##########################################################
68
# Test behavior of variable on new connection # 02 #
69
##########################################################
71
--echo ** Connecting test_con2 using username 'root' **
72
CONNECT (test_con2,localhost,root,,);
73
--echo ** Connection test_con2 **
77
# Setting session value of variable and inserting data in table
78
SET @@session.max_sp_recursion_depth = 4;
79
SELECT @@session.max_sp_recursion_depth;
80
# create procedure to add rows
83
DROP PROCEDURE IF EXISTS sp_addRecords1;
88
CREATE PROCEDURE sp_addRecords1 (IN var1 INT,IN var2 INT)
92
CALL sp_addRecords1(var1+1,var2);
100
CALL sp_addRecords1(0,4);
105
--echo '#---------------------FN_DYNVARS_99_03----------------------#'
106
####################################################################
107
# Check if max_sp_recursion_depth value is set to 10 #
108
####################################################################
109
SET @@max_sp_recursion_depth = 10;
110
# create procedure to add rows
113
DROP PROCEDURE IF EXISTS sp_addRecords2;
118
CREATE PROCEDURE sp_addRecords2(IN var1 INT,IN var2 INT)
122
CALL sp_addRecords2(var1+1,var2);
130
CALL sp_addRecords2(0,8);
133
--echo '#---------------------FN_DYNVARS_99_04----------------------#'
134
###############################################################################
135
#Check if max_sp_recursion_depth value is set lower then called recursion value
136
###############################################################################
139
SET @@max_sp_recursion_depth = 4;
140
# create procedure to add rows
141
#DROP PROCEDURE IF EXISTS sp_addRecords1;
144
DROP PROCEDURE IF EXISTS sp_addRecords3;
149
CREATE PROCEDURE sp_addRecords3 (IN var1 INT,IN var2 INT)
153
CALL sp_addRecords3(var1+1,var2);
160
--echo Expected error 'SP Recursion limit'
161
--ERROR ER_SP_RECURSION_LIMIT
162
CALL sp_addRecords3(0,8);
168
--echo ** Connection default **
171
--echo ** Disconnecting test_con1, test_con2 **
172
disconnect test_con1;
173
disconnect test_con2;
176
DROP PROCEDURE IF EXISTS sp_addRecords;
177
DROP PROCEDURE IF EXISTS sp_addRecords1;
178
DROP PROCEDURE IF EXISTS sp_addRecords2;
179
DROP PROCEDURE IF EXISTS sp_addRecords3;
182
SET @@SESSION.max_sp_recursion_depth = @session_max_recursion_depth;