ERROR 1137: Can't reopen table when joining a temporary table

Question

I have a stored procedure:

DROP PROCEDURE IF EXISTS dijResolve; 
DELIMITER | 
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
BEGIN 
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
  CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
  CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;

  -- null out path info in the nodes table 
  UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
  -- find nodeIDs referenced by input params 
  SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
  IF vFromNodeID IS NULL THEN 
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
  ELSE 
    BEGIN 
      -- start at src node 
      SET vNodeID = vFromNodeID; 
      SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
      IF vToNodeID IS NULL THEN 
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
      ELSE 
        BEGIN 
          -- calculate path costs till all are done 
          UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
          WHILE vNodeID IS NOT NULL DO 
            BEGIN 
              UPDATE  
                new_dijnodes AS src 
                JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
                JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
              SET dest.Cost = CASE 
                                WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                                WHEN src.Cost + Paths.Cost < dest.Cost THEN src.Cost + Paths.Cost 
                                ELSE dest.Cost 
                              END, 
                  dest.PathID = Paths.PathID 
              WHERE  
                src.NodeID = vNodeID 
                AND (dest.Cost IS NULL OR src.Cost + Paths.Cost < dest.Cost) 
                AND dest.Calculated = 0; 

              UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 

              SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                              WHERE Calculated = 0 AND Cost IS NOT NULL 
                              ORDER BY Cost LIMIT 1 
                            ); 
            END; 
          END WHILE; 
        END; 
      END IF; 
    END; 
  END IF; 
  IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
    -- problem,  cannot proceed 
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
  ELSE 
    BEGIN 
      -- write itinerary to map table 
      DROP TEMPORARY TABLE IF EXISTS map; 
      CREATE TEMPORARY TABLE map ( 
        RowID INT PRIMARY KEY AUTO_INCREMENT, 
        FromNodeName VARCHAR(20), 
        ToNodeName VARCHAR(20), 
        Cost INT 
      ) ENGINE=MEMORY; 
      WHILE vFromNodeID <> vToNodeID DO 
        BEGIN 
          SELECT  
            src.NodeName,dest.NodeName,dest.Cost,dest.PathID 
            INTO vFromNodeName, vToNodeName, vCost, vPathID 
          FROM  
            new_dijnodes AS dest 
            JOIN new_dijpaths AS Paths ON Paths.PathID = dest.PathID 
            JOIN new_dijnodes AS src ON src.NodeID = Paths.FromNodeID 
          WHERE dest.NodeID = vToNodeID; 

          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 

          SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
        END; 
      END WHILE; 
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
      DROP TEMPORARY TABLE Map; 
    END; 
  END IF; 
END; 
| 
DELIMITER ;

This function is taken from this site http://www.artfulsoftware.com/infotree/qrytip.php?id=766

well I have changed it to be able to do calculations on temporary tables so it is not any need to save data in tables. But I faced a problem, Mysql does not let to call a temporary table with other names. so In above code I'll face an error which says

#1137 - Can't reopen table: 'src'

the above error comes from this query

      UPDATE  
        new_dijnodes AS src 
        JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
        JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
      SET dest.Cost = CASE 
                        WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                        WHEN src.Cost + Paths.Cost < dest.Cost THEN src.Cost + Paths.Cost 
                        ELSE dest.Cost 
                      END, 
          dest.PathID = Paths.PathID 
      WHERE  
        src.NodeID = vNodeID 
        AND (dest.Cost IS NULL OR src.Cost + Paths.Cost < dest.Cost) 
        AND dest.Calculated = 0;

here is http://sqlfiddle.com/#!9/bc5a01c data of my tables As you can see the above query joins the same table once as src and again as dest and updates their fields. I tried to create another new_dijnodes but I could not make it work,Here is my try

DROP PROCEDURE IF EXISTS dijResolve; 
DELIMITER | 
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
BEGIN 
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
  DECLARE vFromNodeName, vToNodeName VARCHAR(20); 
  DROP TEMPORARY TABLE IF EXISTS new_dijnodes; 
  DROP TEMPORARY TABLE IF EXISTS new_dijpaths; 

  CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
  CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;

  -- null out path info in the nodes table 
  UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
  -- find nodeIDs referenced by input params 
  SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
  IF vFromNodeID IS NULL THEN 
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
  ELSE 
    BEGIN 
      -- start at src node 
      SET vNodeID = vFromNodeID; 
      SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
      IF vToNodeID IS NULL THEN 
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
      ELSE 
        BEGIN 
          -- calculate path costs till all are done 
          UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
          WHILE vNodeID IS NOT NULL DO 
            BEGIN 
            DROP TEMPORARY TABLE IF EXISTS new_dijnodes_dst; 
            CREATE TEMPORARY TABLE new_dijnodes_dst AS SELECT * FROM new_dijnodes;

              UPDATE  
                new_dijnodes  
                JOIN new_dijpaths  ON new_dijpaths.FromNodeID = new_dijnodes.NodeID 
                JOIN new_dijnodes_dst ON new_dijnodes_dst.NodeID = new_dijpaths.ToNodeID 
              SET new_dijnodes_dst.Cost = CASE 
                                WHEN new_dijnodes_dst.Cost IS NULL THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                WHEN new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                ELSE new_dijnodes_dst.Cost 
                              END, 
                  new_dijnodes_dst.PathID = new_dijpaths.PathID 
              WHERE  
                new_dijnodes.NodeID = vNodeID 
                AND (new_dijnodes_dst.Cost IS NULL OR new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost) 
                AND new_dijnodes_dst.Calculated = 0; 

              UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 

              SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                              WHERE Calculated = 0 AND Cost IS NOT NULL 
                              ORDER BY Cost LIMIT 1 
                            ); 
            END; 
          END WHILE; 
        END; 
      END IF; 
    END; 
  END IF; 
  IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
    -- problem,  cannot proceed 
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
  ELSE 
    BEGIN 
      -- write itinerary to map table 
      DROP TEMPORARY TABLE IF EXISTS map; 
      CREATE TEMPORARY TABLE map ( 
        RowID INT PRIMARY KEY AUTO_INCREMENT, 
        FromNodeName VARCHAR(20), 
        ToNodeName VARCHAR(20), 
        Cost INT 
      ) ENGINE=MEMORY; 
      WHILE vFromNodeID <> vToNodeID DO 
        BEGIN 
        DROP TEMPORARY TABLE IF EXISTS new_dijnodes_src; 
        CREATE TEMPORARY TABLE new_dijnodes_src AS SELECT * FROM new_dijnodes;

          SELECT  
            new_dijnodes_src.NodeName,new_dijnodes.NodeName,new_dijnodes.Cost,new_dijnodes.PathID 
            INTO vFromNodeName, vToNodeName, vCost, vPathID 
          FROM  
            new_dijnodes 
            JOIN new_dijpaths  ON new_dijpaths.PathID = new_dijnodes.PathID 
            JOIN new_dijnodes_src  ON new_dijnodes_src.NodeID = new_dijpaths.FromNodeID 
          WHERE new_dijnodes.NodeID = vToNodeID; 

          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 

          SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
        END; 
      END WHILE; 
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
      DROP TEMPORARY TABLE Map; 
    END; 
  END IF; 
END; 
| 
DELIMITER ;

but it always returns SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); as It updates new_dijnodes_dst which is tables duplication.

I also can not make any real table as this procedure is unique for each user and its handling is not easy for multiuser. what solutions are available to fix this problem? thanks

score 4 · Answer 1 · answered Dec 06 '17 at 08:00

MySQL doc suggests

You cannot refer to a TEMPORARY table more than once in the same query

Please refer to this thread. Most practical solutions seem to be

Replace temporary table with permanent table
Replace subsequent calls of temporary table with sub-query behind temporary table
Duplicate temporary table
Find a workaround self joins

Since your particular problem is update with self join and you don't want permanent table, I'd recommend that making duplicate temporary tables is most suitable option.

I tried to create another new_dijnodes but I could not make it work

Can you please share where are you having trouble in this?

Thanks very much. I have updated my question and added table duplication method with my problem, can you please look at it? — Majid Hojati, Dec 06 '17 at 08:14

score 1 · Accepted Answer · answered Dec 06 '17 at 09:45

Adding another answer because it's too long for comment.

After update statement, I have brought both instances of temporary table on same page and it's working fine now. You can see output here

Please use stored procedure below.

DROP PROCEDURE IF EXISTS dijResolve; 
DELIMITER | 
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
BEGIN 
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
  DECLARE vFromNodeName, vToNodeName VARCHAR(20); 
  DROP TEMPORARY TABLE IF EXISTS new_dijnodes; 
  DROP TEMPORARY TABLE IF EXISTS new_dijpaths; 

  CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
  CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;

  -- null out path info in the nodes table 
  UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
  -- find nodeIDs referenced by input params 
  SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
  IF vFromNodeID IS NULL THEN 
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
  ELSE 
    BEGIN 
      -- start at src node 
      SET vNodeID = vFromNodeID; 
      SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
      IF vToNodeID IS NULL THEN 
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
      ELSE 
        BEGIN 
          -- calculate path costs till all are done 
          UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
          WHILE vNodeID IS NOT NULL DO 
            BEGIN 
            DROP TEMPORARY TABLE IF EXISTS new_dijnodes_dst; 
            CREATE TEMPORARY TABLE new_dijnodes_dst AS SELECT * FROM new_dijnodes;

              UPDATE  
                new_dijnodes  
                JOIN new_dijpaths  ON new_dijpaths.FromNodeID = new_dijnodes.NodeID 
                JOIN new_dijnodes_dst ON new_dijnodes_dst.NodeID = new_dijpaths.ToNodeID 
              SET new_dijnodes_dst.Cost = CASE 
                                WHEN new_dijnodes_dst.Cost IS NULL THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                WHEN new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                ELSE new_dijnodes_dst.Cost 
                              END, 
                  new_dijnodes_dst.PathID = new_dijpaths.PathID 
              WHERE  
                new_dijnodes.NodeID = vNodeID 
                AND (new_dijnodes_dst.Cost IS NULL OR new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost) 
                AND new_dijnodes_dst.Calculated = 0; 

            DROP TEMPORARY TABLE IF EXISTS new_dijnodes; 
            CREATE TEMPORARY TABLE new_dijnodes AS SELECT * FROM new_dijnodes_dst;

              UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 

              SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                              WHERE Calculated = 0 AND Cost IS NOT NULL 
                              ORDER BY Cost LIMIT 1 
                            ); 
            END; 
          END WHILE; 
        END; 
      END IF; 
    END; 
  END IF; 
  IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
    -- problem,  cannot proceed 
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
  ELSE 
    BEGIN 
      -- write itinerary to map table 
      DROP TEMPORARY TABLE IF EXISTS map; 
      CREATE TEMPORARY TABLE map ( 
        RowID INT PRIMARY KEY AUTO_INCREMENT, 
        FromNodeName VARCHAR(20), 
        ToNodeName VARCHAR(20), 
        Cost INT 
      ) ENGINE=MEMORY; 
      WHILE vFromNodeID <> vToNodeID DO 
        BEGIN 
        DROP TEMPORARY TABLE IF EXISTS new_dijnodes_src; 
        CREATE TEMPORARY TABLE new_dijnodes_src AS SELECT * FROM new_dijnodes;

          SELECT  
            new_dijnodes_src.NodeName,new_dijnodes.NodeName,new_dijnodes.Cost,new_dijnodes.PathID 
            INTO vFromNodeName, vToNodeName, vCost, vPathID 
          FROM  
            new_dijnodes 
            JOIN new_dijpaths  ON new_dijpaths.PathID = new_dijnodes.PathID 
            JOIN new_dijnodes_src  ON new_dijnodes_src.NodeID = new_dijpaths.FromNodeID 
          WHERE new_dijnodes.NodeID = vToNodeID; 

          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 

          SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
        END; 
      END WHILE; 
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
      DROP TEMPORARY TABLE Map; 
    END; 
  END IF; 
END; 
| 
DELIMITER ;

Wow, thanks very much for your help, But I thought temporary tables performance is much better than others but I saw it is decreased — Majid Hojati, Dec 06 '17 at 10:02
Most likely because there is a 3 temporary table join with no indexes. What is row count of your temporary tables when you're running this? Spending few CPU clocks is probably worthwhile to increase performance. You can create index with SELECT statement like this `CREATE TEMPORARY TABLE new_dijnodes_dst (INDEX index_name (column_name_1, column_name_2)) SELECT * FROM new_dijnodes;` — Hasaan Mubasher, Dec 06 '17 at 10:26
pathid and nodeid are primary keys, I think I must index them both in temporary table, right? — Majid Hojati, Dec 06 '17 at 12:12
Yes, you may wanna create 2 separate indexes, one for each of these columns. Since at one time, only time column is being used in a join. — Hasaan Mubasher, Dec 06 '17 at 12:16

ERROR 1137: Can't reopen table when joining a temporary table

2 Answers2