MySQL Bugs: #11660: Expose either SQLState, mysql_error() or other diagnostics in stored procedures

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:	None
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

Description:
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:
NA

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 (
        code
    ,   message
    ) values (
        @@SQLSTATE
   ,    @@SQLSTATE_MESSAGE
    )

It´s absolutly neccessary, see http://forums.mysql.com/read.php?98,31753,31753#msg-31753

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!

Really, the return errors is necessary for complete control of application.
Urgent!

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.

Thx,
R

Bug #16371 was marked as a duplicate of this one.

Bug #10797 was marked as a duplicate of this one.

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.

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!

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.

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.

Thx
Thomas.

I completely subscribe to Roland's request

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

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.

thanks...

Regards
mohnkhan
http://www.mohitech.com

This improvement would be very helpful to our development team.

There is no way my company can write serious stored procedures without this.  This is a severe lack of planning.

Bug#16371 HOW TO GET SQLSTATE was marked a duplicate of this bug.

Any news about this?

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)
upgrade_main:begin
declare exit handler for SQLEXCEPTION
  begin
    select true into @err;
    select 'Error upgrading from version 2, step 0';
  end;

  select 'Upgrade version "2", step 0';
  set @sql_query='alter table ...';
  prepare sql_stmt from @sql_query;
  execute sql_stmt;
  deallocate prepare sql_stmt;
end;
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.

Thanks,
greg.fenton

This feature is critical.  Please prioritize.

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.
thanks.

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.

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.

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 !!!

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.

**************************************************************************

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_Test` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_Test`()
body:
BEGIN

    DECLARE err_key INT DEFAULT 0;

    CREATE TEMPORARY TABLE T1 (ID INT, Name VARCHAR(250));
    CREATE TEMPORARY TABLE T2 (ID INT, Name VARCHAR(250));

    SET AUTOCOMMIT=0;
    START TRANSACTION;

    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION SET err_key=1;
        DECLARE EXIT HANDLER FOR SQLWARNING SET err_key=2;

        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;
    END;

    IF err_key = 1 THEN
         ROLLBACK;
         SELECT 'SQL EXCEPTION ERROR';
         SELECT perror;
         DROP TABLE T1;
         DROP TABLE T2;
         leave body;
    END IF;

    IF err_key = 2 THEN
         ROLLBACK;
         SELECT 'SQL WARNING ERROR';
         DROP TABLE T1;
         DROP TABLE T2;
         leave body;
    END IF;

    SELECT * FROM T1;
    DROP TABLE T1;
    DROP TABLE T2;
    COMMIT;
END $$

DELIMITER ;

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!

Please Implement This feature!
This is very important!

Surprised to see itsnot been implemented for so long .. Any workarounds people are surviving with ?.

This is much needed

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...

For anyone looking for workarounds...

http://www.devshed.com/c/a/MySQL/Error-Handling-Examples/

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.

I can't believe mysql don't support this. It is very important in applications.

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?

any latest news update mysql sqlstate error

at least in 5.5 we have this already:
http://dev.mysql.com/doc/refman/5.5/en/signal-resignal.html

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.

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

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.

Happy 6th anniversary to this feature request.  The only work around that I have found for this were these posts by Claudio G:

http://forums.mysql.com/read.php?98,80946,81341#msg-81341
http://forums.mysql.com/read.php?98,80946,81343#msg-81343

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.

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.

Another example of where this would be very helpful, with the following in stored proc

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
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?

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!

Best,
Manuel

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.

Marc, great news! Thanks :)

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

As noted earlier, GET DIAGNOSTICS is now implemented in MySQL 5.6