MySQL Bugs: #51186: Forward engineering Routines duplicates schemas

Bug #51186	Forward engineering Routines duplicates schemas
Submitted:	15 Feb 2010 12:56	Modified:	18 Mar 2010 11:47
Reporter:	Rafez Noorullah	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.15 OSS Beta Rev 5053, 5.2.16	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	forward engineering, Omit Schema Qualifier, routines, stored procedures

Description:
When creating a new schema, with a new stored procedure (routine), forward engineering results in duplication of the name of the schema.

This causes the forward engineering script to error on the MySQL server.

DELIMITER //
USE mydb//
mydb// -- This is a duplicate/errorenous line
DROP procedure IF EXISTS `mydb`.`p1` //
SHOW WARNINGS//
USE mydb//
mydb// -- This is a duplicate/errorenous line
CREATE PROCEDURE p1 ( ) SELECT *  FROM table1; //
SHOW WARNINGS//

If "Omit schema qualifier in object names" is checked on then both the Use "schema" and the "schema" line disappear as you would expect, but unfortunately, then the context of the SQL statements also disappears.

How to repeat:
In MySQL Workbench, EER mode

New Schema

Add table (t1)

Add Routine Group

Add following stored procedure
CREATE PROCEDURE p1 ( ) SELECT *  FROM t1; //

Save

Forward engineer with "Omit schema qualifier in object names" turned off.

View the SQL statements about to be sent, and you should see the duplicate/errorenous lines within the statements for the stored procedures.

Suggested fix:
Either turn on "Omit schema qualifier in object names" and then manually type in the name of the schema at the top of the SQL statements, before sending the SQL to the server

or

Turn off "Omit schema qualifier in object names" and then manually remove the duplicate/errorenous lines

Please, check with a newer version, 5.2.15. 

For me (on Mac OS X) it generates something like:

-- -----------------------------------------------------
-- procedure p1
-- -----------------------------------------------------

DELIMITER $$
DELIMITER //
USE `mydb`$$
CREATE PROCEDURE p1 ( ) SELECT *  FROM t1; 
//
DELIMITER ;
;
$$

DELIMITER ;

when "Omit Schema Qualifier..." is checked, and this is better while still not ideal. With "Omit Schema Qualifier..." unchecked SQL generated is just correct.

Using 5.2.15 OSS Beta Rev 5053 on Windows XP Pro.
"Omit Schema Qualifier..." not checked
-- -----------------------------------------------------
-- procedure p1
-- -----------------------------------------------------
DELIMITER $$
DROP procedure IF EXISTS `mydb`.`p1` $$
SHOW WARNINGS$$
CREATE PROCEDURE p1 ( ) SELECT *  FROM t1; $$
SHOW WARNINGS$$
DELIMITER ;

But, unsurprisingly, get the following error as create procedure p1 not qualified by schema, and no default schema (no Use schema at top)

Executing SQL script in server
ERROR: Error 1046: No database selected
CREATE PROCEDURE p1 ( ) SELECT *  FROM t1; 
SQL script execution finished: statements: 12 succeeded, 1 failed

"Omit Schema Qualifier..." checked
-- -----------------------------------------------------
-- procedure p1
-- -----------------------------------------------------
DELIMITER $$
USE `mydb`$$
DROP procedure IF EXISTS `p1` $$
SHOW WARNINGS$$
DELIMITER //
USE `mydb`$$
CREATE PROCEDURE p1 ( ) SELECT *  FROM t1; 
//
DELIMITER ;
;
$$
SHOW WARNINGS$$
DELIMITER ;

Which is obviously now placing schemas in the SQL, whereas it is excluded from other objects.

Verified just as described in your last comment with 5.2.16.

Unfortunately, I am not able to follow you.

Do you agree with me that this is not a bug or did I misunderstood you?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".