CODEWITHSUNDEEP
phpmysql
Ovilia
Ovilia

Reputation: 7256

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

Upvotes: 10

Views: 17825

Answers (4)

Yuri Waki
Yuri Waki

Reputation: 745

I was getting the same message for a whole different reason, so I will leave my solution here:

It was just a typo in a parameter name. My function had a parameter named preferable:

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

In the function body, I was using the parameter preferable with the wrong name:

if prefered  = 1 then
  ...
end if;

Once I changed prefered for preferable it started working again.

Upvotes: -1

basos
basos

Reputation: 576

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 (probably) returns 2 result sets. The first is 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 processing the legit one (the first).

Example PHP code

function do_query($con, $sql)
{
    $result = mysqli_query($con, $sql);
    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";
    }
}

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;

Upvotes: 16

amelvin
amelvin

Reputation: 9051

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.

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

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.

Upvotes: 1

Rahan
Rahan

Reputation: 19

This "bug" was happening to me with extremely simple procedure even inside phpmyadmin. The reason was that I was declaring general variables (without the @ prefix). I changed my variables to user-defined variables prefixed with @ and it was solved.

Upvotes: 0

Related Questions