MySQL Bugs: #46505: Forward Engineer SQL.CREATE SCRIPT... Incorrect syntax for Delimiters

Bug #46505	Forward Engineer SQL.CREATE SCRIPT... Incorrect syntax for Delimiters
Submitted:	31 Jul 2009 23:11	Modified:	25 Nov 2009 14:48
Reporter:	R. Carty	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.1.16	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	delimeters, forward engineer, generated, routines, scripts, syntax

Description:
When forward engineer SQL Create script against the Sakila database, and trying to script the Routine Objects... the resulting script contains the following delimeters within the script (//) as shown...;

DELIMITER //
//

CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);
     SELECT FOUND_ROWS() INTO p_film_count;
END //
//

... which is not compatible with Query Browser 1.2.17.. the recomended GUI Tools for MySQL Vers. 5.0 and causes an error when running the script against mySQL V 5.1.36 - Community.

How to repeat:
Select Export>Forward Engineer SQL CREATE SCRIPT... 
Go through the wizard and analyze the resulting script...
Open MySQL Query Browser Vers 1.2.17...
Open the script and run it to create the Salika Database Schema...
Observe the numerous errors.

Bbut... more specific to this bug... the syntax error noted.

You can also remove the above line of code in the description to create the procedure...

Suggested fix:
Generate delimeters that do not indicate empty routines.

OR ... Create a delimeter selection for various versions.

The statement below executes correctly in Query Builder as the '$$' delimeter is recognized.

DELIMITER $$

CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)

READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);
     SELECT FOUND_ROWS() INTO p_film_count;
END

Sakila.sql script that was generated from Workbench OSS 5.1.16

Attachment: sakila.sql (application/octet-stream, text), 23.59 KiB.

Thank you for the bug report. Verified just as described. Indeed, the code generated gives error while running, even in mysql command line client:

mysql> DELIMITER //
mysql> //
ERROR: 
No query specified

mysql> CREATE PROCEDURE `mydb`.`routine1` ()
    -> BEGIN
    ->   select 1;
    -> END//
Query OK, 0 rows affected (0.00 sec)

Also it would be nice to have configurable option for the DELIMITER used.

the problem is the standalone
//
which raises the "no query specified" error
it's not a problem from WB or command line because it doesn't do any harm, but it creates error message which - dependent on the number - can produce a lot of annoying "noise" in the output

Bug #47361 is set as duplicate of this bug here.

Bug #46904 is set as duplicate here.

changed delimiter used for procedures to $$
fix will be included in 5.2.9

An entry was added to the 5.2.9 changelog:

The Forward Engineer SQL Script wizard did not generate correct delimiter syntax for routines. For example, it generated the following code:

DELIMITER //
//

CREATE PROCEDURE `sakila`.`film_not_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT
p_film_count INT)

READS SQL DATA
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND NOT inventory_in_stock(inventory_id);
     SELECT FOUND_ROWS() INTO p_film_count;
END //
//

This resulted in errors when an attempt was made to run the generated script on MySQL Server.