Bug #11660 Expose either SQLState, mysql_error() or other diagnostics in stored procedures
Submitted: 30 Jun 2005 11:53 Modified: 19 Jul 2012 13:34
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Stored Routines Severity:S4 (Feature request)
Version:5.07 OS:Any (NA)
Assigned to: Marc ALFF CPU Architecture:Any
Tags: bfsm_2007_03_01

[30 Jun 2005 11:53] Roland Bouman
In the MySQL stored procedure language, handlers can be written to handle the occurrence of some condition. Typically, handlers are used to handle error conditions. 
Error conditions are conveniently declared by referring to some predefined class of conditions (e.g. NOT FOUND), or by specifying a particular SQLSTATE. Also, a "catch-all-others" condition is predefined (SQLEXCEPTION), in order to handle those errors for which no appropriate handler is in scope.
However, when handling such a general error condition, MySQL stored procedures do not provide any means to discover the nature of the error condition. If one would want to log the occurrence of the error, there is no way of logging the current value of SQLSTATE from within the stored procedure layer. One is forced to do this from the application layer, which seems to be the wrong place to do this.
Other major dbms-es have solved this by exposing the current errorcode and (sometimes message) in a global variable (@@ERROR for MSSQL, SQLCODE and SQLERRM for Oracle). The ANSI Standard also suggests a command to discover the nature of an error condition by using the GET DIAGNOSTICS command (although it is not clear to me in what form the information would become available in the context where diagnostics are requested).

How to repeat:

Suggested fix:
Expose SQLSTATE or something similar (like C API mysql_error()) as a global variable or function from within the context of stored procedures so that we could write:

declare exit handler for sqlexception
    insert into error_table (
    ,   message
    ) values (
[2 Nov 2005 8:33] Th. Schuett
It´s absolutly neccessary, see http://forums.mysql.com/read.php?98,31753,31753#msg-31753
[27 Feb 2006 17:23] Roland Bouman
Thanks Valeriy for picking it up. I really dig mysql version 5, but I'm really going to 
need the improved error management support. 

I will be among those testing the functionality as soon as it is available.

Thanks again!
[15 May 2006 21:16] ROGERIO VILELA DE PAULO
Really, the return errors is necessary for complete control of application.
[13 Jun 2006 12:45] Rick Robinson
Seriously, what has happened to this and 11661?  Are these being worked?  Are they part of an upcoming 5.0.x or 5.1.x release?  If so, when?

Not having this capability makes stored procedure support in MySQL marginal, at best.  Please don't forget the basics like this as you move forward with all the nifty partitioning and other features.  It's the basics on which the reputation was built.

[7 Sep 2006 13:55] Valeriy Kravchuk
Bug #16371 was marked as a duplicate of this one.
[14 Sep 2006 11:06] Valeriy Kravchuk
Bug #10797 was marked as a duplicate of this one.
[19 Oct 2006 18:53] Marc Grue
Urgently needing this feature! Hope this feature will make it into 5.2!!

Aside from the suggested @@SQLSTATE and @@SQLSTATE_MESSAGE, another global @@MYSQLERROR (the MySql error number) would be highly appreciated with it's more detailed mysql-specific error mapping.
[17 Nov 2006 23:29] Dejan Topalovic
I appreciate your effort and I'll appreciate it even more, if you could implement something like SQLERRM and SQLCODE in Oracle...
That thingie would be really helpful!
[14 Mar 2007 9:11] Peter Schwager
This is by far the most SERIOUS weak point of MYSQL from my point of view. We have written complex SP and are not able to proberly track exceptions due to this bug.
[12 Apr 2007 23:15] Thomas Rozario
Is there any news on this bug/enhancement? We are exploring the possibility of moving to MySql from Oracle and this is something we need to have.

[13 Nov 2007 13:19] Mikhail Serov
I completely subscribe to Roland's request
[9 Jan 2008 19:48] Daniel Bramblett
I'm shocked and dismayed that MySQL does not have the ability to capture error code/message from within a stored procedure.  I love MySQL so come on folks I'm cheering for you. However, when I go to the powers that be in my company and explain that our Oracle procedures, that have had so much invested in them, won't port over to MySQL due to weak error handling, that makes it a tough sell. I'm strongly encouraging you to put this bug on the fast track to resolution NOW as it has been on the shelf much too long. 

Kind regards, Daniel
[8 May 2008 15:04] Mohiuddin Khan Inamdar
I too desperately need this  feature...
just waiting .....

I hate workarounds... bcos they will later create more complications.
I agree 100% to Roland Bouman who filed this report.


[25 Jul 2008 23:50] Bret Elzinga
This improvement would be very helpful to our development team.
[3 Oct 2008 18:29] Joshua Butcher
There is no way my company can write serious stored procedures without this.  This is a severe lack of planning.
[4 Oct 2008 18:10] Konstantin Osipov
Bug#16371 HOW TO GET SQLSTATE was marked a duplicate of this bug.
[7 Nov 2008 22:23] Emery Fabrice NZEYIMANA
Any news about this?
[11 Mar 2009 15:10] Greg Fenton
Writing any serious SQL-based tools in MySQL is simply impossible without this.

There is no way to ensure that I can be aware of each specific SQLSTATE my procedures can result in.  A "catch all" handler is the only viable approach and yet there is no way to log the error/warning or communicate it to the user.

In our schema upgrade approach, we create a SQL file with a series of procedure calls:

create procedure upgrade(INOUT @err boolean)
declare exit handler for SQLEXCEPTION
    select true into @err;
    select 'Error upgrading from version 2, step 0';

  select 'Upgrade version "2", step 0';
  set @sql_query='alter table ...';
  prepare sql_stmt from @sql_query;
  execute sql_stmt;
  deallocate prepare sql_stmt;
call upgrade(@upgrade_error);

My current generated upgrade script is more than 200 of the above, plus a whack of conditional checks around each call.  When there is an error executing this with the MySQL client, it is impossible to figure out what went wrong.

[8 May 2009 16:59] Jeremiah Gowdy
This feature is critical.  Please prioritize.
[6 Jun 2009 9:18] Hadi Zeftin
signed up just to add another motivation (or pressure you may call it), yep this is a critical feature, most of the time I DON'T EXPECT error happen in my SP, but when it does I really need to know WHAT HAPPEN, 

sorry for lot of "I" and "need" word, apreciated all of your works on this.
[10 Jun 2009 8:22] Giles McArdell
I cannot believe this still has not been implemented, it is a serious flaw in the stored routines and is an issue I come up against every week.

It`s a catch 22 - you can either capture an error with a handler, but not know what it was, or not use the handler and not know where it happened or act upon it.

End of this month it will have been 4 years since this feature request first appeared.

Andrew Gilfrin seemed to think this was being dealt with in v5.2 (see http://forums.mysql.com/read.php?98,72762,72767#msg-72767) but now 5.2 has gone, 5.4 is here (which seems to be all about innodb), but still no fix for this.

Right now I am getting seriously concerned about the direction mysql is going in as, from where I`m sitting, it looks like user requests are getting less and less attention then they once did.
[18 Jun 2009 21:21] Steve Preskitt
Here's another voice asking that this be implemented.  Having the ability to implement an exception handler is of limited usefulness if there's no way to find out what abnormal condition you're trying to deal with.
[1 Jul 2009 11:11] Pol Alcori
And another one asking for this feature to be implemented... 

I think everyone has exposed why this is a critical need. I found the first topic on this to be on 2005, so come on folks, I am sure this is much more important than other secondary features being developed right now !!!
[24 Jul 2009 20:37] Rajeev Ranjan
Please do something for this. Even provide us the workaround. As i puzzled. I written a code to handle the exception. Now, if i am unable to know what kind of exception came, how will i remove it.

I cant use static error message like SELECT 'SQL WARNING ERROR';.

Help plzzzzz.



CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Test`()

    DECLARE err_key INT DEFAULT 0;




        INSERT INTO T111
        SELECT Source_ID_In, Master_Company_Name_in
        FROM htn.jigsaw_temp limit 1,10;

        INSERT INTO T1
        SELECT Source_ID_In, Master_Company_Name_in
        FROM htn.jigsaw_temp limit 11,30;

        INSERT INTO T1
        SELECT Source_ID_In, Master_Company_Name_in
        FROM htn.jigsaw_temp limit 31,40;

    IF err_key = 1 THEN
         SELECT perror;
         DROP TABLE T1;
         DROP TABLE T2;
         leave body;
    END IF;

    IF err_key = 2 THEN
         DROP TABLE T1;
         DROP TABLE T2;
         leave body;
    END IF;

END $$

[19 Apr 2010 17:58] Kartik R
any updates on these? This remains unresolved since 2005 Jun! Another 2 months folks and it would be 5 years. Definitely feel like I made the wrong decision to switch to MySQL a couple of years ago!
[27 Jun 2010 4:43] Szabolcs Rugina
Please Implement This feature!
This is very important!
[13 Jul 2010 19:06] SUBRAHMANYA KARUMURI
Surprised to see itsnot been implemented for so long .. Any workarounds people are surviving with ?.

This is much needed
[19 Aug 2010 2:18] Jacob Allen
For those of us migrating from Sybase or MSSQL, particularly those of us who do most of our programming using stored procedures, this feature is critical.  

I am really shocked that this simple and important feature is not available after 5 years of waiting.

I hope it can make it past triage soon...
[19 Aug 2010 2:26] Jacob Allen
For anyone looking for workarounds...

[19 Aug 2010 14:01] Greg Fenton
Thanks for the workaround link Jacob.  But the concluding sentence of that article is exactly why this bug exists:

  "Until MySQL implements a SQLSTATE or SQLSTATE variable, avoid creating a general-purpose SQLEXCEPTION handler."

For many of us, it is just unfathomable to create a handler for every possible error condition our stored procedures might run into.  And that approach is also impossible to test and to maintain.
[27 Sep 2010 20:43] Иван Бишевац
I can't believe mysql don't support this. It is very important in applications.
[6 Oct 2010 19:43] Richard Lee
Its funny that the two biggest omissions in MySQL (at least in my view - given over 2 decades of professional RDBMS programming) both have long list of support over many years, and both are completely ignored - these being this one (generic error handling within a SPROC) and the so-called "documented limitation" of not having the ability to set current-date or getdate() on na DateTime column default (not talking about the ability to add current-timestamp on a single TS column). Why is no one listening?
[7 May 2011 7:30] shiva Kumar
any latest news update mysql sqlstate error
[7 May 2011 7:43] MySQL Verification Team
at least in 5.5 we have this already:
[7 May 2011 19:48] Pavel Pushkarev
I don't think signal/resignal can do anything with this bug. The only thing they can be used for is exporting error from stored routines.

The bug is different: we need to catch any error (possible) and store the sqlstate in the error table for later reference (impossible). With resignal we can propagate the error correctly, but we still cannot store it.
[7 May 2011 22:36] Roland Bouman
Shane, Pavel is right. This bug is not solved with SIGNAL/RESIGNAL, at least not until MySQL also implements the diagnostics feature. The current SIGNAL/RESIGNAL implementation does solve http://bugs.mysql.com/bug.php?id=11661
[12 Jul 2011 12:27] rudi vos
Any news as to when/If this will be implemented.

Another example of where this would be very helpful, with the following in stored proc Insert into mydata
	Set serialno = mA,
            pinNo = mB

I would like to know if this insert fails (due to unique indexes on serialno and pin no) which index caused the insert to fail.
[18 Jul 2011 22:57] James Robinson
Happy 6th anniversary to this feature request.  The only work around that I have found for this were these posts by Claudio G:


It is appalling that the only method for this functionality requires thousands of declare handlers for something that is already exists in this product.  I would love for this feature to make it past verified.
[19 Jul 2011 14:30] Greg Fenton
Happy anniversary indeed!  Who is buying the donuts?

BTW: Claudio G's multi-post "solution" shows how ridiculous it is trying to solve this problem with the proposals put forward by others in this thread.  Thanks for the links.
[31 Jul 2011 20:28] rudi vos
Another example of where this would be very helpful, with the following in stored proc

Insert into mydata
	Set serialno = mA,
            pinNo = mB

I would like to know if this insert fails (due to unique indexes on serialno and pin no)
Which index caused the insert to fail?
[15 Sep 2011 12:44] Manuel Swiercz
OMG! I thought I just was too dumb to find it in the documentation. -.-
Such a big issue open for over 6 years?
Shame on you MySQL guys!

[18 Oct 2011 9:40] Marc ALFF
GET DIAGNOSTICS is now available in mysql-trunk, in 5.6

See http://marcalff.blogspot.com/2011/10/mysql-get-diagnostics.html

As a reminder, mysql-trunk is the current development branch, and is not for production use.
[18 Oct 2011 9:45] Roland Bouman
Marc, great news! Thanks :)
[17 Feb 2012 20:55] GRATIS GRATIS
yuxrnyi ckd atgrqs mpurg ivon vfa <a href="http://my.telegraph.co.uk/robertjohnsons/robertjohnsons/1/hello-world/">gratis</a> odhhdix gja yofvwe flzgo ahmm fov     
http://newlevelstudios.8k.com d
[19 Jul 2012 13:34] Marc ALFF
As noted earlier, GET DIAGNOSTICS is now implemented in MySQL 5.6