非递归过程中超出递归限制

2022-01-15 00:00:00 递归 mariadb mysql stored-procedures

我有一个调用另一个过程的过程.他们从不互相打电话或打电话给自己，但我收到一个错误响应，表明他们是.程序如下:

I have a procedure which calls another procedure. They never call back to each other or call themselves, but 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

和

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 my procedure is recursive? Just because the name is mentioned in the definition text?

推荐答案

将此添加到存储过程的开头:

Add this to the beginning of the Stored Procedure:

SET max_sp_recursion_depth=255;

相关文章