Bug #15367 Stored procedure edit in MA will cause server crash, corrupt stored procedures
Submitted: 30 Nov 2005 22:11 Modified: 30 Nov 2005 23:24
Reporter: Harrison Fisk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.16 OS:Microsoft Windows (Windows 2000 Professional)
Assigned to: CPU Architecture:Any

[30 Nov 2005 22:11] Harrison Fisk
Description:
When you create a procedure and then try to edit it using MySQL Administrator 1.1.5, it will cause the server to crash.  Upon it will cause the server to crash repeatedly when you do a SHOW PROCEDURE STATUS.

How to repeat:
1.  create a procedure in a database

root:(none)~> use test;
Database changed
root:test~> CREATE PROCEDURE foo () SELECT 1\g
Query OK, 0 rows affected (0.00 sec)

2.  Open MySQL Administrator 1.1.5
3.  Click on catalogs
4.  Select test database
5.  Click stored procedures tab
6.  Click on edit stored proc button
7.  Click "Execute SQL" (no need to change anything)
8.  Server should crash
9.  Restart server
10. Issue SHOW PROCEDURE STATUS, which will crash

Suggested fix:
Figure out what statement MA is using and figure out what went wrong.
Server shouldn't crash if mysql.proc table is messed up.
[30 Nov 2005 23:24] Miguel Solorzano
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

I was able to repeat with server 5.0.16 and the reason why it crashed is
the bug when trying to create a procedure without so select a database.
Then Administrator tool dropped the procedure and tries to create the
procedure without to select a database (see picture I will attach).

C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p --prompt="win5017>"
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.17-nt-max

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

win5017>use test;
Database changed
win5017>delimiter //
win5017>create procedure sp2()
    -> begin
    -> select 1+1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

win5017>show create procedure test.sp2;
    -> //
+-----------+----------+------------------------------------------------+
| Procedure | sql_mode | Create Procedure                               |
+-----------+----------+------------------------------------------------+
| sp2       |          | CREATE PROCEDURE `sp2`()
begin
select 1+1;
end |
+-----------+----------+------------------------------------------------+
1 row in set (0.00 sec)

Then I try to edit the procedure on MySQL Administrator and it is dropped:

win5017>show create procedure test.sp2;
    -> //
ERROR 1305 (42000): PROCEDURE sp2 does not exist
win5017>
[30 Nov 2005 23:25] Miguel Solorzano
Create procedure error message

Attachment: bug15367.PNG (image/x-png, text), 55.43 KiB.