Bug #39929 Forward Engineer SQL Create Script generates code with errors for procedures
Submitted: 8 Oct 2008 13:12 Modified: 29 Jun 2010 9:33
Reporter: Gabriele Tozzi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.25, 5.1, 5.1.18 OS:Any
Assigned to: Mike Lischke CPU Architecture:Any
Tags: create, performance, PROCEDURE

[8 Oct 2008 13:12] Gabriele Tozzi
Description:
Hello,

when i insert one or more stored procedure(s) on my project and then I use the "generate SQL create script" feature, workbench inserts a blank query (//) after every inserted procedure. This blank query generates a very noisy "No query specified" error when running the script.

How to repeat:
- Create a new project on mysql workbench
- Click "Add Routine"
- Insert the following example code on the routine:
  DELIMITER //
  CREATE DEFINER = 'mydefiner'@'localhost'
  PROCEDURE `get_user_list` ()
  SQL SECURITY DEFINER
  COMMENT 'Returns list of users'
  BEGIN
    SELECT `idUsers`, `username`, `nome`, `cognome` FROM `Users`;
  END//
- Now chose File -> Export -> Forward Engineer SQL Create Script

The generated SQL will be something similar to:

DELIMITER //

CREATE DEFINER = 'vf_lowpriv'@'localhost'
PROCEDURE `vf`.`sp_admin_get_user_list` ()
SQL SECURITY DEFINER
COMMENT 'Returns list of users'
BEGIN
  SELECT `idUsers`, `username`, `nome`, `cognome`, `reg_complete` FROM `vf`.`Users`;
END//
//  <---- this is the empty query that generates the error

DELIMITER ;

Suggested fix:
Remove the empty query
[8 Oct 2008 14:36] MySQL Verification Team
Thank you for the bug report.
[19 May 2009 12:24] Susanne Ebrecht
Also in 5.1
[21 Sep 2009 22:26] Gabriele Tozzi
Also on 5.1.18 on Linux.

The generated sql code now includes this two lines before every procedure too:

USE vf//
vf//

'vf' is the name of the DB. First line is not necessary, second one generates one more error.

Empty query (//) after the procedure is still present.
[29 Sep 2009 21:01] Gabriele Tozzi
Here is a workaround bash script to post-process the generated sql file. It also removes the "DROP SCHEMA" statement

#!/bin/bash
if [ ! -f "$1" ]; then
	echo "usage: $0 <filename>";
	exit;
fi

cp -a "$1" "$1.bak";

awk '{
	if (match($0,"^((USE )?(vf)?\/\/|DROP SCHEMA IF EXISTS.*;)$")) {
		print ""
	} else {
		print $0
	}
}' "$1.bak" > "$1";

echo "Done.";
[19 Oct 2009 12:29] Susanne Ebrecht
Bug #43235 is marked as duplicate of this bug here.
[19 Oct 2009 12:34] Johannes Taxacher
Bug #48028 was marked as duplicate of this one
[23 Nov 2009 16:53] Rogelio Jacinto
Patch to remove duplicate database name from generated source.

Attachment: module_db_mysql.cpp.patch (application/octet-stream, text), 400 bytes.

[25 Nov 2009 9:00] Mike Lischke
Fixed in 5.2
[29 Jun 2010 9:33] Tony Bedford
An entry has been added to the 5.2.10 changelog:

When a model containing stored routines was forward engineered to a script, the script contained an erroneous additional delimiter after each stored routine. This resulted in a “No query specified” error when an attempt was made to run the script on a server.