Bug #13589 simple stored procedures through PHP mysqli API fail
Submitted: 28 Sep 2005 23:00 Modified: 29 Sep 2005 8:04
Reporter: Jasper Bryant-Greene Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.12-beta OS:Linux (Gentoo Linux x86)
Assigned to: CPU Architecture:Any

[28 Sep 2005 23:00] Jasper Bryant-Greene
Description:
Running a simple stored procedure through the PHP mysqli gives the error "Lost connection to MySQL server during query", although the server doesn't die.

In the code below, the second call to the stored procedure fails with the above error -- it is the first stored procedure that has crashed the connection though as any kind of query will fail as the second query.

How to repeat:
<?php
$db = new mysqli( 'localhost', 'root', 'password', 'test' );

$db->query( "CREATE TABLE users (
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8" );

$db->query( "INSERT INTO users (username) VALUES ('test')" );

$db->query( "CREATE PROCEDURE getUser( IN userID INT )
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
SELECT * FROM users WHERE id=userID LIMIT 1" );

$result = $db->query( "CALL getUser( 1 )" );

var_dump( $result->fetch_assoc() );

print( '<br>' );

$result = $db->query( "CALL getUser( 1 )" );

if( $result ) {
	var_dump( $result->fetch_assoc() );
} else {
	print( $db->error );
}
?>

Suggested fix:
Make it work?
[29 Sep 2005 8:04] Georg Richter
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

For calling/executing stored procedures, which return a resultset you have to use mysqli_multi_query ($db->multi_query()).
[29 Sep 2005 8:13] Andrey Hristov
The call to the SP returns 2 result sets - one for the SELECT and one for the SP itself. Thus, you have to use mysqli->more_results()/mysqli->next_result(). If you need only the first one you have to write a function that reads everything from the wire and thus cleaning it.
[19 Feb 2006 16:54] Eduard Aumüller
Hi, I am also having trouple with SPs and PHP5
I have this piece of code in my php script, however the script aborts without mysqli_error reporting (I placed a mysqli_error call behind each functioncall that uses a $conn parameter)

@mysqli_multi_query($conn,"CALL get_current_research('$userid');");
$res = @mysqli_use_result($conn);
... do something here...
@mysqli_free_result($res);
while(@mysql_next_result($conn))
{
	$res = @mysqli_use_result($conn);
	@mysqli_free_result($res);
}

I don t know whats wrong with the code above, its nearly identical to the examples in the php manual so I guess the problem lies somewhere else, maybe the mysqli extension?

I am running php5 on a apache2 server
[12 Jul 2006 13:09] Bogdan Enache
This should be clearly mentioned in the manual, I think.
Searching 5.0 docs for "multi_query" or "multi query" or "stored multi query" does not yield any related information.