Ovilia Ovilia - 1 year ago 195
MySQL Question

MySQL stored procedure caused `Commands out of sync`

Call procedure works all right in MySQL terminal, but in PHP, caused

Commands out of sync; you can't run this command nowCommands out of sync; you can't run this command now

My procedure is

delimiter $$
create procedure getMostSimilar (IN vU_ID INT, IN voffset INT, IN vsize INT)
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;

where getSimilarity is a function.


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;
return $ans;

What should I do now? Thanks!

Answer Source

C.5.2.14. Commands out of sync If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

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



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


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download