PDO拒绝删除一行数据(在特定表中) [英] PDO refuses to delete a row of data (in specific table)
问题描述
好吧，我对此感到困惑.我的数据库中有一个表，似乎无法通过PDO删除行(我已经注意到这种行为已有几周了，在此之前它运行良好).
Okay, I am stumped with this one. I have a table in my database where I cannot seem to delete rows via PDO (I have noticed this behaviour for a few weeks now, it was working perfectly before that).
我的PHP代码是这样:
My PHP Code is this:
// 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表中.
However, when I look in the database, I still see my record sitting in my workflow table.
我已经检查并再次检查了数据类型. ID和工作流ID都是整数.为它们提供相距几行的相同变量.
I have checked and double checked the data types. ID and workflowID are both ints. They are being supplied with the same variable a few lines apart.
我认为这可能是权限问题，因此我用以下内容全面介绍了该问题:
I thought it might be a permissions issue, so I have blanket covered that with the following:
mysql> grant all privileges on storeProcess.* to 'myusername'@'localhost' with
grant option;
Query OK, 0 rows affected (0.19 sec)
然后，我认为这可能是一些时髦的计时/过载/问题，因此我在工作流表上创建了一个触发器来完成应用程序的工作并清理其他表.然后，我将PHP代码更改为:
I then thought it might be some funky timing/overload/whothehellknowswhat issue, so I created a trigger on the workflow table to do the work of the application and clean up other tables. I then changed my PHP code to this:
// 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使用的帐户的用户名/密码登录时，使用完全相同的命令从控制台删除记录当然没有问题(触发器可以正常工作，并且可以从其余表中删除数据，因为很好):
I of course have no problems deleting the record from the console with the exact same command when I am logged in using the usename/password of the account that PDO is using (the trigger works fine and removes data from the remaining tables as well):
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).
This is running on my work desktop (Win XP, nothing fancy, standard type SOE from large corporation).
在这些查询期间，我没有使用任何交易.此外，只有少数用户在使用该应用程序，并且在任何情况下都不会达到很高的CPU使用率.
I am not using any transactions during these queries. Additionally there are only a few users using the app and it doesn't hit high CPU during anything.
我将把代码和架构带回家以在linux下进行测试，并在返回时发布结果.
I will be taking the code and schema home to test it under linux and will post the results when I get back.
更新:我刚刚将其移到了这里的我的linux系统中.完全没有变化.
Update: I have just moved it to my linux system here at home. No change at all.
我已根据建议更改了代码:
I have changed my code as suggested to this:
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();
}
我用以下输出来运行它:
I ran it with the following output:
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.
The PDO::exec() function returns the number of affected rows, including 0 if no rows are affected.
这样的行将是die()，因为exec将返回0，这被解释为布尔假.
A line like this will die() because exec will return 0 which is interpreted as boolean false.
$dblink->exec("UPDATE `sometable` SET `somecolumn`=0 WHERE `somecolumn`=0") or die("Never use die for error handling.");
PDO的最佳错误处理实践是使用PDO异常.启用如下所示的PDO异常(属于PDOException类，请参见docs):
The best error handling practice for PDO is to use PDO exceptions. Enable PDO exceptions (of PDOException class, see docs) like this:
//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();并启用例外模式.我敢打赌，这将解决您的怪异"问题.还要看看即使使用过程代码(替换die()和exit().
Remove or die() and exit(); and enable exception mode. I bet this will fix your "weird" problem. Also take a look at throwing Exceptions in PHP, even with procedural code (to replace die() and exit().
BTW exit与die一样停止执行，除了在CLI模式下很有用，因为它会向操作系统返回成功/错误代码.确实不是要进行错误处理.
BTW exit stops execution just like die, except it is usefull in CLI mode because it returns a success/error code to the operating system. It really isn't meant for error handling.
这篇关于PDO拒绝删除一行数据(在特定表中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
