Bug #67168 [Nab]: connection hangs after stored procedure call

Edit report at https://bugs.php.net/bug.php?id=67168&edit=1 ID: 67168 User updated by: php at lummert dot net Reported by: php at lummert dot net Summary: connection hangs after stored procedure call Status: Not a bug Type: Bug Package: MySQLi related Operating System: Windows 7 PHP Version: 5.5.12 Block user comment: N Private report: N New Comment: 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"! Previous Comments: ------------------------------------------------------------------------ [2014-05-07 08:26:01] 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 05:12:36] 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-06 08:22:45] 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-01 15:35:50] php at lummert dot net expected result is of cause: 1, 2, 3|1, 2, 3 ------------------------------------------------------------------------ [2014-05-01 12:14:27] 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 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=67168&edit=1