php - PDOException 与 DROP TABLE IF EXISTS

Question

我正在使用 PDO 调用以 DROP TABLE IF EXISTS 开头的存储过程。我随机得到 PDOException 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'historygr.reached' doesn't exist' 更烦人的是它会从告诉我这个到抛出一个异常说表已经存在，彼此在几秒钟内，似乎来自同一个连接。

我自己无法触发错误，但会收到错误通知。

这是错误产生的 PHP:

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

这是 MySQL 过程定义:

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

Answer 1

您遇到了竞争条件。

如果两个连接都在执行相同的脚本，它们将同时创建和删除相同的表，从而导致冲突。

考虑使用临时表而不是在事务中创建和删除真实表。

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.

编辑

如评论中所述，查询多次引用表。

另一种方法是在过程中使用锁:http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_get-lock