php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #67168 connection hangs after stored procedure call
Submitted: 2014-05-01 12:14 UTC Modified: 2014-05-07 11:29 UTC
Votes:1
Avg. Score:2.0 ± 0.0
Reproduced:0 of 1 (0.0%)
From: php at lummert dot net Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.5.12 OS: Windows 7
Private report: No CVE-ID: None
 [2014-05-01 12:14 UTC] php at lummert dot net
Description:
------------
Calling a stored procedure with rowset result yields the connection corrupted as shown in example. Content of second query seems unimportant, I always get the same error.
After first call to sp and consumption of rowset as shown, next_result() method still yields true. store_result method returns no valid resultset, though.
When store_result is called after first sp call and result use, the next call actually succeeds, so this might be used as a workaraound.

Test script:
---------------
<?php
	$con = new mysqli('127.0.0.1', 'root', 'Y@pack', 'uschi');
	if ($con->connect_errno) { die('Failed to connect to database'); }
	
	$con->query('DROP PROCEDURE IF EXISTS sp_test');
	if ($con->errno) { die($con->error); }
	$con->query('CREATE PROCEDURE sp_test() BEGIN SELECT 1 AS a, 2 AS b, 3 AS c; END');
	if ($con->errno) { die($con->error); }

	if ( ! ($res = $con->query('call sp_test()'))) { die($con->error); }
	while ($row = $res->fetch_assoc()) {
		echo $row['a'], ', ', $row['b'], ', ', $row['c'];
	}
	$res->free_result();

	echo '|';

	if ( ! ($res = $con->query('SELECT 1 AS a, 2 AS b, 3 AS c;'))) { die($con->error); }
	while ($row = $res->fetch_assoc()) {
		echo $row['a'], ', ', $row['b'], ', ', $row['c'];
	}
	$res->free_result();
?>




--yields--> 1, 2, 3Commands out of sync; you can't run this command now



<?php
	$con = new mysqli('127.0.0.1', 'root', 'Y@pack', 'uschi');
	if ($con->connect_errno) { die('Failed to connect to database'); }
	
	$con->query('DROP PROCEDURE IF EXISTS sp_test');
	if ($con->errno) { die($con->error); }
	$con->query('CREATE PROCEDURE sp_test() BEGIN SELECT 1 AS a, 2 AS b, 3 AS c; END');
	if ($con->errno) { die($con->error); }

	if ( ! ($res = $con->query('call sp_test()'))) { die($con->error); }
	while ($row = $res->fetch_assoc()) {
		echo $row['a'], ', ', $row['b'], ', ', $row['c'];
	}
	$res->free_result();
	// bug workaraound for stored procedures delivering a row set
	if ($con->next_result()) { $res = $con->store_result(); }

	echo '|';

	if ( ! ($res = $con->query('SELECT 1 AS a, 2 AS b, 3 AS c;'))) { die($con->error); }
	while ($row = $res->fetch_assoc()) {
		echo $row['a'], ', ', $row['b'], ', ', $row['c'];
	}
	$res->free_result();
	// bug workaraound for stored procedures delivering a row set
	if ($con->next_result()) { $res = $con->store_result(); }
?>



--yields--> 1, 2, 3|1, 2, 3

Expected result:
----------------
1, 2, 3

Actual result:
--------------
1, 2, 3Commands out of sync; you can't run this command now

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-05-01 15:35 UTC] php at lummert dot net
expected result is of cause:
1, 2, 3|1, 2, 3
 [2014-05-06 08:22 UTC] johannes@php.net
-Status: Open +Status: Not a bug
 [2014-05-06 08:22 UTC] johannes@php.net
For stored procedures you have to use multi_query() instead of query() as the protocol works slightly different. See http://php.net/mysqli.quickstart.stored-procedures
 [2014-05-07 05:12 UTC] php at lummert dot net
If Stored Procedures should always be used with multi_query(), which is all but self-explanatory, an sp call using query() method MUST error out. As one of the key features of sp's is to keep the actual database structure opaque to its user, and thus the code of the procedure, a php-developer might not know, if a stored procedure delivers a resultset output or not (in addition to its documented behaiviour, e.g. one or more strings reporting on the results of the call might be returned as result set).
But the current behaviour of query() is to deliver all you want on the first sp call with result-set, even execute free_result() without any complaints, and then error out on any subsequent call.
This behaviour is completely erratic. If I've ever seen a bug, this is one!
 [2014-05-07 08:26 UTC] johannes@php.net
Still this is nothing PHP can fix in a sane way. (the unsane way is parsing the query and erroring out) The way this could e fixed is by changing the protocol and having the server error out. Thus MySQL maybe could fix it, not PHP.
 [2014-05-07 11:29 UTC] php at lummert dot net
Using mysqli_real_query() or mysqli_multi_query() yields exactly the same results as described for mysqli_query!
After fetching the one and only result set that is to be expected of the example procedure, $con->next_result() still yields true while another $con->store_result() returns false instead of a result set.

Please reset the status to "Open Bug"!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Wed Apr 24 19:01:31 2024 UTC