调用MySQL存储过程时数据包出现乱序错误 [英] Packets out of order error when calling MySQL stored proc

问题描述

我正在尝试使用PDO调用存储的proc，但是在尝试对结果进行提取时遇到以下错误.

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.

警告:数据包乱序.预期有1个收到.16.数据包大小= 163

我存储的proc使用的是我从临时表中选择之前关闭的两个游标.我怀疑这可能是问题所在，因为我可以直接在MySQL中调用SP并查看结果.在迁移到php_pdo_mysql.dll之前使用php_mysql扩展名时，我也从未遇到过此SP的问题. 我还可以使用PDO在PHP中调用其他更简单的包含INPUT参数的存储过程，并且可以无错误地获取结果.

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.

以下是返回错误的代码:

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);

我在Try/Catch块中有此代码，并且没有引发异常.实际上，PHP在浏览器中将其显示为警告.

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.

我的存储过程签名如下:

My Stored Procedure signature looks like this:

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `GetResults`(IN siteIdParam INT(11), IN siteSearchText VARCHAR(45))

我也不确定问题是否在于将 null 作为参数之一.有时第一个参数传递 null ，有时它是第二个.但是不管它总是直接在MySQL服务器上工作.

I'm also not sure if the problem is with passing null as 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.

我尝试了bindParam和bindValue，结果相同.我也可以发布我的SP，但这可能会导致矫kill过正.

I tried bindParam and bindValue, same results. I can also post my SP but it might be overkill.

是否可以通过PDO扩展程序打开其他日志记录?

Is there any way to turn on additional logging from the PDO extension?

有什么想法或建议吗?如果您需要更多信息，请告诉我.

Any ideas or suggestions? If you need more information, please let me know.

注意:我正在使用PHP v5.5.4和MySQL v5.6.14.

NOTE: I'm using PHP v5.5.4 and MySQL v5.6.14.

推荐答案

花了很多时间试图隔离我的代码的一部分以解决此问题之后，我注意到将ATTR_EMULATE_PREPARES标志设置为true后，错误消失了. >

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.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

这告诉PDO模仿准备好的语句，而不是MySQL本身.根据我的阅读，如果您使用的是MySQL和PHP的最新版本，通常建议关闭此标志(默认情况下为true).您可以在此 SO文章中找到有关此信息的更多信息.

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.

我确实认为这是MySQL的错误(我在5.6.17版之前有问题).关于此特定问题的讨论不多，因此希望这可以节省其他人的故障排除时间.该问题也在此MySQL错误页面上进行了讨论，但是发布的解决方案没有不能帮助我解决我的情况.

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存储过程时数据包出现乱序错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！