MySQL Bugs: #97869: All attempts to use GET within a Routine Error Handler fails

Bug #97869	All attempts to use GET within a Routine Error Handler fails
Submitted:	3 Dec 2019 21:28	Modified:	5 Dec 2019 16:14
Reporter:	David Allen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	8.0.18	OS:	Windows
Assigned to:		CPU Architecture:	x86
Tags:	diagnostics, error, get

Description:
Any attempt to create a routine that has the GET DIAGNOSTICS CONDITION action within a Routine Definition in Workbench fails with the error "GET is not valid at this position". This includes copying (verbatim) the sample code from https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html.

The exact same code WORKS in the Query Window. The error can be seen here:
https://i.stack.imgur.com/QCMpI.png

Every single variation of this fails.

How to repeat:
CREATE PROCEDURE routine1 ()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN 
        GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        SET @full_error = CONCAT('SQL Exception:act_account_main_create:(', @p1, ') ', @p2);
        SELECT @full_error;
    END;
END

Suggested fix:
This must be fixed; right now, I have no detailed error handling capabilities.

I just verified this is a but with the MWB file. I exported everything and imported it into a new MWB and it all works fine. Note the MWB file was originally from a model I created around 2 years ago (I used it as a base template) so this bug seems to only apply to older models - my guess is there's some old setting that's being overlooked.

Sorry for the type - a BUG with the MWB.

Hello David Allen,

Thank you for the report.
I tried to reproduce your issue on windows 10 with workbench 8.0.18 using the procedure provided but I am not seeing any issues at my end.
To investigate further this issue at our end, may I kindly request you to launch workbench under debug mode (--log-level=debug3) and provide unaltered workbench log file(more details about log are explained here - https://dev.mysql.com/doc/workbench/en/workbench-reporting-bugs.html). Thank you.

Regards,
Ashwini Patil

8.0.18 test results

Attachment: 97869_results.png (image/png, text), 376.79 KiB.

You can reproduce the issue by opening the MWB and clicking on Create Routine and try to put the code in there; it'll always fail on the GET.

Hello David Allen,

Thank you for the bug report and feedback.
Verified as described.

Regards,
Ashwini Patil

8.0.18 test results

Attachment: 97869_mwb_results.png (image/png, text), 352.17 KiB.

Posted by developer:
 
This is not a bug. `GET DIAGNOSTICS` is available not before MySQL server 5.6.4. In your model however, you are using version 5.6 (derived from the application settings). Instead use model specific settings (see Menu -> Model -> Model Options... -> MySQL -> Target MySQL Version) and change that version to at least 5.6.4. After confirming the change your procedure will immediately start parsing fine.

Note: you have to enable model specific options by unchecking "Use defaults from global settings" or, alternatively set the target version in the application settings, which will however apply to all operations MySQL Workbench will do from then on (e.g. code generation, versions used for models without a model specific setting etc.).

Ugh. I remember going into the settings to see which version of MySQL was selected and making sure it was the most current, but it was probably the global settings issue you mentioned. Very frustrating. Well, my apologies for wasting your time - but I have to say, the IDE of Workbench doesn't do anything to provide details on what the cause might be (e.g. it would have been nice if the error highlight for the GET said "not supported in this version" rather than a generic error that provided no lead for the real issue). Regardless, thank you for clarifying.