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

[23 Feb 2016 10:32] Antoine Awad
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.
[23 Feb 2016 10:42] MySQL Verification Team
Thank you for the bug report. Are you able to provide the project model file attaching it here using the Files tab?. Thanks.
[23 Feb 2016 10:47] Antoine Awad
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
[23 Feb 2016 17:49] MySQL Verification Team
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.
[24 Feb 2016 17:20] Antoine Awad
Sample_Syntax_Error_Model

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

[24 Feb 2016 17:22] Antoine Awad
Screenshot_of_error

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

[24 Feb 2016 17:23] Antoine Awad
Hi Miguel,

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

Thanks,
Tony
[24 Feb 2016 17:23] Antoine Awad
Note: I am using MYSQL Workbench on OS X EL Capitan Version 10.11.3. Not sure if that makes a difference.

Thanks again.
[24 Feb 2016 22:18] MySQL Verification Team
Thank you for the feedback.
[25 Feb 2016 16:11] MySQL Verification Team
http://bugs.mysql.com/bug.php?id=80517 marked as duplicate of this one.
[1 Mar 2016 16:24] Mike Lischke
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.

[1 Mar 2016 21:29] Antoine Awad
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