MySQL Bugs: #46268: Option to "Omit schema qualifier in object names" has no effect

Bug #46268	Option to "Omit schema qualifier in object names" has no effect
Submitted:	17 Jul 2009 17:29	Modified:	8 Feb 2010 15:32
Reporter:	Craig Fowler	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.1.16	OS:	Linux (Kubuntu Jaunty using .deb installer)
Assigned to:	Alexander Musienko	CPU Architecture:	Any

Description:
The option seems to have no effect, if I understand what it should do correctly.  After choosing the option and continuing through the export wizard, the "preview of the SQL that will be created" continues to show me the schema name in the generated SQL.

I'm assuming that meaning of the option is so that you are not tied to a schema/database name when exporting a CREATE script.

How to repeat:
Create or import a project, set a schema name.  Click:

File » Export » Forward Engineer CREATE Script

Tick the "Omit chema qualifier in object names" option (in this case I also had the "Generate DROP statements before each CREATE statement" option selected).

Proceed through the wizard to the "Review SQL Script" page.

The schema name is still in the generated SQL, for example:

USE `my_database`;
IF NOT EXISTS DROP TABLE `my_database`.`my_table`;

Thank you for the problem report. Please, try to repeat with a newer version, 5.1.16, and inform about the results.

Confirming that it still exists in 5.1.16 (wow, 5.1.16 starts up a hell of a lot faster than 5.1.13 - good work!)

Also - some more info (because I'm still not 100% certain that I have understood the feature properly).  I assume the feature is so that the creation script can be imported into any schema, instead of the one that was defined in Workbench.

Below are some 'with' and 'without' pastes from the beginning of the created .sql script. I noticed that in the CREATE TABLE statement the schema name is omitted.  If I understand mysql though, it still isn't enough to allow me to import it into any schema of my choice:

In short the schema name is still present in:
* The USE directive
* The comments
* The DROP TABLE directives
* The CREATE SCHEMA directive

I think that these references to the original schema name are enough to interfere with importing the structure into a different, arbitrarily-named schema.

WITH 'Omit schema qualifier in object names' ticked
===================================================

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

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

-- -----------------------------------------------------
-- Table `royalties`.`record_company`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `royalties`.`record_company` ;

CREATE  TABLE IF NOT EXISTS `record_company` (
  `record_company_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `long_name` VARCHAR(50) NULL DEFAULT NULL,
  `short_name` VARCHAR(20) NOT NULL,
  `rounding_digits` INT(2) NOT NULL,
  PRIMARY KEY (`record_company_id`) ,
  UNIQUE INDEX `short_name` (`short_name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

WITHOUT 'Omit schema qualifier in object names' ticked
======================================================

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

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

-- -----------------------------------------------------
-- Table `royalties`.`record_company`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `royalties`.`record_company` ;

CREATE  TABLE IF NOT EXISTS `royalties`.`record_company` (
  `record_company_id` INT(11) NOT NULL AUTO_INCREMENT ,
  `long_name` VARCHAR(50) NULL DEFAULT NULL,
  `short_name` VARCHAR(20) NOT NULL,
  `rounding_digits` INT(2) NOT NULL,
  PRIMARY KEY (`record_company_id`) ,
  UNIQUE INDEX `short_name` (`short_name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Thank you for the bug report. Verified on Windows Vista with Sakila model sample.

Verified the same problem on Windows XP SP3.

this fix has been committed to the source repository of Workbench 5.1. (it's also included in current development version 5.2)

An entry has been added to the 5.1.19 changelog:

The Omit Schema Qualifier in Object Names option in the Forward Engineer SQL Script wizard appeared to have no effect. After selecting this option and proceeding through the wizard, the generated script still contained schema qualifiers.