asked on

Problem with recursive call of Stored Procedure

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 version 5.0.17, on Mac OS X 10.4

Many tanks in advance.

todd_farmer

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)?

ASKER CERTIFIED SOLUTION

todd_farmer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

javaQQ

ASKER

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 the configuration variables -- for example, I have not been able to find the my.cnf file -- so if you can give me (or point me toward) some more detailed instructions I would greatly appreciate it.

todd_farmer

Information on the options file (my.cnf) is located here:

http://dev.mysql.com/doc/refman/5.0/en/option-files.html

I don't know that the max_sp_recursion_depth variable can be set there; but it makes sense.