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
LVL 7
minnirokAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Muhammad WasifCommented:
Did you solve the problem? What was the reason?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.