MySQL Bugs: #63742: Missing DELIMITER when using "Omit Schema Qualifier in Object Names"

Bug #63742	Missing DELIMITER when using "Omit Schema Qualifier in Object Names"
Submitted:	14 Dec 2011 8:24	Modified:	22 Jun 2012 0:48
Reporter:	Glenn Larsen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.36	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	DELIMITER, Omit Schema Qualifier

Description:
When using "Export" -> "Forward Engineer SQL CREATE script", and you select "Omit Schema Qualifier in Object Names", all routines will be saved without the delimiter option in the SQL.

How to repeat:
Create new model
Create a routine
Select "Export", "Forward Engineer SQL CREATE script", and check the option "Omit Schema Qualifier in Object Names"

I do not see this problem on Windows XP.

I couldn't repeat on Ubuntu 11.04 too. Which Linux OS are you using and have you intalled our binary package or yourself compiled one?. Thanks.

Hi,

I'm running Fedora release 16, I installed the RPM package: mysql-workbench-gpl-5.2.36-1fc15.x86_64 from your download page..

G

Please, send some .mwb file that demonstrates the problem in your environment.

Workbench model

Attachment: b1.mwb (application/octet-stream, text), 3.39 KiB.

Video of the problem...

Attachment: export.h264 (application/octet-stream, text), 437.53 KiB.

Thank you for the feedback. Verified with the provided model on Windows 7.

Verifying this bug occurs on Windows XP SP3, and a hacky workaround / fake fix.

Context:

MySql Workbench v. 5.2.37 CE Revision 8576
MySql version 5.5.15-log
Windows XP SP3

Intermittent issue:

When forward engineering or synchronizing a model, checking the option "Omit Schema Qualifier in object names" results in all DELIMITER's being removed from the generated SQL script.

This breaks any stored procedures added to the model when creating an instance of it on the server.

When this bug is active, toggling the check mark on the schema qualifier option will toggle the DELIMITER statements. By deselecting all options during the forward engineer operation, then proceeding with the forward engineering op., the bug seems to be suppressed. I don't know what triggers the bug to activate, or how long suppression will last. Once the bug is suppressed, toggling the schema qualifier option does not affect DELIMITER's

* Excerpts from generated SQL (bug active):

** Schema Qualifier option is NOT checked (stored procedures will work):

-- -----------------------------------------------------
-- procedure getBookContents
-- -----------------------------------------------------

USE `default_schema`;
DROP procedure IF EXISTS `default_schema`.`getBookContents`;
SHOW WARNINGS;

DELIMITER $$
USE `default_schema`$$
CREATE PROCEDURE `default_schema`.`getBookContents` (bookId INT)
BEGIN
SELECT * FROM `book_contents` WHERE `book_id` = bookId;
END

$$

DELIMITER ;
SHOW WARNINGS;

** Schema Qualifier option is checked (stored procedures will not work):

-- -----------------------------------------------------
-- procedure getBookContents
-- -----------------------------------------------------
DROP procedure IF EXISTS `getBookContents`;
SHOW WARNINGS;
CREATE PROCEDURE `getBookContents` (bookId INT)
BEGIN
SELECT * FROM `book_contents` WHERE `book_id` = bookId;
END

$$
SHOW WARNINGS;

Fixed as of 5.2.41, and here's the changelog entry:

Using "File", "Export", "Forward Engineer SQL CREATE Script" would 
remove delimiters statements if the "Omit Schema Qualifier in Object
Names" option was selected.

I still see this bug in 5.2.46

It wasn't fixed completely, the 'DELIMITER ;' after routine body is still missing.

Using: MySQL Workbench 5.2.46, Windows Vista

Steps to simulate the bug:
1) Forward engineer
2) check "DROP Objects...", "Omit schema qualifier...", "Add SHOW WARNINGS..."
3) Next
4) check "Export routines"
5) Next

And the result is this:

-- -----------------------------------------------------
-- procedure routine1
-- -----------------------------------------------------
DROP procedure IF EXISTS `routine1`;
SHOW WARNINGS;

DELIMITER $$
CREATE PROCEDURE `routine1` ()
BEGIN
	select 1, 2, 3;
END
$$
-- !!! --->>> there should be "DELIMITER ;" but it is missing <<<--- !!!
SHOW WARNINGS;

-- -----------------------------------------------------
-- procedure routine2
-- -----------------------------------------------------
DROP procedure IF EXISTS `routine2`;
SHOW WARNINGS;

DELIMITER $$
CREATE PROCEDURE `routine2` ()
BEGIN
	set @dummyVar := 'Hello';
END
$$
-- !!! --->>> there should be "DELIMITER ;" but it is missing <<<--- !!!
SHOW WARNINGS;

If you use the same steps, but in step 2) you DO NOT check "Omit schema qualifier...", you get this:

-- -----------------------------------------------------
-- procedure routine1
-- -----------------------------------------------------

USE `mydb`;
DROP procedure IF EXISTS `mydb`.`routine1`;
SHOW WARNINGS;

DELIMITER $$
USE `mydb`$$
CREATE PROCEDURE `routine1` ()
BEGIN
	select 1, 2, 3;
END
$$

DELIMITER ; -- --->>> now the DELIMITER is correctly present <<<---
SHOW WARNINGS;

-- -----------------------------------------------------
-- procedure routine2
-- -----------------------------------------------------

USE `mydb`;
DROP procedure IF EXISTS `mydb`.`routine2`;
SHOW WARNINGS;

DELIMITER $$
USE `mydb`$$
CREATE PROCEDURE `routine2` ()
BEGIN
	set @dummyVar := 'Hello';
END
$$

DELIMITER ; -- --->>> now the DELIMITER is correctly present <<<---
SHOW WARNINGS;

I tested this also with WB 5.2.42 and 5.2.47 on the same OS and the result is the same - DELIMITER ; after routine body is not present if "Omit schema qualifier" is checked.

I also tested this with WB 5.2.40 revision 8790 and if using the same steps as mentioned, result is correct:

-- -----------------------------------------------------
-- procedure routine1
-- -----------------------------------------------------
DROP procedure IF EXISTS `routine1`;
SHOW WARNINGS;
DELIMITER $$
CREATE PROCEDURE `routine1` ()
BEGIN
	select 1, 2, 3;
END
$$

$$ -- this is useless, but it does not spoil the generated script
DELIMITER ;

SHOW WARNINGS;

-- -----------------------------------------------------
-- procedure routine2
-- -----------------------------------------------------
DROP procedure IF EXISTS `routine2`;
SHOW WARNINGS;
DELIMITER $$
CREATE PROCEDURE `routine2` ()
BEGIN
	set @dummyVar := 'Hello';
END
$$

$$
DELIMITER ;

SHOW WARNINGS;

Please take a look at it.
Thanks.

This bug is still present in 5.2.47 - any news on this issue? How to reopen this bug?

To be precise: the bug is still available in 6.0.7.11215