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:Microsoft Windows (Microsoft Windows 8.1 Pro)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[14 Jan 2016 3:28] taka taka
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
[14 Jan 2016 5:46] Umesh Shastry
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
[14 Jan 2016 5:49] Umesh Shastry
Screenshot..

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

[14 Jan 2016 8:31] Mike Lischke
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.
[15 Feb 2016 1:00] Bugs System
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".
[26 Apr 2016 14:53] Craig Perkinson
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
[11 Jul 2016 11:56] Umesh Shastry
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.
[11 Aug 2016 5:32] Jeffrey Zeng
this bug still repro on work bench 6.3.7 windows 64. but error message changed as:" Syntax error: unexpected 'GET' (identifier) "
[11 Aug 2016 5:36] Jeffrey Zeng
screen shot of error

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

[8 Feb 2017 10:52] Vlad Vlad
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.

[8 Feb 2017 10:54] Vlad Vlad
This error was not fixed in Version 6.3.8 build 1228 CE (64 bit)
look at screen above
[21 Dec 2018 7:41] Szabolcs Szekely
Affects me too!!!