MySQL stored procedures and out of sync

    Today I found out for a very long time the reason why, after I make a MySQL query, in which I refer to the stored procedure (PHP 5, MySQL 5, mysqli driver)
    CALL procedureName ()

    then the request following it is not executed, and mysqli_error returns an error

    Error "Commands out of sync; you can't run this command now ¬Ľnumber" 2014 "in query: ...

    Judging by mistake, it seemed that the connection was lost. This was misleading and led the wrong way.

    As it turned out, everything comes from the fact that my procedure returns the result in the form of a table, and not a single result (a number or a string or boolean). For example, the result of such a query will be a table:
    SELECT * FROM users;

    If the stored procedure returns such a table, then in addition to this table, another value is also returned - the result of the procedure itself. This behavior of the mysqli driver is called a multiple result . So, most developers take the first and most often the only result. In the case when the procedure returns the table, the secondary result must also be pulled out, otherwise the results buffer will remain uncleaned and the rest of the requests will not pass - they will not be able to write there.

    In the general case, you need to reset the unnecessary results by pulling them out of the buffer after the first result is taken with the usual fetch:
    // $ connection - your MySQLI object
    while ($ connection-> next_result ()) $ connection-> store_result ();

    Thanks to the man under the nickname Evert, who prompted the answer to this question.

    Also popular now: