asked on

some stored procedure help

Hi,

I'm calling a stored procedure from a PHP script - which works - but then all subsequent SQL queries fail with the message:

"Lost connection to MySQL server during query"

My procedure runs fine in the MySql prompt, it just does something simple for testing like SELECT * FROM my_table.

Then my PHP script looks like:

$result = mysqli_query($conn, "call sp_FirstTry()");
if (!$result) {
return "Failed executing stored proc: [" . mysqli_error($conn) . "]";
}

// Fails on this next call?
$result = mysqli_query($conn, "SELECT * FROM my_table");
if (!$result) {
return "Failed on next SQL interaction!?!?!?: [" . mysqli_error($conn) . "]";
}

Any ideas what's going on?

Thanks

Tomeeboy

Hi minnirok,

With your query that is calling the stored procedure, try using mysqli_multi_query() instead of mysqli_query(). There are probably leftover results that haven't been cleared out from the procedure, which would cause your next query to get that error.

$result = mysqli_multi_query($conn, "call sp_FirstTry()");

Hope that helps!

minnirok

ASKER

Hi Tomeeboy,

No luck, if I use the multi() version now I get the error:

"MySQL server has gone away"

I followed the example from the PHP manual site. This is really frustrating. There must be some sort of call I'm missing with the stored procedure. The contents of the stored procedure is literally:

"SELECT COUNT(*) AS something WHERE name = 'bob';

If you have any other ideas please let me know, thanks

Muhammad Wasif

Try using $result->close(); before the 2nd query

minnirok

ASKER

Hi wasifg,

I tried:

$result->close();

and also:

mysqli_free_result($result);

but neither did anything. Still the same bizarre error message. This is a most cruel situation to be in.

minnirok

ASKER

I think you can only call one stored procedure and then the connection is automatically closed? There seems to be no other explanation. I think stored procedures are supposed to be packed with multiple calls anyways, so there is no point to having something happen after it. As far as I can tell.

ASKER CERTIFIED SOLUTION

Muhammad Wasif

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Muhammad Wasif

Did you solve the problem? What was the reason?