MySQL Bugs: #53467: Incorrect delimiter for forward engineered procedure

Bug #53467	Incorrect delimiter for forward engineered procedure
Submitted:	6 May 2010 14:52	Modified:	28 May 2010 9:42
Reporter:	Ben Breslauer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.17 OSS Beta, Rev 5565	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	DELIMITER, forward engineer, PROCEDURE

Description:
When I export a model with stored procedures via the "forward engineer" process, the forward engineer completes successfully.  However, if I copy the SQL script that is created to a file and then execute it (using a variation of  `mysql < file.sql`) the execution fails.  The SQL that is created is:

------------------
USE `mydb`$$
DROP procedure IF EXISTS `mydb`.`routine1` 
DELIMITER $$
USE `mydb`$$
CREATE PROCEDURE `mydb`.`routine1` (IN id INT)
BEGIN
SELECT * FROM table1 WHERE table1.id = id
;
END

$$
DELIMITER ;
-------------------

The problem is that in the "USE `mydb`$$" line, the delimiter has not yet been set to $$.  It should still be a semicolon.  (Additionally, the DROP line needs a semicolon, but this was fixed in another bug report).  This USE line only occurs if you select the "DROP Objects Before Each CREATE Statement" option.

How to repeat:
1. Create a model and add a stored procedure.
2. Click Database->Forward Engineer
3. Select the "DROP Objects Before Each CREATE Statement" option and click next.
4. Select the "Export MySQL Routine Objects" option sand click next.
5. The SQL code should now appear, and the procedure will have the "USE `mydb`$$" line (or perhaps USE `mydb`//, depending on the alternative delimiter defined in Workbench) before the line "DELIMITER $$"

Suggested fix:
Change the "USE `mydb`$$" line to "USE `mydb`;"

in conjunction with the export options "omit schema qualifier" and "generate use statements" the procedure code is surrounded with multiple delimiter incorrect statements

fix confirmed in repository

An entry has been added to the 5.2.22 changelog:

When a model containing a stored procedure was forward engineered to a script, the script contained invalid SQL code. This happened when the DROP Objects Before Each CREATE Statement and Export MySQL Routine Objects options were selected. Problems that occured with the SQL code included a delimiter being used before its definition and also DROP statements not being terminated.

Bug #54046 has been marked as duplicate of this one

Bug #54090 has been marked as a duplicate of this one