#35203 [Bgs-Csd]: Unable to call multiple stored procedures with single mysql connection
ID: 35203
User updated by: kristaps dot kaupe at itrisinajumi dot lv
Reported By: kristaps dot kaupe at itrisinajumi dot lv
-Status: Bogus
+Status: Closed
Bug Type: MySQLi related
Operating System: Gentoo Linux
PHP Version: 5.0.5
New Comment:
Ok, got the point. Additional $db-next_result() with
$db-multi_query() was solution. My fault, sorry!
Previous Comments:
[2005-11-13 18:40:19] [EMAIL PROTECTED]
fixed status
[2005-11-13 18:36:55] [EMAIL PROTECTED]
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-13 16:33:03] kristaps dot kaupe at itrisinajumi dot lv
Status change to Open.
[2005-11-13 15:55:15] 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 resultbr /';
}
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 08:22:49] [EMAIL PROTECTED]
For calling stored procedures you have to use mysqli_multi_query.
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/35203
--
Edit this bug report at http://bugs.php.net/?id=35203edit=1
#35203 [Bgs]: Unable to call multiple stored procedures with single mysql connection
ID: 35203
User updated by: kristaps dot kaupe at itrisinajumi dot lv
Reported By: kristaps dot kaupe at itrisinajumi dot lv
Status: Bogus
Bug Type: MySQLi related
Operating System: Gentoo Linux
PHP Version: 5.0.5
New Comment:
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 resultbr /';
}
else
echo 'br /'.$db-error.'br /';
-
Produces the following output:
-
Array
(
[id] = 1
[txt] = test1
)
No result
-
(Expected was two identical results)
Previous Comments:
[2005-11-13 08:22:49] [EMAIL PROTECTED]
For calling stored procedures you have to use mysqli_multi_query.
[2005-11-13 02:09:29] 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-12 22:27:09] 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
--
Edit this bug report at http://bugs.php.net/?id=35203edit=1
#35203 [Bgs-Opn]: Unable to call multiple stored procedures with single mysql connection
ID: 35203
User updated by: kristaps dot kaupe at itrisinajumi dot lv
Reported By: kristaps dot kaupe at itrisinajumi dot lv
-Status: Bogus
+Status: Open
Bug Type: MySQLi related
Operating System: Gentoo Linux
PHP Version: 5.0.5
New Comment:
Status change to Open.
Previous Comments:
[2005-11-13 15:55:15] 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 resultbr /';
}
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 08:22:49] [EMAIL PROTECTED]
For calling stored procedures you have to use mysqli_multi_query.
[2005-11-13 02:09:29] 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-12 22:27:09] 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
--
Edit this bug report at http://bugs.php.net/?id=35203edit=1
#35203 [NEW]: Unable to call multiple stored procedures with single mysql connection
From: kristaps dot kaupe at itrisinajumi dot lv
Operating system: Gentoo Linux
PHP version: 5.0.5
PHP Bug Type: MySQLi related
Bug description: Unable to call multiple stored procedures with single mysql
connection
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
--
Edit bug report at http://bugs.php.net/?id=35203edit=1
--
Try a CVS snapshot (php4): http://bugs.php.net/fix.php?id=35203r=trysnapshot4
Try a CVS snapshot (php5.0):
http://bugs.php.net/fix.php?id=35203r=trysnapshot50
Try a CVS snapshot (php5.1):
http://bugs.php.net/fix.php?id=35203r=trysnapshot51
Fixed in CVS:http://bugs.php.net/fix.php?id=35203r=fixedcvs
Fixed in release:http://bugs.php.net/fix.php?id=35203r=alreadyfixed
Need backtrace: http://bugs.php.net/fix.php?id=35203r=needtrace
Need Reproduce Script: http://bugs.php.net/fix.php?id=35203r=needscript
Try newer version: http://bugs.php.net/fix.php?id=35203r=oldversion
Not developer issue: http://bugs.php.net/fix.php?id=35203r=support
Expected behavior: http://bugs.php.net/fix.php?id=35203r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=35203r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=35203r=submittedtwice
register_globals:http://bugs.php.net/fix.php?id=35203r=globals
PHP 3 support discontinued: http://bugs.php.net/fix.php?id=35203r=php3
Daylight Savings:http://bugs.php.net/fix.php?id=35203r=dst
IIS Stability: http://bugs.php.net/fix.php?id=35203r=isapi
Install GNU Sed: http://bugs.php.net/fix.php?id=35203r=gnused
Floating point limitations: http://bugs.php.net/fix.php?id=35203r=float
No Zend Extensions: http://bugs.php.net/fix.php?id=35203r=nozend
MySQL Configuration Error: http://bugs.php.net/fix.php?id=35203r=mysqlcfg
#35203 [Opn]: Unable to call multiple stored procedures with single mysql connection
ID: 35203
User updated by: kristaps dot kaupe at itrisinajumi dot lv
Reported By: kristaps dot kaupe at itrisinajumi dot lv
Status: Open
Bug Type: MySQLi related
Operating System: Gentoo Linux
PHP Version: 5.0.5
New Comment:
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.
Previous Comments:
[2005-11-12 22:27:09] 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
--
Edit this bug report at http://bugs.php.net/?id=35203edit=1
