some stored procedure help

Posted on 2006-04-07
Last Modified: 2013-12-12

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?

Question by:minnirok
    LVL 15

    Expert Comment

    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!
    LVL 7

    Author Comment

    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
    LVL 20

    Expert Comment

    by:Muhammad Wasif
    Try using $result->close(); before the 2nd query
    LVL 7

    Author Comment

    Hi wasifg,

    I tried:


    and also:


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

    Author Comment

    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.
    LVL 20

    Accepted Solution

    >>> 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 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.
    LVL 20

    Expert Comment

    by:Muhammad Wasif
    Did you solve the problem? What was the reason?

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    The viewer will learn how to dynamically set the form action using jQuery.
    The viewer will learn how to count occurrences of each item in an array.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now