MySQL Bugs: #24783: Commands out of sync error when calling stored procedures from PHP and Perl

Bug #24783	Commands out of sync error when calling stored procedures from PHP and Perl
Submitted:	4 Dec 2006 0:35	Modified:	4 Dec 2006 12:35
Reporter:	Edoardo Serra	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.27	OS:
Assigned to:		CPU Architecture:	Any

Description:
From PHP and Perl I cannot call two stored procedure in the same connection (I guess it depends on the fact they are returning MULTI_RESULTS, it doesnt happen with normal SELECTs)

When I run the second one I get the following error: "Commands out of sync; you can't run this command now".

I made a lot of tests with different MySQL versions, at the moment I'm running a 5.0.27 built from source and PHP 4.4.4 built from source and linked to 5.0.27 (not using the bundled library) and I get that error but, for example, I got it working with MySQL 5.1.7 beta (but not with 5.1.12 beta)

How to repeat:
Use the following SQL to create a test database:

CREATE TABLE `Accounts` (
  `Id` int(11) NOT NULL auto_increment,
  `Username` varchar(52) NOT NULL,
  `Password` varchar(52) default '',
  `Status` enum('enabled','disabled') NOT NULL default 'disabled',
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

INSERT INTO Accounts (Username, Status) VALUES ('webrainstorm', 'enabled');

DELIMITER //

CREATE PROCEDURE test_proc (IN account VARCHAR(64))
BEGIN
SELECT Status as valid from Accounts WHERE Username=account AND Status='enabled' LIMIT 1;
END
//

DELIMITER ;

Then use the following PHP script to execute queries

#!/usr/bin/php -q
<?php
define('CLIENT_MULTI_RESULTS', 131072);

$conn = mysql_connect("localhost", "root", "", 1 , CLIENT_MULTI_RESULTS) or die(mysql_error());
mysql_select_db("tester") or die(mysql_error());

$query = "CALL test_proc('webrainstorm')";

for ($i = 1; $i < 5; $i++) {
        $ret = mysql_unbuffered_query($query) or die(mysql_error()."\n");
        while ($row = mysql_fetch_array($ret, MYSQL_ASSOC)) {
                foreach ($row as $col) {
                        echo "$col\t";
                }
                echo "\n";
        }
        mysql_free_result($ret);
        unset($ret);
}

mysql_close($conn);
?>

I get the following output: 
# ./test.php
enabled
Commands out of sync; you can't run this command now

Expected output is:
# ./test.php
enabled
enabled
enabled
enabled

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully how to handle Multiple Statement Execution at http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html

Tnx for your answer, I see we have to use mysql_next_result to correctly handle multi_results but I cannot explain myself why it is working with some version of libmysqlclient.

For example it was working with 5.0.12-beta and 5.1.7-beta but not with 5.0.27 and 5.1.13 from bitkeeper, that's why I opened that bug.

Does it make sense ? are there important changes between them ?
Is possibile and safe to use 5.1.7 client lib with 5.0.27 server ?

Tnx

Regards