Packets out of order error when calling MySQL stored proc

Question

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.

Solution 1

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.

Solution 2

I ran into the same problem as soon as I tried changing the PDO::ATTR_EMULATE_PREPARES attribute.

I can reliably reproduce what you've described when I attempt to call a second prepared statement on the same PDO connection. Something like this:

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

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 cursor before preparing and calling the second. Like this:

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

Solution 3

@http203 I ran into the same issue using WAMP server with mysql 5.6.17 using this

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

fixes the issue but I also recommend increased the query limit in the 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

Solution 4

This PHP bug has been fixed in PHP 7.4.15

The bug was in mysqlnd, which was not able to properly fetch records from a stored procedure that used cursors internally when using prepared statements. This has been patched now.

For people using older versions, the workaround to switch to emulated prepares still works.

View more solutions

Comments

[Barmar]

This may be helpful: bugs.mysql.com/bug.php?id=68359

[http203]

@Barmar I had seen that, and tried various combinations but I'm not sure how to convert the mysqli posted solution to PDO...

[er.irfankhan11]

any idea in laravel?

[KD.S.T.]

where do you set this thing?

[http203]

Call the setAttribute function on your PDO object. For ex: $db = new PDO(...); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);