CODEWITHSUNDEEP
mysqlstored-procedures
James Pitt
James Pitt

Reputation: 441

MySQL Stored Procedure - Can't reopen table (not a temporary table)

I am converting a stored procedure from MS-SQL to MySQL. It is based around Directed Acyclic Graphs.

The original MS-SQL script is in Listing 2 on the following page: http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o

The stored procedure saves fine but is not removing any rows from the database. I have tried running through Debugger for MySQL and it will pass the first time and seems to update the table I am using for the temporary data, but doesn't update the "edges" table. It also doesn't drop the table at the end.

Second run through, if I remove the purgelist table manually, it will run through to the REPEAT statement and then I get Error Code: 1137 SQLState: HY000, Message: Can't reopen table: 'purgelist'. Originally I wanted to use a temporary table but I understand I cannot reference the temporary table multiple times in the way my SELECT does, however, I am surprised this error is occurring on a non-temporary table. Also, I recognise in a multi session environment I will need purgelist to be dynamically named.

Here is the MySQL code:

DELIMITER //
CREATE PROCEDURE RemoveEdge(
IN iId int(11)
)

MAIN_BLOCK: BEGIN

DECLARE counter int default 0;
DECLARE rcount int default 0;
SET counter = ( SELECT id FROM edges WHERE id = iId AND hops = 0 );
IF counter = 0 THEN
   BEGIN
      LEAVE MAIN_BLOCK;
   END;
END IF;

CREATE TABLE purgeList (id int);

-- step 1: rows that were originally inserted with the first
-- AddEdge call for this direct edge
INSERT INTO purgeList
    SELECT id
      FROM edges
      WHERE directEdgeId = iId;

-- step 2: scan and find all dependent rows that are inserted afterwards
REPEAT
    INSERT INTO purgeList
        SELECT id    
            FROM edges
            WHERE hops > 0
                AND ( entryEdgeId IN ( SELECT id FROM purgeList ) 
                    OR exitEdgeId IN ( SELECT id FROM purgeList ) )
            AND id NOT IN (SELECT id FROM purgeList );
    SET rcount = ROW_COUNT();
UNTIL rcount = 0
END REPEAT;

DELETE FROM edges
   WHERE id IN ( SELECT id FROM purgeList);
DROP TABLE purgeList;
END //
DELIMITER ;

I've double checked my syntax but I assume there is an error in there somewhere. Any help greatly appreciated.

Upvotes: 1

Views: 4999

Answers (1)

joocer
joocer

Reputation: 1121

There may be a cleaner approach but I think you need to create a temporary table inside the Repeat that is populated with 

SELECT id FROM purgeList

and then reference the temp table rather than than purgeList, something like this:

REPEAT

    CREATE TABLE innerPurgeList (id int);

    INSERT INTO innerPurgeList
       SELECT id
            FROM purgeList;

    INSERT INTO purgeList
       SELECT id    
            FROM edges
            WHERE hops > 0
                AND ( entryEdgeId IN ( innerPurgeList ) 
                    OR exitEdgeId IN ( innerPurgeList ) )
            AND id NOT IN ( innerPurgeList );
    SET rcount = ROW_COUNT();

    DROP TABLE innerPurgeList;

UNTIL rcount = 0
END REPEAT;

Upvotes: 2

Related Questions