MySQL Bugs: #79975: GET DIAGNOSTICS is Syntax error: missing 'colon' in PROCEDURE

Bug #79975	GET DIAGNOSTICS is Syntax error: missing 'colon' in PROCEDURE
Submitted:	14 Jan 2016 3:28	Modified:	11 Jul 2016 11:56
Reporter:	taka taka	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	6.3.6	OS:	Windows (Microsoft Windows 8.1 Pro)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
In the procedure, "Syntax error: missing 'colon'" occurred.

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
      SELECT STATUS_FAIL AS STATUS, code AS CODE, msg AS MSG;
    END;

How to repeat:
MySQL Model -> Model Overview -> Add Rutine

CREATE PROCEDURE do_insert
https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html

Hello taka taka,

Thank you for the report.
I didn't notice this issue with Modeling but with SQL Editor.
Observed syntax issue when routine was not wrapped inside DELIMITER.
With DELIMITER, this issue is not observed in SQL Editor.

Thanks,
Umesh

Screenshot..

Attachment: 79975.png (image/png, text), 54.51 KiB.

Strange verification...

In an SQL editor you need of course a DELIMITER command to make things work. The editor must cope with multiple statements so it needs a good delimiter. This is not necessary however in the routine editor where you can have only one statement (the CREATE .. statement).

The code given by the OP is invalid by its own. It is only allowed as part of a create routine statement.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am having the same issue as described with Workbench 6.3.4.0 build 829 32-bit.

Here is a stored procedure with the issue.

CREATE DEFINER=`root`@`localhost` PROCEDURE `Role_RemoveLineage_Delete`(
	_roleId BIGINT
)
BEGIN
	DECLARE _msg VARCHAR(128);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION 
	BEGIN
		ROLLBACK;
        GET DIAGNOSTICS CONDITION 1 @_errorText = MESSAGE_TEXT;
		SET _msg = LEFT(CONCAT('Delete failed for roleId (', _roleId, '): ', @_errorText), 128);
		SIGNAL SQLSTATE '43000' SET MESSAGE_TEXT = _msg;
	END;

	START TRANSACTION;
...
	COMMIT;
END

I'm not seeing this issue with 6.3.7.
If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

this bug still repro on work bench 6.3.7 windows 64. but error message changed as:" Syntax error: unexpected 'GET' (identifier) "

screen shot of error

Attachment: get error.png (image/png, text), 15.87 KiB.

How to repeat error - Create PROCEDURE in MySQL Workbench: Modeling with keyword GET DIAGNOSTICS. look at screen.

Attachment: MySQLWB.6.3.8.1228.64.png (image/png, text), 106.34 KiB.

This error was not fixed in Version 6.3.8 build 1228 CE (64 bit)
look at screen above

Affects me too!!!