MeekroDB error "Commands out of sync; you can't run this command now"

Question

I have a PHP script with the following lines:

require_once 'meekrodb.2.1.class.php';
DB::$user = 'usr';
DB::$password = 'pwd';
DB::$dbName = 'db';
DB::$encoding = 'utf8';

$results = DB::queryFirstField("
    CALL getSequence('time_id', %i); // ***** Stored procedure call *****
", TENANT_ID);

DB::insert('timeentry', array(
    'tenant_id' => TENANT_ID,
    'time_id' => $results,
    'timestart' => DB::sqleval("now()"),
    'assig_id' => $assig_id
));

I am getting the following error:

QUERY: INSERT INTO timeentry (tenant_id, time_id, timestart, assig_id) VALUES (1, '42', now(), '1')

ERROR: Commands out of sync; you can't run this command now

If I replace the call to the stored procedure with a SELECT statement, everything works fine.

$results = DB::queryFirstField("
    SELECT 45; // ***** SELECT statement *****
");

DB::insert('timeentry', array(
    'tenant_id' => TENANT_ID,
    'time_id' => $results,
    'timestart' => DB::sqleval("now()"),
    'assig_id' => $assig_id
));

I have not analyzed the internals of the MeekroDB Library (http://www.meekro.com).

I tried wrapping each statement in a transaction but I get the same error when COMMIT is executed right after the call to the stored procedure.

Any help is greatly appreciated.

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

Calls to stored procedures in MySQL produce multiple result sets. That is, a stored proc might have more than one SELECT, so the client has to iterate through several result sets to finish processing the CALL.

See examples in the answer to this question: Retrieving Multiple Result sets with stored procedure in php/mysqli

Until all results from the CALL are finished, it isn't considered closed, and MySQL does not permit you to run another query before the current query is completely finished.

I don't know the MeekroDB library well, but glancing at the online docs I don't see any way to iterate through multiple result sets. So there may not be any way to call stored procedures safely. I suggest you contact the author for specific support: http://www.meekro.com/help.php.

edited May 23 '17 at 12:12

Community

1
1

answered Dec 13 '12 at 21:29

Bill Karwin

538,548
86
673
828

Thanks Bill for your answer, how can I know for sure if the stored procedure is getting one or more result sets. I have executed it in a MySQL client and I only get one result... it is probably a MeekroDB bug. I have already contacted the author. Can I execute a command (outside this library) in order to close the pending query? – supercoco Dec 13 '12 at 21:47
1

I managed to close the connection by altering the library. Thanks for your help. Of course I hope to get the official fix for this issue. – supercoco Dec 13 '12 at 22:06
1

Even if a given stored proc returns only one result set, call it invokes the multi-query interface. This is probably just an API usage that the MeekroDB developer didn't anticipate. Yes, you can close the statement, or you can call `while ($pdoStmt->nextRowset()) { }` to force it to iterate to the end of the chain of results. See also http://php.net/manual/en/pdostatement.nextrowset.php – Bill Karwin Dec 13 '12 at 23:08

MeekroDB error "Commands out of sync; you can't run this command now"

1 Answers1

Linked