MySQL Bugs: #38532: MySQL Administrator - Stored Procedure dropped from database if edit fails.

Bug #38532	MySQL Administrator - Stored Procedure dropped from database if edit fails.
Submitted:	3 Aug 2008 0:54	Modified:	8 Oct 2008 13:50
Reporter:	Bryan Allo	Email Updates:
Status:	Duplicate
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.2.12	OS:	Microsoft Windows (Windows XL SP3 v.3311)
Assigned to:	Mike Lischke	Target Version:
Tags:	mysql administrator, stored procedure, Edit, Drop
Triage:	D2 (Serious)

Description:
MySQL Administrator is droping stored procedures alltogether while editing. While trying
to save changes with syntax error, instead of reverting to the last correct/working
stored procedure, it is dropping everything!

How to repeat:
PROBLEM REPLICATION:
- Launch MySQL Administrator
- Under Catalogs, select "Stored Procedures" tab
- Double-click any stored procedure to edit it (must have an existing stored procedure)
- While in stored procedure edit dialog, modify any part of the SQL code so as to set-off
a "SQL Syntax Error" when you click the "Execute SQL" button.
- The "execution error" dialog will pop-up
- Click OK
- Now back in the edit screen, click the "Cancel" button

MYSQL ADMINISTRATOR has just Dropped your stored procedure, even though your updates were
never accepted due to syntax error.

- Doubleclick on the stored procedure again and it pulls-up a blank page.

Suggested fix:
OBSERVATION
- It seems while editing a stored procedure, when you click the "Execute SQL" button,
MySQL Administrator deletes/drops the entire stored procedure. This means that if your
edits had a syntax error, your original working stored procedure is now GONE!!! Even
though your changes were never saved.

This process flow is WRONG! You cannot replace/drop/delete/remove the existing stored
procedure from the system tables if the edits never passed syntax check in the first
place.

I suggest:

1) MySQL Admin creates a "temporary stored procedure" in the routines table and only
overwrite the original stored procedure if there are no syntax errors.

OR

2) MySQL Admin creates a back-up copy of the original stored procedure before dropping
it. This way if there is syntax error, it automatically reverts to the old stored
procedure that had correct syntax.

OR

Setup a trigger on routines table to automatically back-up a stored procedure before it
is updated, reverting to the back-up if the procedure is blank. This approach is messy
but would stop the problem none-the-less.

I realize the root of this problem may be in the way MySQL Server (not MySQL
Administrator) handles stored procedure updates. None-the less I believe this can be
solved in the MySQL Administrator interface; by copying/cache-ing the stored procedure in
memory everytime you open it for editing... then when you get the SQL execution error,
MySQL Administrator automatically re-executes the cached procedure. That way nothing is
lost as the procedure automatically reverts to it's last stable/syntactically-correct
state (of there is such a word). This step should definitely happen if the user clicks
the "cancel" button after a SQL execution error while trying to save any changes.

Thank you for a bug report. Verified just as described.

Duplicate of Bug #37546