MySQL Bugs: #54836: Forward engineering model fails with routines

Bug #54836	Forward engineering model fails with routines
Submitted:	27 Jun 2010 11:26	Modified:	7 Mar 2011 15:05
Reporter:	Karsten Wutzke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.24 RC	OS:	Windows (7)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	forward engineer, routines

Description:
I have a model with nearly 40 tables. One of them contains a routine and two triggers. I regularly get an error when forward engineering that model:

Executing SQL script in server

ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$

DELIMITER $$

CREATE FUNCTION generate_team_type_code(gender VARCHAR(6), rel' at line 4

-- -----------------------------------------------------
-- function generate_team_type_code
-- -----------------------------------------------------

DROP function IF EXISTS `generate_team_type_code`$$
DELIMITER $$

CREATE FUNCTION generate_team_type_code(gender VARCHAR(6), relation VARCHAR(6), age SMALLINT)

RETURNS CHAR(4)

BEGIN
  DECLARE gender_char CHAR(1) DEFAULT '_'

SQL script execution finished: statements: 133 succeeded, 1 failed

How to repeat:
Use the forward engineering options:

1. DROP Objects Before Each CREATE Object (checked!)
2. Omit Schema Qualifier in Object Names (checked!)
3. Generate Separate CREATE INDEX Statements (checked!)
4. Generate INSERT Statements for Tables (checked!)

*** If you leave away the first option it will complete without errors ***

Verified just as described with your .mwb file uploaded. This is where the problem starts:

...
CREATE INDEX `teams_teamtypes_fk` ON `Teams` (`teamtype_code` ASC) ;

-- -----------------------------------------------------
-- function generate_team_type_code
-- -----------------------------------------------------
DROP function IF EXISTS `generate_team_type_code`$$
...

Here $$ is used before it is declared as DELIMITER.

I can confirm that this is a problem with the Mac version as well.  It seems that without 'Drop Objects' checked it will not put a 'DELIMITER $$' at the beginning of the routine definitions.  Even with 'Drop Object' the '$$' delimiter is not put at the end of each routine's definition.  Also, the last routine doesn't get an EOL after the final 'END' and the delimiter is not reset to ';'.  In my case I end up with 'ENDUSE `dbname`;' which fails.  Adding the delimiter in the editor doesn't help because it is stripped off when saved.

A related problem is it seems to collect Carriage Return characters (^M) as I cut-n-paste.

If I edit the generated SQL when it is shown just before execution and add the '$$' delimiter and fix the last routine's last line, then the script execute successfully.  This is a quick scrip I made I call fixWBsql.bsh which tries to do the editing for me.  (Replace the '^M' with real Carriage Returns.)

#!/bin/bash
cat "$1" | sed '/^$/N;/\n$/N;//D;
                s/^ *ENDUSE .*$/END;$$/g;
                s/^ *END$/END;/g;
                s/^ *END;$/END;$$/g;
                s/^DROP VIEW/DELIMITER ;^MDROP VIEW/' | \
           tr "^M" "\n"

the original problem is fixed in repository, but there's still some delimiter mixup going on to be cleaned up.

Bug #56784	has been marked as duplicate

fix confirmed in repository

An entry has been added to the 5.2.32 changelog: 

When forward engineering a model, the generated script resulted in SQL errors 
when executed on the server. This happened when the following options were 
selected: 

DROP Objects Before Each CREATE Object 
Omit Schema Qualifier in Object Names 
Generate Separate CREATE INDEX Statements 
Generate INSERT Statements for Tables