php - PDO 拒绝删除一行数据(具体表中)

Question

好吧，我被这个难住了。我的数据库中有一个表，我似乎无法通过 PDO 删除行(我已经注意到这种行为几周了，在那之前它工作得很好)。

我的 PHP 代码是这样的:

    // Echo's have been added for testing.
    try{
            $dbh = new PDO($hostname, $username, $password);

            $sql="delete from sources where workFlowID=".$ID.";";
            $numRows=$dbh->exec($sql);
            echo "There were $numRows deleted with: $sql <br><br>";

            $sql="delete from workflow where id=".$ID." limit 1;";
            // I have only put the 'or die' section in this today to try to see where
            // it was having problems. It carries through happily as the output 
            // below shows.
            $numRows=$dbh->exec($sql) or die(print_r($dbh->errorInfo(), true));
            // This is the problem delete...
            echo "There were $numRows deleted with: $sql <br><br>";

            $dbh=null;

    }
    catch(PDOException $e){
            echo 'Error : '.$e->getMessage();
            exit();
    }

输出为:

    There were 1 deleted with: delete from sources where workflowid=1;

    There were 1 deleted with: delete from workflow where id=1 limit 1;

但是，当我查看数据库时，我仍然看到我的记录位于我的 workflow 表中。

我已经检查并仔细检查了数据类型。 ID 和 workflowID 都是整数。它们被提供了相隔几行的相同变量。

我认为这可能是权限问题，所以我用以下内容概括了这一点:

    mysql> grant all privileges on storeProcess.* to 'myusername'@'localhost' with
     grant option;
    Query OK, 0 rows affected (0.19 sec)

然后我认为这可能是一些奇怪的计时/过载/whothehellknowswhat 问题，所以我在工作流表上创建了一个触发器来完成应用程序的工作并清理其他表。然后我将我的 PHP 代码更改为:

    // Echo's have been added for testing.
    try{
            $dbh = new PDO($hostname, $username, $password);

            $sql="delete from workflow where id=".$ID." limit 1;";
            $numRows=$dbh->exec($sql) or die(print_r($dbh->errorInfo(), true));
            echo "There were $numRows deleted with: $sql <br><br>";

            $dbh=null;

    }
    catch(PDOException $e){
            echo 'Error : '.$e->getMessage();
            exit();
    }

现在的输出是:

    There were 1 deleted with: delete from workflow where id=1 limit 1;

但同样，记录仍然存在。

    mysql> select count(*) from workflow where id=1;
    +----------+
    | count(*) |
    +----------+
    |        1 |
    +----------+
    1 row in set (0.00 sec)

当我使用 PDO 正在使用的帐户的用户名/密码登录时，使用完全相同的命令从控制台删除记录当然没有问题(触发器工作正常并从剩余表中删除数据好吧):

    mysql> delete from workflow where ID=1;
    Query OK, 1 row affected (0.00 sec)

    mysql> select count(*) from workflow where id=1;
    +----------+
    | count(*) |
    +----------+
    |        0 |
    +----------+
    1 row in set (0.00 sec)

那么到底出了什么问题呢？

这是在我的工作桌面上运行的(Win XP，没什么特别的，大公司的标准类型 SOE)。

我在这些查询期间没有使用任何事务。此外，只有少数用户使用该应用程序，并且它在任何过程中都不会占用高 CPU。

我会把代码和模式带回家在 linux 下进行测试，回来后会发布结果。

更新:我刚把它移到我家里的 linux 系统上。完全没有变化。

编辑:

我已经按照建议更改了我的代码:

try{
    $dbh = new PDO($hostname, $username, $password);
    $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql="delete from sources where workflowid=".$ID.";";

    //echo $sql."<br><br>";

    $numRows=$dbh->exec($sql);
    echo "There were $numRows deleted with:<b> $sql </b><br><br>";

    $sql="delete from workflow where id=".$ID." limit 1;";
    $numRows=$dbh->exec($sql);
    echo "There were $numRows deleted with:<b> $sql </b><br><br>";

    $sql="delete from workflow where id=".$ID." limit 1;";
    $numRows=$dbh->exec($sql);
    echo "There were $numRows deleted with:<b> $sql </b><br><br>";

    $dbh=null;

}
catch(PDOException $e){
    echo 'Error : '.$e->getMessage();
    //exit();
}

我用下面的输出运行它:

There were 601 deleted with: delete from sources where workflowid=77;

There were 1 deleted with: delete from workflow where id=77 limit 1;

There were 0 deleted with: delete from workflow where id=77 limit 1; 

该行仍未删除:

mysql> select count(*) from workflow where id=77;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

Answer 1

PDO::exec() 函数返回受影响的行数，如果没有行受到影响，则返回 0。

像这样的一行将 die() 因为 exec 将返回 0，它被解释为 bool 值 false。

$dblink->exec("UPDATE `sometable` SET `somecolumn`=0 WHERE `somecolumn`=0") or die("Never use die for error handling.");

PDO 的最佳错误处理实践是使用 PDO 异常。像这样启用 PDO 异常(属于 PDOException 类，请参阅文档):

//enable Exception mode (uncaught exceptions work just like die() with the benefit of giving you details in logs of where execution was stopped and for what reason)
$pdoDBHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

删除 or die() 和 exit(); 并启用异常模式。我敢打赌这会解决你的“怪异”问题。另请查看在 PHP 中抛出异常，即使使用过程代码(替换 die() 和 exit()。

BTW exit 停止执行就像 die 一样，除了它在 CLI 模式下很有用，因为它向操作系统返回成功/错误代码。它真的不是用来处理错误的。