"Lost connection to MySQL server during query" error after calling stored procedure
Posted on 2006-05-22
_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;
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.