“Command out of Sync. We can’t run this command now“, This error made my last few days extremely frustrating.
After numerous search and hit and trial, I found the exact cause of this. So I thought it is worthy to share with you all.
How MySQL Works:
First of all, I want to little describe the MySQL exceution: So when we run a query or a stored procedure, it returns a resource id, and from this we fetch data using various function like mysql_fetch array/assoc etc.
When we execute a query/stored procedure,the connection object holds that resource, and didn’t free it till we command it to do so. And which should be done.
Our Usual Belief: We use inline query/stored procedure, we usually handle a single result set. And many of us has the belief, it always should return a single result set.
But it’s not. We can use multi result set and access the result sets by mysqli::next_result() i.e. http://php.net/manual/en/function.mssql-next-result.php
Cause of Error:
The error occurs when multiple resultsets are returned and we don’t handle the result set properly i.e. don’t free the result after using it.
Best Practice:
Always after getting result ie.e storing the data in an array/variable, make the result free, by calling mysqli::free_result() i.e. http://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-result.free.html
What it does is: just frees the resource.