Karooka Karooka - 1 year ago 47
PHP Question

Why Commands out of sync error when the result set was freed?

I saw an example snippet on php.net relating to


Why does it give
Error: Commands out of sync; You can't run this command now
, when
method was called on
? What exactly is happening here?

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if ($mysqli->connect_errno) {
printf("Connect failed: %s\n", $mysqli->connect_error);

/* Create table doesn't return a resultset */
if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {
printf("Table myCity successfully created.\n");

/* Select queries return a resultset */
if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
printf("Select returned %d rows.\n", $result->num_rows);

/* free result set */

/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */
if ($result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)) {

/* Note, that we can't execute any functions which interact with the
server until result set was closed. All calls will return an
'out of sync' error */
if (!$mysqli->query("SET @a:='this will not work'")) {
printf("Error: %s\n", $mysqli->error);


It gives an output:

Table myCity successfully created.
Select returned 10 rows.
Error: Commands out of sync; You can't run this command now

Answer Source

Your error is caused because after this line...

 $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)

The very next SQL command is...

$mysqli->query("SET @a:='this will not work'")

This command is called even though the results from the previous line have not been freed. Between those two lines, insert the following to fix your error:


This limitation applies to both stored procedures and regular queries. Most people never run into this problem because by default the query result is copied over to PHP and freed automatically from the server; 99% of the time, people use the default. However, sometimes you want to use the result directly from the server without making a copy (as indicated by the MYSQLI_USE_RESULT constant). In this mode, the server cannot make further queries and this error surfaces if you try.


By the way if your result set is not really large, you can instruct PHP to make a copy of all results and free up the DB automatically by running your query without the MYSQLI_USE_RESULT constant:

$mysqli->query("SELECT * FROM City")
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download