PDOException with DROP TABLE IF EXISTS

Question

I'm using PDO to call a stored procedure that starts with a DROP TABLE IF EXISTS. I'm randomly getting PDOException 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'historygr.reached' doesn't exist' and even more annoying it'll go from telling me that to throwing an exception saying that the table already exists, within a matter of seconds of each other, seemingly from the same connection.

I can't trigger the error myself, but I get error notifications of it.

Here's the PHP that the errors originate in:

$dbh = PDODB::getInstance();
$stmt = $dbh->query("CALL ListReached(".$this->item_id.")"); // <-- ERROR
$items = $stmt->fetchAll();

And here's the MySQL Procedure definition:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ListReached`( IN root INT)
BEGIN
    DECLARE rows SMALLINT DEFAULT 0;

    DROP TABLE IF EXISTS reached;
    CREATE TABLE reached(
    node_id INT PRIMARY KEY
    ) ENGINE=HEAP;

    INSERT INTO reached VALUES (root);
    SET rows = ROW_COUNT();

    WHILE rows > 0 DO
    INSERT IGNORE INTO reached
        SELECT DISTINCT child_id 
        FROM related_item AS r
        INNER JOIN reached AS p ON r.parent_id = p.node_id;
    SET rows = ROW_COUNT();

    INSERT IGNORE INTO reached
        SELECT DISTINCT parent_id
        FROM related_item AS r
        INNER JOIN reached AS p ON r.child_id = p.node_id;
    SET rows = rows + ROW_COUNT();
    END WHILE;

    DELETE FROM reached WHERE node_id = root;

    SELECT * FROM reached;
    DROP TABLE reached;

END

Martin Samson Martin Samson · Accepted Answer · 2013-01-02T16:28:30

You are hitting a race condition.

If two connections are both executing the same script, they will both create and delete the same tables, resulting in conflicts.

Consider using temporary tables instead of creating and deleting real ones within transactions.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

EDIT

As mentionned in comments, the queries are referencing several time the table.

Another approach is to use locks inside the procedure: http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock

PDOException with DROP TABLE IF EXISTS

1 Answers