Bug #51102 Wrong placement of objects when reverse engineering from Script in Schema "MYDB"
Submitted: 11 Feb 2010 14:24 Modified: 14 Jun 2013 0:03
Reporter: Martin Pirringer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:5.2.15, 5.2.16 OS:Any (Windows 7, Linux)
Assigned to: Assigned Account CPU Architecture:Any
Tags: MYDB, Object Placement

[11 Feb 2010 14:24] Martin Pirringer
Description:
If a procedure or table was created with 
your
CREATE PROCEDURE `yourproc`

Instead of 

CREATE PROCEDURE `yourdb`.`yourproc`

then a reverse engingeer will put that proc into the Schema "mydb" which should not be there in the first place. When creating procedures with mysqladmin 5.0 when being prompted for a name the Schema was not automatically added to the procedure name. So stored proc are attached to the shema, work fine but do not have the syntax schema.procname as there name.

How to repeat:
To quickly simulate this problem

1.) reverse engineer the mysql schema
2.) Add a routine with anything in it but delete the 'Mysql' part in the name so it reads something like this

CREATE PROCEDURE `routine1` ()

BEGIN
SELECT * FROM event;
END//
3.) Forward engineer it but stop at the point where it shows the SQL statements and click "Save to File" and save it to "test.sql". 

The preceeding steps are to simulate a schema with an object created under 5.0 with the `schema`. part ommited in the name

4. IN WB click "Create EER diagram from Script
5. When prompted for a name select "test.sql"
6. You will have a model with a mysql and a mydb schema. your Routine1 will have been reverse engineered into the "Mydb" schema

Suggested fix:
1.) When reverse engineering only Schemas in the script or selected from the Server should be included
2.) When a script starst with something like
CREATE SCHEMA IF NOT EXISTS `mysql` DEFAULT CHARACTER SET latin1 ;
Then all subsequent items should belong to that schema except when explicitly declared otherwise.
[19 Feb 2010 11:39] Susanne Ebrecht
Verified as described.

CREATE SCHEMA bug51102;
USE bug51102;
CREATE TABLE t(i integer);

Open Workbench:
Reverse engineer bug51102;

Click onto the schema tab.

Add a routine.

DELIMITER //

CREATE PROCEDURE `routine1` ()
BEGIN
select * from t;
END//

Sync database ...

In sync script:

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `mydb`;

DELIMITER $$

CREATE PROCEDURE `routine1` ()
BEGIN
select * from t;
END$$

DELIMITER ;
[14 Apr 2010 8:02] Sergei Tkachenko
Can't repeat this with 5.2.18.
[23 Apr 2010 10:55] Sergei Tkachenko
Reverse engineering places all unspecified objects into default schema, but it changes default schema correspondingly when encounters "use" statement in the passed script, while "create schema" statement doesn't change default schema (this behavior is equal to MySql CLI-client). Initial schema is determined by selected schema tab page in a physical schemata overview panel by the time of invoking reverse-engineering.

Need Alex's input here.
My proposal is to make forward-engineering generate "use" statement right after "create schema" statement.
[23 Apr 2010 11:02] Sergei Tkachenko
Martin, in your case you just need to create target schema manually in your model, if it doesn't exist yet, and select it before starting reverse-engineering.
[14 Jun 2013 0:03] Philip Olson
Fixed as of the upcoming MySQL Workbench 6.0.2 public beta release, and here's the changelog entry:

The "USE" statement is now being added prior to creating procedures, as
otherwise it was possible that the procedures would be placed in the wrong
schema.

Thank you for the bug report.