PHP执行MYSQL存储过程报错:Commands out of sync; you can't run this command now

 

CREATE PROCEDURE test1()

begin

        drop table if exists tb1;

    create table tb1

    (

        val int not null

    )engine = innoDB;

    insert into tb1(val) values(1),(2),(3);

    select * from tb1;

end

 

 

$mysqli = new mysqli("localhost", "root", "sbqcel", "test");

if (mysqli_connect_errno()) 
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$result = null;
$mysqli->autocommit(FALSE);
if(!($result = $mysqli->query( "call test1();")))
{
    echo mysqli_error($link);
    $mysqli->rollback();
}
$mysqli->commit();

print 'Result1:';

while ($row = $result->fetch_row()) 
{
        printf ("%s <br />", $row[0]);
}
$result->close();
mysqli_free_result($result);

echo 'result2:<br />';
if ($result2 = $mysqli->query("select val from tb1;")) 
{
    while ($row = $result2->fetch_row()) 
    {
        printf ("%s <br />", $row[0]);
    }
    $result2->close();
}
else
{
    echo $mysqli->error;
}
mysqli_free_result($result2);

mysqli_close($link);

 执行上面的代码后就会出现上面的错误，消息说明MYSQL数据库认为是这一个错误的命令执行顺序。原因在于MYSQL的存储过程执行完成后除了返回实际结果集还会返回存储过程执行的转态

，而上面的代码仅处理了第一个结果集，第二个结果集并没有被释放掉。

When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the SELECT CALL inside the stored procedure. 2ndfor the call of the stored procedure itself

 (2nd usually is only an OK or ERR packet).

要解决这个问题，需要用mysqli的multi_query方法，遍历所有的结果集并释放掉掉。代码如下：

 

<?php
$mysqli = new mysqli("localhost", "root", "sbqcel", "test");

$mysqli->multi_query("set names 'utf8'");

if (mysqli_connect_errno()) 
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
echo 'result1:<br />';
$mysqli->autocommit(FALSE);
if ($mysqli->multi_query("call test1();")) 
{
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->close();
        }
    } while ($mysqli->next_result());
}
$mysqli->commit();
echo "<br />";
echo "result2:<br />";
if ($result2 = $mysqli->query("select val from tb1;")) 
{
    while ($row = $result2->fetch_row()) {
        printf ("%s <br />", $row[0]);
    }
    $result2->close();
}
else
{
    echo $mysqli->error;
}
$mysqli->close();
?>