MySQL Bugs: #15259: detail: server crushes, when information

Bug #15259	detail: server crushes, when information_schema.routines is accessed
Submitted:	26 Nov 2005 11:04	Modified:	27 Nov 2005 14:33
Reporter:	Andreas Kolbe	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.16	OS:	Windows (Win2003)
Assigned to:		CPU Architecture:	Any

Description:
After editing a stored procedure, that I redently created, the server crashed, leaving no entries in the errlog. Starting up the service and trying to access any database thereafter has a 98% chance of crashing the server again, leaving again no entries in the log. This behaviour is same with mysql-nt or mysqld.

How to repeat:
Create this procedure:

CREATE PROCEDURE `mydatabase`.`sp_mystoredproc` (IN MN smallint(4),FN tinyint(2))
BEGIN
SELECT mnummer,fnummer,COUNT(id) AS anzahl
FROM role
WHERE castid IN (
SELECT castid FROM role WHERE mnummer=MN AND fnummer=FN ORDER BY castid ASC
)
GROUP BY mnummer,fnummer
ORDER BY anzahl DESC
LIMIT 1,5;
END

Afterwards edit the proc, try to fix the parameters to: (IN MN smallint(4),IN FN tinyint(2))
[notice the "IN" for the 2nd param!]
when submitting the statement, the server goes down... from then on, you're there!

Suggested fix:
Simply no idea... the logs were no help to me!

Thank you for a problem report. What tool do you use to edit the procedure? If it is Query Browser or other GUI tool, please, specify it's version too.

Can you, please, try to repeat all the actions in mysql client? Connect to the database, create that role table, create procedure, then drop procedure and create it with changed parameters. Will you get a non-stable behaviour in this case?

Hi Valeriy!

I used the MySQL Administrator 1.1.5. With the actual condition of the MySQL installation, I can't rework anything, because whenever I submit a statement, that leads to an access for information_schema.routines, the server crushes. This includes CREATE PROCEDUREs as well as SELECT statements.

You either have a clue for me, on how to fix this or I'd have to install a second instance for further tests. I'd prefer fixing the current installation, but I doubt, that this is possible?

Regards from Berlin... Andreas

Looks like I found the reason for the crash. I think, your report is a duplicate of http://bugs.mysql.com/bug.php?id=14569, really. That known bug will be fixed in 5.0.17 only. So, please, wait for the release.

But there is a workaround. Please, read the bug #14485. The immediate problem is that Administrator 1.1.5 (as well as 1.1.4), as I just checked once more, do not add database name to procedure name, and do not perform 'use db' explicitely. It leads to the crash. So, if you'll try to add that name explicitely (as it is done when you create new SP), you may be able to continue working with Administrator successfully even on this 5.0.16 version...