php 调用 MySQL 存储过程时出现数据包乱序错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19389809/
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
Packets out of order error when calling MySQL stored proc
提问by http203
I'm trying to call a stored proc using PDO but am getting the following error when trying to do a fetch on the results.
我正在尝试使用 PDO 调用存储过程，但在尝试获取结果时出现以下错误。
Warning: Packets out of order. Expected 1 received 16. Packet size=163
警告：数据包乱序。预期 1 收到 16. 数据包大小=163
My stored proc is using two cursors that I close before selecting from the temporary table. I'm suspecting this might be the problem because I can call my SP directly in MySQL and can see results. I also never had a problem with this SP when using the php_mysql extension before migrating to php_pdo_mysql.dll. I'm also able to call my other simpler stored procs containing INPUT params in PHP using PDO and can fetch the results without any errors.
我的存储过程使用两个游标，我在从临时表中选择之前关闭了这两个游标。我怀疑这可能是问题所在，因为我可以直接在 MySQL 中调用我的 SP 并可以看到结果。在迁移到 php_pdo_mysql.dll 之前使用 php_mysql 扩展时，我也从来没有遇到过这个 SP 的问题。我还可以使用 PDO 在 PHP 中调用包含 INPUT 参数的其他更简单的存储过程，并且可以毫无错误地获取结果。
Here is the code that returns the error:
这是返回错误的代码：
$db = new PDO('mysql:host='.__DB_HOST__.';dbname='.__DB_NAME__.';charset=utf8', __DB_USER__, __DB_PASS__);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
/* DOES NOT WORK */
$queryResult = $db->prepare("CALL GetResults(:siteId,null)");
$siteId = 19;
$queryResult->bindValue(':siteId', $siteId, PDO::PARAM_INT);
$queryResult->execute();
$result = $queryResult->fetchAll(PDO::FETCH_ASSOC); // returns packets out of order warning
print_r($result);
I have this code in a Try/Catch block and no exception is being thrown. In fact, PHP is showing this as a Warning in the browser.
我在 Try/Catch 块中有此代码，并且没有抛出异常。事实上，PHP 在浏览器中将此显示为警告。
My Stored Procedure signature looks like this:
我的存储过程签名如下所示：
CREATE DEFINER=`root`@`localhost`
PROCEDURE `GetResults`(IN siteIdParam INT(11), IN siteSearchText VARCHAR(45))
I'm also not sure if the problem is with passing nullas one of the params. Sometimes the first parameter passes null, sometimes it's the 2nd. But regardless it always works directly on the MySQL server.
我也不确定问题是否在于将null作为参数之一传递。有时第一个参数传递null，有时它是第二个。但无论如何它总是直接在 MySQL 服务器上工作。
I tried bindParam and bindValue, same results. I can also post my SP but it might be overkill.
我尝试了 bindParam 和 bindValue，结果相同。我也可以发布我的 SP，但这可能有点矫枉过正。
Is there any way to turn on additional logging from the PDO extension?
有什么办法可以从 PDO 扩展打开额外的日志记录吗？
Any ideas or suggestions? If you need more information, please let me know.
有什么想法或建议吗？如果您需要更多信息，请告诉我。
NOTE: I'm using PHP v5.5.4 and MySQL v5.6.14.
注意：我使用的是 PHP v5.5.4 和 MySQL v5.6.14。
回答by http203
After spending many hours trying to isolate parts of my code to solve this problem, I noticed that the error went away after setting the ATTR_EMULATE_PREPARES flag to true.
在花了很多时间尝试隔离部分代码以解决此问题后，我注意到在将 ATTR_EMULATE_PREPARES 标志设置为 true 后错误消失了。
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
This tells PDO to emulate the prepared statements instead of natively by MySQL. From what I've been reading, it is generally recommended to turn this flag off (it's true by default) if you're using the most up to date version of MySQL and PHP. You can find more information on that in this SO article.
这告诉 PDO 模拟准备好的语句，而不是由 MySQL 本地模拟。根据我的阅读，如果您使用的是最新版本的 MySQL 和 PHP，通常建议关闭此标志（默认情况下为 true）。您可以在这篇SO 文章中找到更多相关信息。
I do believe this to be a bug with MySQL (I had the problem up to version 5.6.17). There isn't much discussion on this particular problem so hopefully this saves someone else hours of troubleshooting. The problem is also discussed on this MySQL bug page, but the posted solution didn't help me in my situation.
我确实相信这是 MySQL 的一个错误（我在 5.6.17 版本之前遇到了问题）。关于这个特定问题的讨论不多，所以希望这可以为其他人节省数小时的故障排除时间。这个问题也在这个 MySQL 错误页面上讨论过，但发布的解决方案在我的情况下没有帮助我。
回答by Matt
I ran into the same problem as soon as I tried changing the PDO::ATTR_EMULATE_PREPARES attribute.
我一尝试更改 PDO::ATTR_EMULATE_PREPARES 属性就遇到了同样的问题。
I can reliably reproduce what you've described when I attempt to call a secondprepared statement on the same PDO connection. Something like this:
当我尝试在同一个 PDO 连接上调用第二个准备好的语句时，我可以可靠地重现您所描述的内容。像这样的东西：
$db = new PDO('mysql:host='.__DB_HOST__.';dbname='.__DB_NAME__.';charset=utf8', __DB_USER__, __DB_PASS__);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
/* DOES NOT WORK */
$queryResult = $db->prepare("CALL GetResults(:siteId,null)");
$siteId = 19;
$queryResult->bindValue(':siteId', $siteId, PDO::PARAM_INT);
$queryResult->execute();
$result = $queryResult->fetchAll(PDO::FETCH_ASSOC); // works fine
print_r($result);
// Call a second stored procedure
$secondCall = $db->prepare("CALL GetOtherResults()");
$secondCall->execute();
$secondResult = $secondCall->fetchAll(PDO::FETCH_ASSOC); // returns packets out of order
print_r($secondResult);
I know your example only shows one call, but to trigger this error, the stored proc calls don't have to be in the same file, just sharing the same PDO connection. I hope my advice below still helps.
我知道你的例子只显示了一个调用，但是要触发这个错误，存储的 proc 调用不必在同一个文件中，只需共享同一个 PDO 连接。我希望我下面的建议仍然有帮助。
When you execute a stored procedure, the MySQL driver returns two rowsets. The native driver requires you to process both, before you reuse your connection. You need to advance to the second rowset, even if you don't care what's in it and also close the cursorbefore preparing and calling the second. Like this:
当您执行存储过程时，MySQL 驱动程序返回两个行集。本机驱动程序要求您在重用连接之前处理两者。您需要前进到第二个 rowset，即使您不关心其中的内容并且在准备和调用第二个之前关闭游标。像这样：
...
$queryResult->execute();
$result = $queryResult->fetchAll(PDO::FETCH_ASSOC);
$queryResult->nextRowSet();
$queryResult->closeCursor();
// Now you can prepare your second statement
...
It looks like PDO's emulated prepares are more forgiving if you forget to make these two additional calls.
如果您忘记进行这两个额外的调用，看起来 PDO 的模拟准备会更宽容。
回答by revobtz
@http203 I ran into the same issue using WAMP server with mysql 5.6.17 using this
@http203 我在使用 WAMP 服务器和 mysql 5.6.17 时遇到了同样的问题
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
fixes the issue but I also recommend increased the query limit in the mysql.ini
解决了这个问题，但我也建议增加 mysql.ini 中的查询限制
max_allowed_packet = 1MB //Default
to
到
max_allowed_packet = 3MB
to prevent future issues, for anyone with a relative problem read this reference article Warning: Packets out of order, MySQL server has gone away
为防止将来出现问题，对于有相关问题的任何人，请阅读此参考文章警告：数据包乱序，MySQL 服务器已消失
回答by DanMan
For me, setting PDO::ATTR_PERSISTENTto falsefixed the issue.
对我来说，设置PDO::ATTR_PERSISTENT来false解决这个问题。
Problem started to occur after mysql_upgrade-ing MariaDB. Not sure what the actual problem is. :-/
mysql_upgrade-ing MariaDB后问题开始出现。不确定实际问题是什么。:-/
回答by Karthik N G
I have cursor in my stored procedure. I solved by fetching all the records at once and then iterating through. I didn't have this 'notice' anymore
我的存储过程中有游标。我通过一次获取所有记录然后迭代来解决。我没有这个“通知”了
$sqlQuery = $conn->prepare('CALL myStoredProcedure(?,?)');
$sqlQuery->bind_param('ss', $param1, $param2);
$sqlQuery->execute();
$resultSet = $sqlQuery->get_result();
$result = $resultSet->fetch_all();
$len = sizeof($result);
for($i=0; $i < $len; $i++){
// Fetching data and processing it
}
