Error 1456 Mysql
Contents |
Andrey Hristov Email Updates: Status: Closed Impact on me: None mysql set max_sp_recursion_depth variable Category:MySQL Server: Stored Routines Severity:S3 (Non-critical) Version:5.1, 5.0 OS:Linux thread stack overrun mysql stored procedure (Linux) Assigned to: Andrey Hristov View Add Comment Files Developer Edit Submission View Progress Log
Change Max_sp_recursion_depth
Contributions [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 | +-----------+----------+---------------------
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow http://bugs.mysql.com/21416 is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Recursion limit exceeded in non-recusrive procedure up vote 1 down vote favorite I have a procedure which calls another procedure. They never call back to each other or call themselves, but http://stackoverflow.com/questions/29111791/recursion-limit-exceeded-in-non-recusrive-procedure I am getting an error response indicating that they are. The procedures are as follows: CREATE PROCEDURE grantPermission (perm VARCHAR(30), target VARCHAR(30), id VARCHAR(8), host VARCHAR(45), passwd VARCHAR(45)) BEGIN SET @setPermissionCmd = CONCAT('GRANT ', perm, ' ON ', target, ' TO ''', id, '''@''', host, ''' IDENTIFIED BY ''', passwd, ''';'); PREPARE setPermissionStmt FROM @setPermissionCmd; EXECUTE setPermissionStmt; DEALLOCATE PREPARE setPermissionStmt; FLUSH PRIVILEGES; END and CREATE PROCEDURE grantAdmin (id VARCHAR(8), host VARCHAR(45), passwd VARCHAR(45)) BEGIN CALL grantPermission('EXECUTE', 'PROCEDURE createUser', id, host, passwd); CALL grantPermission('EXECUTE', 'PROCEDURE grantAdmin', id, host, passwd); CALL grantPermission('EXECUTE', 'PROCEDURE revokeAdmin', id, host, passwd); CALL grantPermission('INSERT,UPDATE', 'TaskType', id, host, passwd); CALL grantPermission('UPDATE', 'User', id, host, passwd); UPDATE User SET isAdmin=1 WHERE dbUser=id; FLUSH PRIVILEGES; END When I call the second procedure, I get the following response: MariaDB [pattsdb]> CALL grantAdmin('patts', '%', 'patts'); ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine grantAdmin Why does it think
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project https://www.experts-exchange.com/questions/21678864/Problem-with-recursive-call-of-Stored-Procedure.html Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > Problem with recursive call of Stored Procedure Want to Advertise Here? Solved Problem with recursive call of Stored Procedure Posted on 2005-12-29 MySQL Server 1 Verified Solution error 1456 4 Comments 5,077 Views Last Modified: 2010-07-27 In trying to run recursive call of a stored procedure, I get this error message: "ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine pickup_HolidaysCheck" I am using version 5.0.17, which is supposed to allow recursive calls. Can anyone tell me how to fix this? I am using error 1456 mysql version 5.0.17, on Mac OS X 10.4 Many tanks in advance. 0 Question by:javaQQ Facebook Twitter LinkedIn Google LVL 30 Best Solution bytodd_farmer Also, it appears that this can be set dynamically: set @@max_sp_recursion_depth=5; Go to Solution 4 Comments LVL 30 Overall: Level 30 MySQL Server 29 Message Expert Comment by:todd_farmer2005-12-29 Haven't tried this myself, but have you attempted to put the following in your my.cnf file: max_sp_recursion_depth=5 (or whatever level of recursion you need)? 0 LVL 30 Overall: Level 30 MySQL Server 29 Message Accepted Solution by:todd_farmer2005-12-29 Also, it appears that this can be set dynamically: set @@max_sp_recursion_depth=5; 0 Message Author Comment by:javaQQ2005-12-29 Hi Todd; I tried your suggestion: mysql> set @@max_sp_recursion_depth=5; Query OK, 0 rows affected (0.26 sec) However I still got the same error message: "ERROR 1456 (HY000): Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine pickup_HolidaysCheck" This seems to have fixed that problem: mysql> set @@max_sp_recursion_depth=5; | Query OK, 0 rows affected (0.00 sec) mysql> FLUSH TABLES | Query OK, 0 rows affected (0.14 sec) I am not experienced in manipulatinng
be down. Please try the request again. Your cache administrator is webmaster. Generated Mon, 10 Oct 2016 12:54:38 GMT by s_ac15 (squid/3.5.20)