ERROR 2014: "Commands out of sync;" when doing a SELECT After CALL

[Peter Postma]

Hi all,

I am hoping you are able to help me with a problem I'm facing.

I have the following setup(s):

* PHP 5.3.5
* MySQL client: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $ (default of PHP 5.3.5)
* MySQL Server: 5.1.51-community (local dev setup) & 5.1.50-community (external hosting)
* IIS5.1 on WinXP SP3 (local dev setup) & Apache/2.2.8 (Win32) mod_ssl/2.2.8 OpenSSL/0.9.8g mod_python/3.3.1 Python/2.5.4 PHP/5.3.5 DAV/2 SVN/1.6.5

I am trying to run 2 SQL queries:

<?php
	header("Content-type: text/plain");
	
	$db = new mysqli("localhost","root","toor","test");
	
	$sql2 = "CALL `test_call`()";
	$result2 = $db->query($sql2);
	$table2 = array();
	while($table2[]=$result2->fetch_assoc());
	$result2->free();
	array_pop($table2);
	var_dump($table2);
	
	$sql3 = "SELECT VERSION()";
	$result3 = $db->query($sql3);
	if ($result3 === false)
	{
		var_dump($db);
	}
	$table3 = array();
	while($table3[]=$result3->fetch_assoc());
	$result3->free();
	array_pop($table3);
	var_dump($table3);
	
?>

the CALL looks like this:

CREATE PROCEDURE `test_call`()
	LANGUAGE SQL
	NOT DETERMINISTIC
	CONTAINS SQL
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	SELECT VERSION();
END

When running this code, the output is as following:

array(1) {
  [0]=>
  array(1) {
    ["VERSION()"]=>
    string(16) "5.1.51-community"
  }
}
object(mysqli)#1 (17) {
  ["affected_rows"]=>
  int(1)
  ["client_info"]=>
  string(48) "mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $"
  ["client_version"]=>
  int(50007)
  ["connect_errno"]=>
  int(0)
  ["connect_error"]=>
  NULL
  ["errno"]=>
  int(2014)
  ["error"]=>
  string(52) "Commands out of sync; you can't run this command now"
  ["field_count"]=>
  int(1)
  ["host_info"]=>
  string(20) "localhost via TCP/IP"
  ["info"]=>
  NULL
  ["insert_id"]=>
  int(0)
  ["server_info"]=>
  string(16) "5.1.51-community"
  ["server_version"]=>
  int(50151)
  ["sqlstate"]=>
  string(5) "HY000"
  ["protocol_version"]=>
  int(10)
  ["thread_id"]=>
  int(12)
  ["warning_count"]=>
  int(0)
}

Fatal error: Call to a member function fetch_assoc() on a non-object in <path>\test_call.php on line 21

As you can see, I neatly free() the $result's... but I still get the "Commands out of sync" error. Does anyone know why this is happening? This is a working example of a real live script that I'm running which is suffering from this problem...



Edited 3 time(s). Last edit at 02/10/2011 09:54AM by Peter Postma.