Thursday, November 09, 2006

"mySQL server has gone away" in PHP when using mySQL stored procedures

There seems to be a lot of conflicting information floating around about stored procedures in PHP, and I spent a bit of time myself grappling with a "mysql server has gone away" error when using the CALL statement to run a stored procedure. I think I've managed to get things squared away, both in the code and in my head, as to the caveats of mySQL 5 stored procedures in PHP. Here's the lowdown:

Per mySQL's own documentation for the C API of mysql_next_result:
"each CALL returns a result to indicate the call status, in addition to any results sets that might be returned by statements executed within the procedure."

This means that you should use mysqli_multi_query() when calling your stored procedure.

Additionally, this means that you cannot simply call mysqli_store_result() to store your buffered result in a variable then move on to your next query, which is perfectly reasonable for normal queries. Even for procedures that return a single resultset, you need to make sure you iterate through the "call status" result even though you won't be using it for anything. In fact, this status result seems to be kind of nebulous in that you can't use_result() or store_result() on it (as far as I can tell), but you need to iterate through it (via next_result()) before executing another query.

My problem with the way this all works is that since all of my procedures only returned a single result set, I wanted to be able to access them exactly as I would any other query (including storing the result so I could use it later), without having to manually iterate through this extra status result each time I called a procedure. Below is what I came up with.

Note: I'm using the procedural functions for the mysqli interface because people who are just getting acquainted with mysqli will recognize the syntax more easily, but you should definitely look into the object oriented approach for using mysqli functions.


function &procedure_get_result( $procedure_call ) {

//
//note: error handling has been removed from this function
//for the sake of the example, but you'll want to check the
//return values of functions like mysqli_connect(), etc...
//

$result_count = 0;
$result_loop_silence = 0;
$false_ret = false;

$dbh = mysqli_connect("localhost", "my_user", "my_password", "my_dbname");

//
// if the word CALL was included in the parameter, just strip it out.
// We'll add it later.
//
if ( substr(strtoupper($procedure_call), 0, 4) == 'CALL' ) {
$procedure_call = substr( $procedure_call, 4 );
}

$procedure_call = trim($procedure_call);

$query = "CALL {$procedure_call}";

if ( mysqli_multi_query($dbh, $query) ) {

$result = mysqli_store_result($dbh); //this is the result we want!

while ( mysqli_more_results($dbh) ) {

//
// iterate through the call status result set
//
if ( $result_count > 0 ) {
//
// this function only supports a 0th and a 1st result set
// so show a warning if we have more
//
if ( !$result_loop_silence ) {
trigger_error( 'Too many results returned for ' . __METHOD__ . '. Use multi query.', E_USER_WARNING );
$result_loop_silence = 1;
}
}

mysqli_next_result($dbh);

if ( $status_result = mysqli_use_result($dbh) ) {

//
// this never seems to get executed with
// CALL that only return one result set plus the
// status resultset, but we'll leave it in
//

$status_result->close();
mysqli_free_result($status_result);

}

$result_count++;
}

return $result;

}

return null;
}

1 comment:

BUY WOW GOLD said...

Nice blog. I a also ardent player of WOW GOLD. I love this game. Nice posting about wow gold. Thanks