MySQL Bugs: #80475: MYSQL Workbench - 6.3.6 - Syntax Error Preventing SP Deploy

Bug #80475	MYSQL Workbench - 6.3.6 - Syntax Error Preventing SP Deploy
Submitted:	23 Feb 2016 10:32	Modified:	1 Mar 2016 21:29
Reporter:	Antoine Awad	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	6.3.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	6.3.6, SP, syntax_error, workbench

Description:
Hello, 

I updated recently to Workbench 6.3.6 and now I am getting the syntax error for all stored procedures in the Modeling editor with the following syntax: 

BEGIN 
GET DIAGNOSTICS CONDITION 1 
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT; 
END; 

The following words are highlighted in red:
"GET", "DIAGNOSTICS", "code", "RETURNED_SQLSTATE" and "msg". 

I discussed it with Mike, one of the MYSQL Developers and he recommended that I open a bug. 
http://forums.mysql.com/read.php?152,642728,642728#msg-642728

Thank you,
Tony

How to repeat:
When I open my .mwb file, and click on an SP to edit it. The following words are underlined with the syntax error:

"GET", "DIAGNOSTICS", "code", "RETURNED_SQLSTATE" and "msg" .

The problem is the stored proc gets saved with a _SYNTAX_ERR appended to the name and when I try to sync with my server it fails cause of that. 

It's like a ripple effect that starts with the syntax error.

Thank you for the bug report. Are you able to provide the project model file attaching it here using the Files tab?. Thanks.

I am sorry I can't attach my model file. Is the below enough ?

Thanks.

CREATE DEFINER=`user_name`@`%` PROCEDURE `SP_name`(
							IN parm1 int
)
    COMMENT 'this is a comment'
BEGIN
	-- Declare variables to hold diagnostics area information
	DECLARE code CHAR(5) DEFAULT '00000';
	DECLARE msg TEXT;
	DECLARE rows INT;
	DECLARE result TEXT;
    DECLARE rc int;
    DECLARE rowsaffected int;
    
	-- Declare exception handler for failed insert
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;
	
    SELECT * FROM TABLE
    
       -- Check whether the insert was successful
	IF code = '00000' THEN
		GET DIAGNOSTICS rows = ROW_COUNT;
                SET rc = code;
		SET result = CONCAT('Insert Succeeded, Row Count = ',rows);
                SET rowsaffected = rows;
	ELSE
		set rc = code;
		SET result = CONCAT('Insert Failed, Error = ',code,', Message = ',msg);
                SET rowsaffected = rows;
	END IF;
	
	SELECT rc,rowsaffected,result;
END

Thank you for the feedback. I couldn't repeat with my own model file using the SP sample you provided on Windows 10. You don't need to provide the original file but if you are able to create a model file project test case I would appreciate. Thanks.

Sample_Syntax_Error_Model

Attachment: sample.mwb (application/octet-stream, text), 39.17 KiB.

Screenshot_of_error

Attachment: Screen Shot 2016-02-24 at 12.21.16 PM.png (image/png, text), 296.16 KiB.

Hi Miguel,

I attached a sample mwb and a screenshot. Please let me know if you need more on this.

Thanks,
Tony

Note: I am using MYSQL Workbench on OS X EL Capitan Version 10.11.3. Not sure if that makes a difference.

Thanks again.

Thank you for the feedback.

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

No syntax error if correctly used. As I wrote in the forum, if you set a correct model version the syntax error disappears.

Attachment: bug80475.png (image/png, text), 183.85 KiB.

Yes. You are right when I set it to 5.6.4 the syntax error disappears. 

Is this something that is going to be addressed in the next version as well ?

Thanks Miguel for your help.
Tony