MySQL Bugs: #13543: crash when creating a stored procedure

Bug #13543	crash when creating a stored procedure
Submitted:	27 Sep 2005 20:55	Modified:	8 Nov 2005 21:27
Reporter:	Andrea Rui	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	MySQL 5.0.13-rc-nt up to 5.1.26	OS:	Windows (Win XP SP2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
when creating a stored procedure from the command line (mysql), the server crashes.
The service needs to be manually restarted.

I tried to uninstall and then re-install mysql from scratch, but the problem remained.

How to repeat:
the insert statement works fine when issued through phpmyadmin 2.6.2.

DROP PROCEDURE IF EXISTS SP_test;

delimiter //

CREATE PROCEDURE SP_test ()
BEGIN
  insert into test_table (text_field) values ('goofy');
END
//

I was unable to repeat:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP PROCEDURE IF EXISTS SP_test;
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>
mysql> delimiter //
mysql>
mysql> CREATE PROCEDURE SP_test ()
    -> BEGIN
    ->   insert into test_table (text_field) values ('goofy');
    -> END
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql>

Did you selected a database before try to create the stored
procedure ? If yes then this bug is duplicate of:

http://bugs.mysql.com/bug.php?id=13514

Thanks in advance.

yes, I selected a database, which I suppose being the right thing.
So, we should not fall into bug #13514

here is the dump of my session:
at the end the servere crashes and the client loses the connection.

Thanks in advance

C:\Documents and Settings\arui>mysql -D test -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> DROP PROCEDURE IF EXISTS SP_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE SP_test ()
    -> BEGIN
    -> insert into test_table (text_field) values ('goofy');
    -> END
    -> //
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> Aborted

I was unable to repeat doing the test with packages installer/non-installer
and XP/Windows 2003:

Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.13-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Database changed
mysql> DROP PROCEDURE IF EXISTS SP_test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE SP_test ()
    -> BEGIN
    -> insert into test_table (text_field) values ('goofy');
    -> END
    -> //
Query OK, 0 rows affected (0.05 sec)

mysql>

The only guess I can have if you did an upgrade over an older server version
as 5.0.9 which was already reported, when creating new procedures when
exists proedures created with that older version.

the problem was most probably due to corrupted data files.
I tried some times to re-install the software and re-created the databases, but I never removed the datafiles, so the problem persisted.
In a final try I removed also the datafiles, and re-created the databases.
Now the problems seems to be solved.