skip to Main Content

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


  1. Chosen as BEST ANSWER

    I added

    max_sp_recursion_depth=255
    

    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!


  2. SET GLOBAL ...
    

    does not have any effect on the current connection; only future connections. (Exception: Some variables are “only global”; those probably do show up immediately.)

    SET SESSION ...
    

    Is what to do to make it work for the current connection.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search