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

[2 Aug 2006 14:34] Andrey Hristov
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)
[2 Aug 2006 17:41] Miguel Solorzano
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>
[4 Aug 2006 10:47] Bugs System
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.
[4 Aug 2006 10:51] Bugs System
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.
[11 Aug 2006 8:51] Tomash Brechko
Pushed to 5.1.12.
[11 Aug 2006 9:03] Tomash Brechko
The previons comment is wrong---the patch wasn't pushed yet.
[24 Aug 2006 17:40] Bugs System
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.
[24 Aug 2006 17:45] Andrey Hristov
Queued in 5.0-rt
[31 Aug 2006 10:19] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12.
[21 Sep 2006 2:35] Paul Dubois
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.