Bug #64812 get diagnostics working in command line but not in stored proc

• Submitted: 30 Mar 2012 10:31
• Modified: 30 Mar 2012 12:38
• Reporter: Edward Boggis-Rolfe
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.6-m7
• OS: Windows (windows 7)
• Assigned to: Jon Olav Hauglid
• CPU Architecture: Any
• Tags: get diagnostics stored proc procedure

[30 Mar 2012 10:31] Edward Boggis-Rolfe

Description:
following Marc blog http://marcalff.blogspot.co.uk/2011/10/mysql-get-diagnostics.html I can successfully call get diagnostics on an error.  

However doing the same inside a stored proc based on the example in  http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html and http://rpbouman.blogspot.co.uk/2006/02/dont-you-need-proper-error-handling.html results in syntax errors.  

Has this been fully implemented and released to work in stored procs yet?

How to repeat:
The parser throws up on the word 'diagnostics'

DELIMITER $$

CREATE PROCEDURE do_insert(value INT)
    begin
        declare exit handler for sqlwarning
            begin
                get diagnostics condition 1
                    set v_sqlstate := RETURNED_SQLSTATE
                    ,   v_message_text := MESSAGE_TEXT
                ;
            end;   

    end;

[30 Mar 2012 12:04] Jon Olav Hauglid

Hello!

Try something like this:

  get diagnostics condition 1
     @v_sqlstate = RETURNED_SQLSTATE, @v_message_text = MESSAGE_TEXT;

Basically, no "set" and "=" instead of ":=".

There are more examples here:
http://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

[30 Mar 2012 12:38] Edward Boggis-Rolfe

Thanks for the fast reply.

I have tried this
DELIMITER $$

CREATE PROCEDURE do_insert(value INT)
   begin
        declare exit handler for sqlwarning
            begin
                get diagnostics condition 1     
                    @v_sqlstate = RETURNED_SQLSTATE, @v_message_text = MESSAGE_TEXT;
            end;   

    end;

and this

CREATE PROCEDURE do_insert(value INT)
   begin
DECLARE __proc_code CHAR(5) DEFAULT '00000';
DECLARE __proc_message TEXT;
        declare exit handler for sqlwarning
            begin
                get diagnostics condition 1     
                    __proc_code = RETURNED_SQLSTATE, __proc_message = MESSAGE_TEXT;
            end;   

    end;

to no avail.

In both cases I get a wavy line in mysql workbench on the word diagnostics with a syntax error.

[2 Apr 2012 8:10] Jon Olav Hauglid

Is this using a 5.6 development release?
GET DIAGNOSTICS is not available in MySQL 5.5.

[14 Nov 2016 21:23] Pankaj Rawat

I'm getting same erro for GET in workbench 6.3

[8 Feb 2017 10:58] Vlad Vlad

This BUG was not fixed in Version 6.3.8 build 1228 CE (64 bit)
look at topic with my screen: Bug #79975
https://bugs.mysql.com/bug.php?id=79975