MySQL stored procedures and out of sync

Today I figured out a very long time the reason why after I make a MySQL request in which I access the stored procedure (stored procedure) (PHP 5, MySQL 5, mysqli driver)

CALL procedureName ()

then the following request 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 the error, the impression was 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 as a table, and not a single result (number or string or boolean). For example, the result of such a query would be a table:

SELECT * FROM users;

If the stored procedure returns such a table, then in addition to this table, another value is also output - 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 also needs to be pulled out, otherwise the results buffer will remain uncleaned and other requests will not pass - they will not be able to write there.

In the general case, after the first result is fetched by the usual fetch, you need to reset the unnecessary results by pulling them out of the buffer:

// $ connection - your MySQLI object
while ( $ connection -> next_result ()) $ connection -> store_result ();

Thanks to the person under the name Evert, who suggested the answer to this question.