MySQL Bugs: #39929: Forward Engineer SQL Create Script generates code with errors for procedures

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

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

Thank you for the bug report.

Also in 5.1

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.

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.";

Bug #43235 is marked as duplicate of this bug here.

Bug #48028 was marked as duplicate of this one

Patch to remove duplicate database name from generated source.

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

Fixed in 5.2

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.