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	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.2.12	OS:	Windows (Windows XL SP3 v.3311)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Drop, Edit, mysql administrator, stored procedure

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.

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.

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