Quick note, I know this question has been asked here, but the ‘answer’ gives an alternative which doesn’t involve recursion. I want to get recursion working. Here’s some code I wrote to demonstrate the problem:
CREATE OR REPLACE PROCEDURE `test` (x INT)
BEGIN
SHOW GLOBAL VARIABLES LIKE 'max_sp_recursion_depth';
IF x = 0 THEN
SELECT 1;
ELSE
CALL `test`(x-1);
END IF;
END
Returns:
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_sp_recursion_depth | 64 |
+------------------------+-------+
1 row in set (0.001 sec)
ERROR 1456 (HY000) at line 43 in file: 'workspace.sql': Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine test
What I’ve tried
I will update this as I try more things:
- setting the variable via the MariaDB CLI
- setting the variable via PHPMyAdmin
- restarting MariaDB
- restarted the entire server
- restarted my computer (currently running in a development environment on my mac) and the variable has reset to 0
2
Answers
I added
To the my.cnf file and that fixed the issue. Still unclear as to why the variable wasn't set via the command line, but 🤷 it works. Although I am still interested so if anyone sees this and wants to elaborate, you're an amazing human!
does not have any effect on the current connection; only future connections. (Exception: Some variables are “only global”; those probably do show up immediately.)
Is what to do to make it work for the current connection.