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

Edit report at https://bugs.php.net/bug.php?id=67168&edit=1 ID: 67168 Updated by: johannes@php.net Reported by: php at lummert dot net Summary: connection hangs after stored procedure call -Status: Open +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: 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 Previous Comments: ------------------------------------------------------------------------ [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