Debian Codegraph @

mysql-test/suite/sys_vars/t/max_sp_recursion_depth_func.test

raw
1 ############# mysql-test\t\max_sp_recursion_depth_func.test ###################
2 # #
3 # Variable Name: max_sp_recursion_depth #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: NUMERIC #
7 # Default Value: 0 #
8 # Max Value: 25 #
9 # #
10 # #
11 # Creation Date: 2008-03-02 #
12 # Author: Sharique Abdullah #
13 # #
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 #
17 # #
18 #Reference:http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#
19 # option_mysqld_max_sp_recursion_depth #
20 # #
21 ###############################################################################
22
23
24 #
25 # Setup
26 #
27
28 SET @session_max_recursion_depth = @@SESSION.max_sp_recursion_depth;
29
30
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 **
38 CONNECTION test_con1;
39
40
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
45
46 --disable_warnings
47 DROP PROCEDURE IF EXISTS sp_addRecords;
48 --enable_warnings
49
50 DELIMITER //;
51
52 CREATE PROCEDURE sp_addRecords (IN var1 INT,IN var2 INT)
53 BEGIN
54 SELECT var1,var2;
55 IF var1 < var2 THEN
56 CALL sp_addRecords(var1+1,var2);
57 SELECT var1,var2;
58 END IF;
59 END //
60
61 DELIMITER ;//
62
63
64 CALL sp_addRecords(0,8);
65
66 --echo '#--------------------FN_DYNVARS_099_02-------------------------#'
67 ##########################################################
68 # Test behavior of variable on new connection # 02 #
69 ##########################################################
70
71 --echo ** Connecting test_con2 using username 'root' **
72 CONNECT (test_con2,localhost,root,,);
73 --echo ** Connection test_con2 **
74 connection test_con2;
75
76
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
81
82 --disable_warnings
83 DROP PROCEDURE IF EXISTS sp_addRecords1;
84 --enable_warnings
85
86 DELIMITER //;
87
88 CREATE PROCEDURE sp_addRecords1 (IN var1 INT,IN var2 INT)
89 BEGIN
90 SELECT var1,var2;
91 IF var1 < var2 THEN
92 CALL sp_addRecords1(var1+1,var2);
93 SELECT var1,var2;
94 END IF;
95 END //
96
97 DELIMITER ;//
98
99
100 CALL sp_addRecords1(0,4);
101
102
103
104
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
111
112 --disable_warnings
113 DROP PROCEDURE IF EXISTS sp_addRecords2;
114 --enable_warnings
115
116 DELIMITER //;
117
118 CREATE PROCEDURE sp_addRecords2(IN var1 INT,IN var2 INT)
119 BEGIN
120 SELECT var1,var2;
121 IF var1 < var2 THEN
122 CALL sp_addRecords2(var1+1,var2);
123 SELECT var1,var2;
124 END IF;
125 END //
126
127 DELIMITER ;//
128
129
130 CALL sp_addRecords2(0,8);
131
132
133 --echo '#---------------------FN_DYNVARS_99_04----------------------#'
134 ###############################################################################
135 #Check if max_sp_recursion_depth value is set lower then called recursion value
136 ###############################################################################
137
138
139 SET @@max_sp_recursion_depth = 4;
140 # create procedure to add rows
141 #DROP PROCEDURE IF EXISTS sp_addRecords1;
142
143 --disable_warnings
144 DROP PROCEDURE IF EXISTS sp_addRecords3;
145 --enable_warnings
146
147 DELIMITER //;
148
149 CREATE PROCEDURE sp_addRecords3 (IN var1 INT,IN var2 INT)
150 BEGIN
151 SELECT var1,var2;
152 IF var1 < var2 THEN
153 CALL sp_addRecords3(var1+1,var2);
154 SELECT var1,var2;
155 END IF;
156 END //
157
158 DELIMITER ;//
159
160 --echo Expected error 'SP Recursion limit'
161 --ERROR ER_SP_RECURSION_LIMIT
162 CALL sp_addRecords3(0,8);
163
164 #
165 # Cleanup
166 #
167
168 --echo ** Connection default **
169 connection default;
170
171 --echo ** Disconnecting test_con1, test_con2 **
172 disconnect test_con1;
173 disconnect test_con2;
174
175 --disable_warnings
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;
180 --enable_warnings
181
182 SET @@SESSION.max_sp_recursion_depth = @session_max_recursion_depth;