php PDO 无缓冲查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/841463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO Unbuffered queries
提问by Jhonny D. Cano -Leftware-
I'm trying to get into PDO details. So I coded this:
我正在尝试进入 PDO 细节。所以我编码了这个：
$cn = getConnection();
// get table sequence
$comando = "call p_generate_seq('bitacora')";
$id = getValue($cn, $comando);
//$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (?, ?, ?)';
$comando = 'INSERT INTO dsa_bitacora (id, estado, fch_creacion) VALUES (:id, :estado, :fch_creacion)';
$parametros = array (
':id'=> (int)$id,
':estado'=>1,
':fch_creacion'=>date('Y-m-d H:i:s')
);
execWithParameters($cn, $comando, $parametros);
my getValue function works fine, and I get the next sequence for the table. But when I get into execWithParameters, i get this exception:
我的 getValue 函数工作正常，我得到了表的下一个序列。但是当我进入 execWithParameters 时，我得到了这个异常：
PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. in D:\Servidor\xampp_1_7_1\htdocs\bitacora\func_db.php on line 77
PDOException：SQLSTATE[HY000]：一般错误：2014 无法在其他无缓冲查询处于活动状态时执行查询。考虑使用 PDOStatement::fetchAll()。或者，如果您的代码只针对 mysql 运行，您可以通过设置 PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 属性来启用查询缓冲。在 D:\Servidor\xampp_1_7_1\htdocs\bitacora\func_db.php 第 77 行
I tried to modify the connection attributes but it doesn't work.
我试图修改连接属性但它不起作用。
These are my core db functions:
这些是我的核心数据库功能：
function getConnection() {
try {
$cn = new PDO("mysql:host=$host;dbname=$bd", $usuario, $clave, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
));
$cn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
return $cn;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
}
function getValue($cn, $comando) {
$resul = $cn->query($comando);
if (!$resul) return null;
while($res = $resul->fetch()) {
$retorno = $res[0][0];
break;
}
return $retorno;
}
function execWithParameters($cn, $comando, $parametros) {
$q = $cn->prepare($comando);
$q->execute($parametros);
if ($q->errorInfo() != null) {
$e = $q->errorInfo();
echo $e[0].':'.$e[1].':'.$e[2];
}
}
Somebody who can shed a light for this? PD. Please do not suggest doing autonumeric id, cause i am porting from another system.
有人可以为这件事发光吗？PD。请不要建议做自动数字 ID，因为我是从另一个系统移植的。
回答by Wez Furlong
The issue is that mysql only allows for one outstanding cursor at a given time. By using the fetch() method and not consuming all the pending data, you are leaving a cursor open.
问题是 mysql 在给定时间只允许一个未完成的游标。通过使用 fetch() 方法而不消耗所有挂起的数据，您将保持游标处于打开状态。
The recommended approach is to consume all the data using the fetchAll() method. An alternative is to use the closeCursor() method.
推荐的方法是使用 fetchAll() 方法消耗所有数据。另一种方法是使用 closeCursor() 方法。
If you change this function, I think you will be happier:
如果你改变这个功能，我想你会更开心：
<?php
function getValue($cn, $comando) {
$resul = $cn->query($comando);
if (!$resul) return null;
foreach ($resul->fetchAll() as $res) {
$retorno = $res[0];
break;
}
return $retorno;
}
?>
回答by Jonathon Hill
I don't think PDOStatement::closeCursor() would work if you're not doing a query that returns data (i.e. an UPDATE, INSERT, etc).
如果您不执行返回数据的查询（即更新、插入等），我认为 PDOStatement::closeCursor() 不会起作用。
A better solution is to simply unset() your PDOStatement object after calling PDOStatement::execute():
更好的解决方案是在调用 PDOStatement::execute() 后简单地 unset() 你的 PDOStatement 对象：
$stmt = $pdo->prepare('UPDATE users SET active = 1');
$stmt->execute();
unset($stmt);
回答by Tordek
The problem seems to be---I'm not too familiar with PDO--- that after your getValue call returns, the query is still bound to the connection (You only ever ask for the first value, yet the connection returns several, or expects to do so).
问题似乎是---我对 PDO 不太熟悉---在你的 getValue 调用返回后，查询仍然绑定到连接（你只要求第一个值，但连接返回几个，或希望这样做）。
Perhaps getValue can be fixed by adding
也许 getValue 可以通过添加来修复
$resul->closeCursor();
before the return.
回国前。
Otherwise, if queries to getValue will always return a single (or few enough) value, it seems that using fetchAll will be preferred.
否则，如果对 getValue 的查询将始终返回单个（或足够少的）值，则似乎首选使用 fetchAll。
回答by Henrik
I just spend 15 minutes googling all around the internet, and viewed at least 5 different Stackoverflow questions, some who claimed my bug apparently arose from the wrong version of PHP, wrong version of MySQL library or any other magical black-box stuff...
我只花了 15 分钟在互联网上搜索，并查看了至少 5 个不同的 Stackoverflow 问题，有些人声称我的错误显然是由错误版本的 PHP、错误版本的 MySQL 库或任何其他神奇的黑盒内容引起的......
I changed all my code into using "fetchAll" and I even called closeCursor() and unset() on the query object after each and every query. I was honestly getting desperate! I also tried the MYSQL_ATTR_USE_BUFFERED_QUERY flag, but it did not work.
我将所有代码更改为使用“fetchAll”，甚至在每次查询后在查询对象上调用 closeCursor() 和 unset() 。老实说，我快绝望了！我还尝试了 MYSQL_ATTR_USE_BUFFERED_QUERY 标志，但没有奏效。
FINALLYI threw everything out the window and looked at the PHP error, and tracked the line of code where it happened.
最后，我把所有东西都扔出窗外，查看 PHP 错误，并跟踪发生错误的代码行。
SELECT AVG((original_bytes-new_bytes)/original_bytes) as saving
FROM (SELECT original_bytes, new_bytes FROM jobs ORDER BY id DESC LIMIT 100) AS t1
Anyway, the problem happened because my original_bytesand new_bytesboth where unsigned bigints, and that meant that if I ever had a job where the new_bytes where actually LARGER than the original_bytes, then I would have a nasty MySQL "out of range" error. And that just happened randomly after running my minification service for a little while.
无论如何，问题的发生是因为我的original_bytes和new_bytes都是 unsigned bigints，这意味着如果我有一份工作，其中 new_bytes 实际上比 original_bytes 大，那么我会遇到令人讨厌的 MySQL“超出范围”错误。这只是在运行我的缩小服务一段时间后随机发生的。
Why the hell I got this weird MySQL error instead of just giving me the plain error, is beyond me! It actually showed up in SQLBuddy (lightweight PHPMyAdmin) when I ran the raw query. I had PDO exceptions on, so it should have just given me the MySQL error.
为什么我得到这个奇怪的 MySQL 错误而不是仅仅给我一个简单的错误，这超出了我的能力！当我运行原始查询时，它实际上出现在 SQLBuddy（轻量级 PHPMyAdmin）中。我有 PDO 异常，所以它应该给我 MySQL 错误。
Never mind, the bottom line is:
没关系，底线是：
If you ever get this error, be sure to check that your raw MySQL is actually correct and STILL working!!!
如果您遇到此错误，请务必检查您的原始 MySQL 是否确实正确并且仍在工作！！！
回答by VolkerK
A friend of mine had very much the same problem with the xampp 1.7.1 build. After replacing xampp/php/* by the 5.2.9-2 php.net build and copying all necessary files to xampp/apache/bin it worked fine.
我的一个朋友在 xampp 1.7.1 版本中遇到了非常相同的问题。用 5.2.9-2 php.net 版本替换 xampp/php/* 并将所有必要的文件复制到 xampp/apache/bin 后，它工作正常。
回答by Khashayar
If you're using XAMPP 1.7.1, you just need to upgrade to 1.7.2.
如果您使用的是 XAMPP 1.7.1，则只需升级到 1.7.2。
