"Lost connection to MySQL server during query" error after calling stored procedure

Posted on 2006-05-22
Last Modified: 2008-01-09
Hi Experts

_Leading up to the problem_: I have a stored procedure that I need to access through ASP/ODBC from a MySQL database (ver 5.0.20-nt), running on Windows NT.  I have a problem assigning execute permission to the user that will be calling the SP, since "GRANT EXECUTE ON dev.GetNextOrderReference TO username" errors (apparently this only works on version 5.0.3, which is in development), so I just used "GRANT ALL ON dev.* TO username" instead.

_The Problem_: Now, unfortunately, it seems that I'm experiencing trouble with a timeout.   The stored procedure works perfectly when run out of MySQL Query Browser, but when I call it from an ASP script, I get

    "Microsoft OLE DB Provider for ODBC Drivers error '80040e31'
     [MySQL][ODBC 3.51 Driver][mysqld-5.0.20-nt]Lost connection to MySQL server during query"

on all database calls subsequent to the stored procedure call for a short while (less than a minute).  This interferes with my ability to have a page that does multiple database calls (this particular page does 20-30 seperate queries).

Please help me find a way to get the stored procedure to execute and not cause problems!

The stored procedure looks as follows:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetNextOrderReference`(OrderNumberExpression VARCHAR(255))
     /* generates a new referencenumber for the specific expression (counting) */
     DECLARE IDNum, ID, ReferenceNr INT;


     SELECT COUNT(OrderNumberID) INTO IDNum FROM OrderNumbers WHERE Expression = OrderNumberExpression;

     IF IDNum <= 0 THEN
        INSERT INTO OrderNumbers (Expression, CurrValue) VALUES (OrderNumberExpression, 0);
        SET ID := LAST_INSERT_ID();
        SELECT OrderNumberID INTO ID FROM OrderNumbers WHERE Expression = OrderNumberExpression;
     END IF;

     UPDATE OrderNumbers SET CurrValue = (CurrValue + 1) WHERE OrderNumberID = ID;

     SELECT CurrValue INTO ReferenceNr FROM OrderNumbers WHERE OrderNumberID = ID;
     /* RETURN ReferenceNr; */

     SELECT ReferenceNr as Reference;

As you can see, it is not an immensely taxing procedure, and returns a single-cell row only.  So the problem is not a traffic-related one.

Thank you
Question by:neonpill
    LVL 30

    Accepted Solution

    I don't know if this is the answer or not, but I've always been suspicious of stored procedures that implicitly return data to the client by executing a SELECT at the end of the procedure.  In this case, you are returning one value - why don't you register it as an OUT parameter in the procedure itself?
    LVL 1

    Author Comment

    Ah yes, that seems to sort out the problem I'm having - I can now execute the stored proc and go straight on with my other processing.

    Of course, finding an effective way for MySQL to return variables to ASP proved to be a bit more of a mission than I expected.  I ended up using the method discussed at,82009,88532 , setting temporary variables in MySQL, and then selecting them.  If there is an effective alternative using ADO, I would appreciate someone telling me.
    LVL 30

    Expert Comment

    Glad that works for you.  Sorry I can't comment intelligently on the ADO aspect - my experience is more with Java and PHP.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    728 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

    15 Experts available now in Live!

    Get 1:1 Help Now