Description:
The max_sp_recursion_depth setting is not always being honoured. The recursion depth limit does not always bear any relationship to the setting. Also code can recurse when the max_sp_recursion_depth setting is set to 0 (recursion not allowed).
How to repeat:
Create the test stored procedure recursive_procedure_test as shown.
Then run the code setting the max_sp_recursion_depth setting to 2.
You will see that the setting is not being honoured.
######## test_case_mysql_recursion_bug.sql START ##########
DELIMITER ;;
DROP PROCEDURE IF EXISTS recursive_procedure_test;;
CREATE PROCEDURE recursive_procedure_test (INOUT io_current_run_int INT, IN i_no_of_runs_to_do INT)
BEGIN
DECLARE procedure_status VARCHAR(200);
IF io_current_run_int IS NULL THEN SET io_current_run_int=1; ELSE
SET io_current_run_int = io_current_run_int+1;
END IF;
IF i_no_of_runs_to_do IS NULL THEN SET i_no_of_runs_to_do=0; END IF;
SELECT @@global.max_sp_recursion_depth, io_current_run_int, i_no_of_runs_to_do;
IF (io_current_run_int < i_no_of_runs_to_do)
THEN
SET procedure_status=CONCAT( "Recursing. Run ", io_current_run_int, " of ", i_no_of_runs_to_do);
SELECT procedure_status;
CALL recursive_procedure_test(io_current_run_int, i_no_of_runs_to_do);
END IF;
END;;
DELIMITER ;
######## test_case_mysql_recursion_bug.sql END##########
# Running the test case script
mysql> SET GLOBAL max_sp_recursion_depth=2;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> SET @l_current_run=NULL;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @l_no_of_runs_to_do=5;
Query OK, 0 rows affected (0.00 sec)
mysql> CALL recursive_procedure_test(@l_current_run,@l_no_of_runs_to_do);
+---------------------------------+--------------------+--------------------+
| @@global.max_sp_recursion_depth | io_current_run_int | i_no_of_runs_to_do |
+---------------------------------+--------------------+--------------------+
| 2 | 1 | 5 |
+---------------------------------+--------------------+--------------------+
1 row in set (0.00 sec)
+-----------------------+
| procedure_status |
+-----------------------+
| Recursing. Run 1 of 5 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------+--------------------+--------------------+
| @@global.max_sp_recursion_depth | io_current_run_int | i_no_of_runs_to_do |
+---------------------------------+--------------------+--------------------+
| 2 | 2 | 5 |
+---------------------------------+--------------------+--------------------+
1 row in set (0.00 sec)
+-----------------------+
| procedure_status |
+-----------------------+
| Recursing. Run 2 of 5 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------+--------------------+--------------------+
| @@global.max_sp_recursion_depth | io_current_run_int | i_no_of_runs_to_do |
+---------------------------------+--------------------+--------------------+
| 2 | 3 | 5 |
+---------------------------------+--------------------+--------------------+
1 row in set (0.00 sec)
+-----------------------+
| procedure_status |
+-----------------------+
| Recursing. Run 3 of 5 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------+--------------------+--------------------+
| @@global.max_sp_recursion_depth | io_current_run_int | i_no_of_runs_to_do |
+---------------------------------+--------------------+--------------------+
| 2 | 4 | 5 |
+---------------------------------+--------------------+--------------------+
1 row in set (0.00 sec)
+-----------------------+
| procedure_status |
+-----------------------+
| Recursing. Run 4 of 5 |
+-----------------------+
1 row in set (0.00 sec)
+---------------------------------+--------------------+--------------------+
| @@global.max_sp_recursion_depth | io_current_run_int | i_no_of_runs_to_do |
+---------------------------------+--------------------+--------------------+
| 2 | 5 | 5 |
+---------------------------------+--------------------+--------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
