MySQL stored procedure called "commands out of sync"

Question

The calling procedure works correctly in the MySQL terminal, but in Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now

My procedure

 delimiter $$ create procedure getMostSimilar (IN vU_ID INT, IN voffset INT, IN vsize INT) BEGIN set @offset = voffset; set @size = vsize; set @uid = vU_ID; prepare SimilarStmt from "SELECT U_ID, getSimilarity(U_ID, ?) AS similar FROM Answer WHERE U_ID != ? GROUP BY U_ID ORDER BY similar DESC LIMIT ?, ?"; execute SimilarStmt using @uid, @uid, @offset, @size; deallocate prepare SimilarStmt; END $$ 

where getSimilarity is a function.

In PHP:

 function getMostSimilar($U_ID, $offset, $size){ $query = sprintf("CALL getMostSimilar(%s, %s, %s)", $U_ID, $offset, $size); $result = mysql_query($query); print mysql_error(); if (!$result){ return $query; } $ans = array(); $len = 0; while($row = mysql_fetch_assoc($result)){ $ans[$len] = $row; $len++; } return $ans; } 

What should I do now? Thanks!

Answer 1

C.5.2.14. Commands are not synchronized If you receive commands are not synchronized; you cannot run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you use mysql_use_result () and try to execute a new query before you call mysql_free_result (). It can also, if you try to execute two queries that return data without calling mysql_use_result () or mysql_store_result () between them.

http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html

EDIT

I think you need to rewrite the getMostSimilar stored procedure, instead of using prepare and execute (which I think is a mysql trick) if you use parameters in a type procedure in this example I think your error will be fixed.

NTN

Answer 2

It seems that there is an unpleasant error (or function) that occurs when a stored procedure is called that returns a result set. . That is, a stored procedure that ends with a select statement without an INTO clause (see example below).

The mysqli driver (propably) returns 2 result sets. The first is the one that is returned from the stored procedure, and the second is the empty, empty result set. This is like a command with multiple queries. One solution to this (which is not interrupted by regular (e.g. SELECT) queries) is to consume this dummy result set after passing the legal (first) one.

Php code example

 function do_query($con, $sql) { if ( !($result = mysqli_query($con, $sql)) ) throw new QueryException(mysqli_error($con)); if ($result === true) return true; while ($row = mysqli_fetch_assoc( $result )) { // process rows } // Hack for procedures returning second dummy result set while(mysqli_more_results($con)) { mysqli_next_result($con); // echo "* DUMMY RS \n"; } } 

An example of a stored procedure:

 CREATE PROCEDURE selectStaleHeaders() NOT DETERMINISTIC SELECT TT.* FROM one_pretty_table AS TT LEFT JOIN another AS AN on TT.fk_id = AN.id WHERE TT.id IS NULL; 

Answer 3

I know that it’s too late, and there is already an answer, but I received the same message for a completely different reason, so I will leave my solution here:

it was actually a very stupid mistake. This is just a typo in the parameter name. My function had a parameter called preferable :

 create function call_client (pereferable int, client_id int) returns int 

in the function body, I used the preferable parameter with the wrong name:

 if prefered = 1 then ... end if; 

as soon as I changed prefered to preferable , it started working again.

Answer 4

This “error” occurred to me with an extremely simple procedure even inside phpmyadmin. The reason is because I declared common variables (without the @ prefix). I changed my variables to user variables with the @ prefix, and this was resolved.