MySQL Bugs: #51921: Loss of stored procedure

Bug #51921	Loss of stored procedure
Submitted:	10 Mar 2010 16:42	Modified:	21 Apr 2010 10:51
Reporter:	Martin Pirringer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.16, 5.2.17	OS:	Windows (7)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	PROCEDURE, Saving

Description:
In queryediter when "X" ing out of the window SP will be lost if there was an error if one hits "Yes" for "Save"

Also happens from modeling but is less critical as you have the SP in the model. Editing the SP is possible in the query editor so it should function in a way that does not result in the loss of the SP/Trigger

How to repeat:
1.) Create any stored procedure
2.) In Query Editor Right click on the procedure and click "Alter Procedure"
3.) Change something that will cause an error (Like leave the ; off at the end of a command
4.) Close the window by hitting the "X" in the top right corner
5.) Click "Save" to save the changes.

You will get a message something like "An error occured ... check MYSQ documentation etc etc.

The problem is that the program sends
DROP....
CREATe.....

So the procedure is dropped and the create fails so it is gone - hope you got a backup

Suggested fix:

A.) Include error checking and prevent save on error with "Cannot save you have an error in line ...." This should be possible as you get a little x on the line in many cases (in a SP many lines long sometimes tough to find)

B.) In case the "Create" fails recreate the SP from the backup. (Save a copy when opening the edit window

Thank you for the problem report. Indeed, as we have a local parser in Workbench, we should process this case in a more safe way.

1) Added warning before trying to apply SQL containing syntax errors.
2) In case of a failure when applying changes to an object, including semantic errors (SQL syntax is ok but name collision happen or something), editor will try to restore the object by running original create statement.

fixed in repository

An entry has been added to the 5.2.18 changelog:

In the SQL Editor, if in the Alter Routine dialog, the user had a routine that contained an error, the routine would be lost if an attempt was made to close the Alter Routine dialog using the close button, and then click Yes to apply changes.