Packets out of order error when calling MySQL stored proc

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.

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.

@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