MySQL Bugs: #18422: Error Handler gives Lost Connection or catches nonexistant error in stored proc

Bug #18422	Error Handler gives Lost Connection or catches nonexistant error in stored proc
Submitted:	22 Mar 2006 14:29	Modified:	24 Apr 2006 11:31
Reporter:	Giles McArdell	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (SUSE 10)
Assigned to:		CPU Architecture:	Any

Description:
This is similar to bugs #15752 & #15853.

I have a series of nested stored procedures, the outer one loops through a sequence of dates, this calls a controlling procedure which in turn calls a series of other stored procedures.

Each stored procedure has an error handler as below:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET v_Msg = CONCAT(v_Proc, ' FAILED in section "', v_Section, '"');
SET @Return_Error = 99;
END;

With these handlers in place I get a 'Connection Lost' error, although the connection still appears to be OK after the error. This is at a varying point in the sequence (not necessarily the same stored procedure or date being processed each time).
Without the handlers in the sequence runs fine.
As I attempted to work round the bug I have also had it jump into the handler in a procedure but, when the handler is removed and the sequence re-run, no error occurs.

The point at which connection is reported lost will be consistent while no changes are made, as soon as some changes are made to the procedures (normally to try and pinpoint the problem) the connection lost point will occur somewhere else.

I hope the above is enough for you to go on, as the nature of the bug makes it impossible to post some code to demonstrate it.

I have just upgraded to MySQL 5.0.19, the same bug occurred in 5.0.18.

How to repeat:
Sorry, I wish I could give you a piece of code to repeat this but its just not consistent enough.

Suggested fix:
Not a fix but the only work-around I have found is to not use error handlers for now.

Thank you for a problem report. Complete set of storage procedures (simplest one) and actual CALL statement that demonstrates the behaviour you described are really needed to verify this report.

Thank you, I understand the problems you would have analysing such a bug without a suitable example.

However the bug does not lend itself to being demonstrated with a simple example. I have attempted to create several test Stored Procs but the bug insists on only occurring in the full process and, as stated before, not always in the same place in that process.

To give you the full set of code is not practical, there are 7 functions and 19 stored procs (over 4000 lines, much of which constitues my companies intellectual property). 

Also a minimum of some 20 tables, half of which need to be populated with data, probably about 1GB, some of which is sensitive client data.

Sorry. :(

What I can do is give you some more information from my investigations:

The fix by removing the Handler is a red herring - this did get the code to work but the bug re-appeared later on (after some minor code changes).

The bug has also manifested itself as Error 124 and is similar to bug #14616 (which is supposedly fixed).

Bug #14616 is MyISAM only and I can confirm this is the same here, if I change the underlying tables to be InnoDB the bug does not occur.

I have had a colleague test the code on a second MySQL (v5.0.19) server (SUSE Linux) and he has successfully repeated the bug.

I shall continue to attempt to re-produce the bug in simpler form and will post if I succeed.

Regards,

Giles

Thank you for the additional information. Please, try to create a smaller test case and reopen this report when you'll have more ideas on how to repeat this problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".