You can not run this command now

Something like this only happens, when you don’t have time, a deadline is close, the pressure is high, you have to be fast. What happend: I wrote a stored procedure – tested it via the console, everything was wonderful. But back in PHP nothing worked, a fatal error. The whole thing went dead with the message: “You can not run this command now” – but I want to, so where was the mistake?

A stored procedure is a group of SQL statements compiled into a single execution plan. A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA’s to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL Injection.

But back to my problem, since the procedure worked via console, the error had to be related to the PHP / MySQL interaction. To narrow it down. In one of the following lines something goes wrong:

$mysqli = new MySQLI('host','user','pass','db');
$result = $mysqli->query("CALL myprocedure()");

$data = $result->fetch_assoc();
mysqli_free_result($result);

$result2 = $mysqli->query("SELECT something DIFFERENT");

Either you get the error: Lost connection to MySQL server during query . Or, if you do not use a mysqi_free_result, the request fails with: Commands out of sync; you can’t run this command now.

And all this when you just want to reach the finish line. Even if I was furious in that moment, the error is dead simple, and reading the messages properly it says clearly what goes wrong. And yes, you should be aware of the fact that calling a stored procedure is a special case. Because a stored procedure has the potential for returning multiple result sets, each of which may have its own set of rows, therefore every stored procedure returns at least two results: one (or many) actual results, and on top a status message (one empty result to tell the client there are no more results).

So you have to use mysqli_multi_query() and looping until mysqli_next_result() has no more result sets.

$mysqli = new MySQLI('host','user','pass','db');
if($query = $mysqli->multi_query("CALL myprocedure()") { 
	$result = $mysqli->use_result();
	$data = $result->fetch_assoc();
	$result->free();
	while ($mysqli->next_result()) {
		$result = $mysqli->use_result();
		if ($result instanceof mysqli_result) {
			$result->free();
		}
	}
}
$result2 = $mysqli->query("SELECT something DIFFERENT");

This is necessary to satisfy the MySQL protocol, even if in my case the stored procedure had a single result. Like this you and your database are in sync again.