MySQL Bugs: #21416: SP: Recursion level higher than zero needed for non-recursive call

Bug #21416	SP: Recursion level higher than zero needed for non-recursive call
Submitted:	2 Aug 2006 14:34	Modified:	21 Sep 2006 2:35
Reporter:	Andrey Hristov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.1, 5.0	OS:	Linux (Linux)
Assigned to:	Andrey Hristov	CPU Architecture:	Any

Description:
A procedure which in turn does show create procedure of itself, gives an error that the recursion level is 0, though there is no recursive call at all.

How to repeat:
mysql> select version();
+------------------------------------+
| version()                          |
+------------------------------------+
| 5.1.12-beta-valgrind-max-debug-log |
+------------------------------------+
1 row in set (0.00 sec)

mysql> use db2;
Database changed
mysql> create procedure proc_show() show create procedure proc_show;
Query OK, 0 rows affected (0.04 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> create procedure proc_show2() show create function func1;
Query OK, 0 rows affected (0.04 sec)

mysql> create function func1() returns int return 1;
Query OK, 0 rows affected (0.04 sec)

mysql> call proc_show2();
+----------+----------+-------------------------------------------------------------------------------+
| Function | sql_mode | Create Function                                                               |
+----------+----------+-------------------------------------------------------------------------------+
| func1    |          | CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
return 1 |
+----------+----------+-------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> set max_sp_recursion_depth=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
+-----------+----------+-------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                                          |
+-----------+----------+-------------------------------------------------------------------------------------------+
| proc_show |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_show`()
show create procedure proc_show |
+-----------+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Thank you for the bug report. Verified as described:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db2
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create procedure proc_show() show create procedure proc_show;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> create procedure proc_show2() show create function func1;
Query OK, 0 rows affected (0.00 sec)

mysql> create function func1() returns int return 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show2();
+----------+----------+-------------------------------------------------------------------------------+
| Function | sql_mode | Create Function                                                               |
+----------+----------+-------------------------------------------------------------------------------+
| func1    |          | CREATE DEFINER=`root`@`localhost` FUNCTION `func1`() RETURNS int(11)
return 1 | 
+----------+----------+-------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call proc_show();
ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine proc_show
mysql> set max_sp_recursion_depth=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc_show();
+-----------+----------+-------------------------------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                                          |
+-----------+----------+-------------------------------------------------------------------------------------------+
| proc_show |          | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_show`()
show create procedure proc_show | 
+-----------+----------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10043

ChangeSet@1.2244, 2006-08-04 12:46:54+02:00, andrey@lmy004. +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
  
  Actually there is no recursive call but the limit is checked.
  
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10044

ChangeSet@1.2244, 2006-08-04 12:50:49+02:00, andrey@lmy004. +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
  
  Actually there is no recursive call but the limit is checked.
  
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

Pushed to 5.1.12.

The previons comment is wrong---the patch wasn't pushed yet.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10851

ChangeSet@1.2244, 2006-08-24 19:36:26+02:00, andrey@example.com +3 -0
  Fix for bug#21416 SP: Recursion level higher than zero needed for non-recursive call
  
  The following procedure was not possible if max_sp_recursion_depth is 0
  create procedure show_proc() show create procedure show_proc;
    
  Actually there is no recursive call but the limit is checked.
    
  Solved by temporarily increasing the thread's limit just before the fetch from cache
  and decreasing after that.

Queued in 5.0-rt

Pushed into 5.0.25 and 5.1.12.

Noted in 5.0.25, 5.1.12 changelogs.

With max_sp_recursion set to 0, a stored procedure that executed a
SHOW CREATE PROCEDURE statement for itself triggered a recursion
limit exceeded error, though the statement involves no recursion.