MySQL Bugs: #53887: alter routines with a not allowed sql command, the modifications lost

Bug #53887	alter routines with a not allowed sql command, the modifications lost
Submitted:	21 May 2010 14:10	Modified:	11 Jun 2010 15:33
Reporter:	Csaba Bukor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.21, 5.2.22	OS:	Windows (XP SP3)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
If I alter a routine and modify it without any SQL syntax error, but with a command which is not allowed in routines (for example: lock table) after the apply error message, all of my modifications lost!

How to repeat:
If I alter a routine and modify it without any SQL syntax error, but with a command which is not allowed in routines (for example: lock table) after the apply error message, all of my modifications lost!

Please, give some more details. For me, say, lock tables t1 is reported as a syntax error by Workbench even before apply. Also after the problem with apply I still see original text of my routine and can edit it further to fix the error.

So, please, upload problematic routine to create, then describe what exact edits should be done on it.

First create a routine:

DELIMITER $$
USE `dev_scriptor`$$
DROP procedure IF EXISTS `new_routine`$$
USE `dev_scriptor`$$
CREATE DEFINER=`root`@`%` PROCEDURE `new_routine`()
BEGIN
END;
;
$$
DELIMITER ;

Then alter it and add a line:
lock table t1 write;

There is no any kind of syntax error at all, and when I apply it, the added line is disappeared. As I remembered we had the same problem with recursive calls untill we don't enable the database's recursive level.

I've got error message:

ERROR 1314: LOCK is not allowed in stored procedures

SQL Statement:
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`()
BEGIN
 lock table t1 write;
END
;

When tried to Apply. What's wrong with this? Same result in mysql command line client:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.89-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter $$
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `new_routine`()
    -> BEGIN
    ->  lock table t1 write;
    -> END
    -> ;
    -> $$
ERROR 1314 (0A000): LOCK is not allowed in stored procedures

Maybe you want LOCK TABLE t1 WRITE to remain in routines code?

Sorry for the late reply. So yes You get the error message, but when you ALTER it, you lost all the modifications. 
-So first create a routine without any error. 
-Alter it and add the " lock table t1 write;" line and you don't get any syntax error. 
-Press "apply" and "apply sql". You got the error message. 
-Press "finish" and all the modifications lost in the routine and you get back the original routine.

Thank you for the feedback.

Verified as described. To see the problem add after LOCK TABLE ... several statements. You will loose all them after trying to create such a procedure. Very annoying.

fix confirmed in database.

An entry has been added to the 5.2.23 changelog:

When carrying out an Alter Routine operation in the SQL Editor, if a routine was modified, and a statement used that, although syntactically correct, was not allowed in a routine, then when changes were applied, all modifications were lost.

Also see bug 54124