Bug #70313 Missing delimiter ; after routine in Workbench forward engeneer

• Submitted: 12 Sep 2013 10:50
• Modified: 8 Nov 2013 9:41
• Reporter: Adam Pilorz
• Status: Closed
• Category: MySQL Workbench: Modeling
• Severity: S3 (Non-critical)
• Version: 6.0.7.11215
• OS: Windows
• CPU Architecture: Any
• Tags: DELIMITER, routines, workbench

[12 Sep 2013 10:50] Adam Pilorz

Description:
When I try to export a MySQL model with multiple routines as an SQL CREATE Script, and I select fields:
- Generate DROP statement before each CREATE statement
- Skip creation of Foreign Keys
- Omit schema qualifier in object names
and left all other blank
I get the code as following (I tried to keep the code as simple as possible, but to repeat the bug - there need to be at least 2 routines):

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,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Table `table1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `table1` ;

CREATE TABLE IF NOT EXISTS `table1` (
  `icolumn` INT NOT NULL AUTO_INCREMENT,
  `num` INT NOT NULL,
  PRIMARY KEY (`icolumn`))
ENGINE = InnoDB;

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

DELIMITER $$
CREATE PROCEDURE `routine1` ()
BEGIN
	INSERT INTO `table1` ( `num` ) VALUES( ROUND( RAND() * 1000 ) );
END$$

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

DELIMITER $$
CREATE PROCEDURE `routine2` ()
BEGIN
	INSERT INTO `table1` ( `num` ) VALUES( ROUND( RAND() * 1000 ) + 1000 );
END$$

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This code produces an error while trying to put into SQL Server (DELIMITER is still set to $$, while the statement "DROP procedure IF EXISTS `routine2`" ends with ;. There should be 'DELIMITER ;' after the previous 'END$$'

How to repeat:
I'll upload the mwb file, which reproduces the error

[12 Sep 2013 10:51] Adam Pilorz

Test case which reproduces the error

Attachment: test_case.mwb (application/octet-stream, text), 5.56 KiB.

[12 Sep 2013 12:32] MySQL Verification Team

Thank you for the bug report.

[18 Sep 2013 11:05] MySQL Verification Team

http://bugs.mysql.com/bug.php?id=70374 duplicate of this one.

[31 Oct 2013 15:57] Armando Lopez Valencia

Posted by developer:
 
FIXED.
Verified in:
Windows 7x64
Ubuntu 12.04x64
MySQL Server 5.6
WB 6.0.8.11345
Blocked by: http://vitro49.no.oracle.com/index.php?r=workitem/view&id=787

[8 Nov 2013 9:41] Philip Olson

Fixed as of MySQL Workbench 6.0.8, and here's the changelog entry:

When exporting a model with multiple routines as an SQL Script, under some
circumstances a deliminator was missing after a routine in the generated
SQL.

Thank you for the bug report.