存入mysql的信息乱序,调用MySQL存储过程时数据包出现乱序错误

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.

Warning: Packets out of order. Expected 1 received 16. Packet size=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.

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.

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 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.

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

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.

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

解决方案

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

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.

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.