php - MySQL stored procedure caused `Commands out of sync`

Question

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)
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

There seems to be a nasty bug (or feature) that is manifested when calling a stored procedure that returns a result set.. I.e. 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 being the one returned from the stored procedure and the second a dummy, empty result set. It is like a multiple query command was issued. One solution to this (that does not break on usual (e.g. SELECT) queries), is to consume this dummy result set after proccessing the legit one (the first).

Example php code

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 
";
  }
}

Example 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;