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