php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #35203 Unable to call multiple stored procedures with single mysql connection
Submitted: 2005-11-12 22:27 UTC Modified: 2005-11-14 11:02 UTC
From: kristaps dot kaupe at itrisinajumi dot lv Assigned:
Status: Not a bug Package: MySQLi related
PHP Version: 5.0.5 OS: Gentoo Linux
Private report: No CVE-ID: None
View Developer Edit
 [2005-11-12 22:27 UTC] kristaps dot kaupe at itrisinajumi dot lv 
Description:
------------
Create MySQL test table and procedure:
--------------------------------------
CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `txt` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test_table (txt) VALUES ('test1');

CREATE PROCEDURE `test_proc`(IN p_id INT(10) UNSIGNED) READS SQL DATA DETERMINISTIC
BEGIN
   SELECT * FROM test_table WHERE id = p_id; 
END


Reproduce code:
---------------
// $db is mysqli object

if ($result = $db->query('CALL test_proc(1);')) {
    $res = $result->fetch_assoc();
    print_r($res);
    $result->close();
}
else
    echo '<br />'.$db->error.'<br />';

if ($result = $db->query('CALL test_proc(1);')) {
    $res = $result->fetch_assoc();
    print_r($res);
    $result->close();
}
else
    echo '<br />'.$db->error.'<br />';


Expected result:
----------------
Array
(
    [id] => 1
    [txt] => test1
)

Array
(
    [id] => 1
    [txt] => test1
)

Actual result:
--------------
Array
(
    [id] => 1
    [txt] => test1
)

Lost connection to MySQL server during query

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-13 02:09 UTC] kristaps dot kaupe at itrisinajumi dot lv 
Additional thing - when you don't send any requests to Apache and MySQL for some time, sample script seems to work. But just for one request, when you press "Refresh" in your browser and run it second time, it gives output i've posted.
 [2005-11-13 08:22 UTC] georg@php.net 
For calling stored procedures you have to use mysqli_multi_query.
 [2005-11-13 15:55 UTC] kristaps dot kaupe at itrisinajumi dot lv 
Why I should use mysqli_multi_query()? Where is it documented, that I should use mysqli_multi_query() for stored procedures?

But the following code with mysqli_multi_query() doesn't work either:

-------
// $db is mysqli object
if ($db->multi_query('CALL test_proc(1); CALL test_proc(1);')) {
    if ($result = $db->store_result()) {
        $res = $result->fetch_assoc();
        print_r($res);
        $result->close();
    }
    else
        echo '<br />No result!<br />';
    $db->next_result();
    if ($result = $db->store_result()) {
        $res = $result->fetch_assoc();
        print_r($res);
        $result->close();
    }
    else
        echo '<br />No result<br />';
}
else
    echo '<br />'.$db->error.'<br />';
-----

Produces the following output:
-----
Array
(
    [id] => 1
    [txt] => test1
)

No result
-----

(Expected was two identical results)
 [2005-11-13 18:36 UTC] georg@php.net 
When a stored procedure returns a resultset, MySQL returns at least two resultsets: first for the SELECT CALL inside the stored procedure. 2nd for the call of the stored procedure itself (2nd usually is only an OK or ERR packet)

mysqli_query fetches only one resultset, the second is still on socket: subsequent mysqli_ calls will fail, cause the sockets is still blocked.

Therefore you should use mysqli_next_result and mysqli_multi_query instead.
 [2005-11-14 10:46 UTC] kristaps dot kaupe at itrisinajumi dot lv 
Ok, got the point. Additional $db->next_result() with $db->multi_query() was solution. My fault, sorry!
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 21 14:01:29 2024 UTC