• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

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
0
minnirok
Asked:
minnirok
  • 3
  • 3
1 Solution
 
TomeeboyCommented:
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!
0
 
minnirokAuthor Commented:
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
0
 
Muhammad WasifCommented:
Try using $result->close(); before the 2nd query
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
minnirokAuthor Commented:
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.
0
 
minnirokAuthor Commented:
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.
0
 
Muhammad WasifCommented:
>>> I think you can only call one stored procedure and then the connection is automatically closed
I am not agree with this, though i dont have any proof but...:-)

Check comment in Example 2 at http://www.php.net/mysqli_query which says
"Note, that we can't execute any functions which interact with the server until result set was closed. All calls will return an 'out of sync' error"

But your error is different.
0
 
Muhammad WasifCommented:
Did you solve the problem? What was the reason?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now