MySQL错误2014-命令不同步-尝试调用连续存储的proc时 [英] MySQL Error 2014 - Commands out of sync - when trying to call consecutive stored procs

问题描述

我有一个使用嵌套集模型的分层MySQL表设置.我创建了一个存储过程，可以删除组织.

I have a hierarchical MySQL table setup using the Nested Set model. I have created a stored proc which allows me to delete an organization.

当我尝试对存储的proc进行连续调用时，遇到第二个CALL命令的以下错误:

When I try making consecutive calls to the stored proc, I get the following error referring to the second CALL command:

2014-命令不同步；您现在不能运行此命令

2014 - Commands out of sync; you can't run this command now

这是我正在运行的SQL:

Here is the SQL I am running:

SELECT @id := id, 
       @parent_id := parent_id 
  FROM organization 
 WHERE name = 'TESTDEAL';

CALL deleteOrg(@id);
CALL deleteOrg(@parent_id);

这是我的存储过程:

DELIMITER $$

CREATE PROCEDURE deleteOrg(IN IN_ID INT)

MODIFIES SQL DATA

BEGIN 

    START TRANSACTION;

    SELECT @org_id := id, 
           @myLeft := lft, 
           @myRight := rgt,  
           @myWidth := rgt - lft + 1 
      FROM organization 
     WHERE id = IN_ID;

    # delete statements for removing roles and other dependencies

    DELETE FROM organization 
     WHERE id = @org_id;

    UPDATE organization 
       SET rgt = rgt - @myWidth 
     WHERE rgt > @myRight;

    UPDATE organization 
       SET lft = lft - @myWidth 
     WHERE lft > @myRight;

    COMMIT;

END;
$$

DELIMITER ;

第二个命令为什么会失败? MySQL是否在提交第一个CALL命令之前尝试执行第二个CALL命令?有没有办法可以在调用第二个之前强制第一个完全执行?

Why would the second command be failing? Is MySQL trying to execute the second CALL command before the first one has been committed? Is there a way I can force the first one to fully execute before calling the second?

组织表正在使用InnoDB

organization table is using InnoDB

尝试删除开始交易"；和COMMIT;但仍然出现相同的错误

Tried removing START TRANSACTION; and COMMIT; but am still getting same error

推荐答案

我猜想这可能与您分配变量的方式有关.

I'm guessing this might have something to do with the way you are assigning your variables.

我的建议是在过程中声明局部变量，并使用SELECT...INTO进行分配.一般来说，这是一个好习惯，可能会帮助您解决问题.

My advice is to declare local variables inside your procedure, and to assign them using SELECT...INTO. This is a good practice in general, and may help you with your problem.

这是您的程序的样子:

DELIMITER $$

DROP PROCEDURE IF EXISTS deleteOrg $$

CREATE PROCEDURE deleteOrg(IN IN_ID INT)

MODIFIES SQL DATA

BEGIN 
  DECLARE V_ORG_ID INT;
  DECLARE V_MY_LEFT INT;
  DECLARE V_MY_RIGHT INT;
  DECLARE V_MY_WIDTH INT;

    START TRANSACTION;

    SELECT id, 
           lft, 
           rgt, 
           rgt - lft + 1
      into V_ORG_ID, 
           V_MY_LEFT, 
           V_MY_RIGHT,  
           V_MY_WIDTH
      FROM organization 
     WHERE id = IN_ID;

    -- delete statements for removing roles and other dependencies

    DELETE FROM organization 
     WHERE id = V_ORG_ID;

    UPDATE organization 
       SET rgt = rgt - V_MY_WIDTH 
     WHERE rgt > V_MY_RIGHT;

    UPDATE organization 
       SET lft = lft - V_MY_WIDTH 
     WHERE lft > V_MY_LEFT;

COMMIT;

END;
$$

DELIMITER ;

这篇关于MySQL错误2014-命令不同步-尝试调用连续存储的proc时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！