[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-05-22
3
Medium Priority
?
1,028 Views
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))
BEGIN
     /* generates a new referencenumber for the specific expression (counting) */
     DECLARE IDNum, ID, ReferenceNr INT;

     START TRANSACTION;

     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();
     ELSE
        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; */
     COMMIT;

     SELECT ReferenceNr as Reference;
END

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
Albie
0
Comment
Question by:neonpill
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 1500 total points
ID: 16825891
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?
0
 
LVL 1

Author Comment

by:neonpill
ID: 16831493
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 http://forums.mysql.com/read.php?98,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.
0
 
LVL 30

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 9 hours left to enroll

868 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