mysql执行proc没结果,MySQL错误2014-命令不同步-尝试调用连续存储的proc时

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.

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

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

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

And here is my Stored Procedure:

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 ;

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?

EDIT: organization table is using InnoDB

EDIT: 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.

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.

Here's what your procedure would look like:

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 ;