好吧,我被这个难住了。我的数据库中有一个表,在那里我似乎无法通过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都是int。它们被提供了相同的变量,相隔几行。
我认为这可能是一个权限问题,所以我用以下内容覆盖了它:
mysql> grant all privileges on storeProcess.* to 'myusername'@'localhost' with
grant option;
Query OK, 0 rows affected (0.19 sec)
然后我认为这可能是一个奇怪的定时/过载/whothellknowswhat问题,所以我在工作流表上创建了一个触发器来完成应用程序的工作并清理其他表。然后我把我的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正在使用的帐户的usename/密码登录时,使用完全相同的命令从控制台删除记录没有问题(触发器工作正常,也会从其余表中删除数据):
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)
那么到底出了什么问题呢?
这是在我的工作桌面上运行的(WinXP,没有什么花哨的,来自大公司的标准类型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)
PDO::exec()
函数返回受影响的行数,如果没有行受到影响,则返回0。
像这样的行将返回die()
,因为exec
将返回被解释为布尔false的0
。
$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模式下会被使用,因为它会向操作系统返回成功/错误代码。它并不是用来处理错误的。