Bug #11661 Raising Exceptions from within stored procedures: Support for SIGNAL statement
Submitted: 30 Jun 2005 12:10 Modified: 12 Jul 2011 9:01
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.7 OS:Any (NA)
Assigned to: Marc Alff
Triage: Triaged: D5 (Feature request)

[30 Jun 2005 12:10] Roland Bouman
Description:
The stored procedure language of mysql support handling error conditions. It does not as of yet support raising ("throwing") custom or user defined error conditions ("exceptions"). 
In case some logical error is encountered, one could of course exit a procedure and leave the caller to discover wheter the procedure completed sucessfully. The disadvantage to this approach is that each procedure has to set up it's own interface to communicate it's error info to it's callers. Furthermore, it blurs the distinction between normal, productive code and supportive, assertive code.

How to repeat:
NA

Suggested fix:
Support the SIGNAL statement as described in {ISO 9075:1999 - 4 / 16.2} or similar such as the oracle raise or RAISE_APPLICATION_ERROR() function.
[2 Nov 2005 8:34] Th. Schuett
It´s absolutely neccessary, please see here :
http://forums.mysql.com/read.php?98,31753,31753#msg-31753
[27 Feb 2006 12:52] Valerii Kravchuk
Thank you for a reasonable feature request. In fact, developers are already working on signals implementation.
[27 Feb 2006 17:24] 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!
[3 May 2006 16:26] Rick Robinson
What has happened to this item?  Is it still being worked?  Is it targeted for a 5.0.x release?  A 5.1.x release?

Thanks,
Rick
[3 Jul 2006 12:09] Rajiv Kumar
Can somebody please update the status of this bug? It is needed very urgently.

Thanks,
Rajiv
[4 Jul 2006 6:40] Valerii Kravchuk
This feature will be implemented in 5.2, almost surely.
[4 Jul 2006 6:42] Valerii Kravchuk
This feature will be implemented in 5.2, almost surely.
[6 Sep 2007 9:46] Parahat Melayev
We are migrating our DB from Postgres to MySQL. And I am trying to port a Postgres procedure to MySQL. I am using 5.0.45-community-nt MySQL Community Edition (GPL) on my development machine. In Postgres procedure there are special conditions which we must throw an exception to rollback. But it is not possible on MySQL. You already mentioned that it will be ready by version 5.2 but is there any change on this issue?
[14 Apr 2008 18:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45373

ChangeSet@1.2627, 2008-04-14 12:07:57-06:00, malff@lambda.hsd1.co.comcast.net. +14 -0
  WL#2110 (Stored Procedures: Implement SIGNAL)
  WL#2265 (Stored Procedures: Implement RESIGNAL)
  WL#2111 (Stored Procedures: Implement GET DIAGNOSTICS)
  Bug#11661 (Raising Exceptions from within stored procedures: Support for
    SIGNAL statement)
  
  Part I: Syntax
  
  This patch implements syntax parsing for the following statements:
  - SIGNAL
  - RESIGNAL
  - GET DIAGNOSTICS
  
  Runtime execution of these statements is not implemented in this patch,
  and will be posted separately.
[29 Apr 2008 23:34] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46212

ChangeSet@1.2637, 2008-04-29 17:34:19-06:00, malff@lambda.hsd1.co.comcast.net. +22 -0
  WL#2110 (Stored Procedures: Implement SIGNAL)
  WL#2265 (Stored Procedures: Implement RESIGNAL)
  WL#2111 (Stored Procedures: Implement GET DIAGNOSTICS)
  Bug#11661 (Raising Exceptions from within stored procedures: Support for
    SIGNAL statement)
  
  Intermadiate patch for the SIGNAL runtime,
  not ready for formal review.
[1 May 2008 17:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46266

ChangeSet@1.2638, 2008-05-01 11:34:42-06:00, malff@lambda.hsd1.co.comcast.net. +4 -0
  WL#2110 (Stored Procedures: Implement SIGNAL)
  WL#2265 (Stored Procedures: Implement RESIGNAL)
  WL#2111 (Stored Procedures: Implement GET DIAGNOSTICS)
  Bug#11661 (Raising Exceptions from within stored procedures: Support for
    SIGNAL statement)
  
  SIGNAL runtime implementation and tests, feature preview.
[8 May 2008 15:01] Mohiuddin Khan Inamdar
I hope so this Comes out.. i have been in some pain trying to do a workaround for this....
user defined function uhh!.
 people like me who are facing the same issue look here
http://rpbouman.blogspot.com/2005/11/using-udf-to-raise-errors-from-inside.html

Thanks 
regards
mohnkhan
http://www.mohitech.com
[30 Jul 2008 22:20] Marc Alff
WL#2110-part1

Attachment: WL#2110-part1.eml (message/rfc822, text), 29.93 KiB.

[30 Jul 2008 22:21] Marc Alff
WL#2110-part2

Attachment: WL#2110-part2.eml (message/rfc822, text), 66.63 KiB.

[30 Jul 2008 22:21] Marc Alff
WL#2110-part3

Attachment: WL#2110-part3.eml (message/rfc822, text), 8.28 KiB.

[30 Jul 2008 22:22] Marc Alff
WL#2110-part4

Attachment: WL#2110-part4.eml (message/rfc822, text), 13.24 KiB.

[30 Jul 2008 22:22] Marc Alff
WL#2110-part5

Attachment: WL#2110-part5.eml (message/rfc822, text), 19.04 KiB.

[30 Jul 2008 22:23] Marc Alff
WL#2110-part6

Attachment: WL#2110-part6.eml (message/rfc822, text), 6.60 KiB.

[30 Jul 2008 22:23] Marc Alff
WL#2110-part7

Attachment: WL#2110-part7.eml (message/rfc822, text), 5.57 KiB.

[30 Jul 2008 22:24] Marc Alff
WL#2110-part8

Attachment: WL#2110-part8.eml (message/rfc822, text), 12.79 KiB.

[30 Jul 2008 22:25] Marc Alff
WL#2110-part9

Attachment: WL#2110-part9.eml (message/rfc822, text), 5.44 KiB.

[30 Jul 2008 22:32] Marc Alff
WL#2110-part10 (in text format)

Attachment: WL#2110-part10.txt (text/plain), 160.39 KiB.

[30 Jul 2008 22:38] Marc Alff
The code for WL#2110 (SIGNAL) is currently under code review.

The complete patch has been broken into 10 parts, to facilitate the process.
The 10 parts have been send to the commit list already,
and are also added as file attachments to this bug report.
[27 Aug 2008 22:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52790

2698 Marc Alff	2008-08-27
      WL#2110 (SIGNAL)
      WL#2265 (RESIGNAL)
      Bug#11661 (Raising Exceptions from within stored procedures:
                 Support for SIGNAL statement)
[28 Aug 2008 20:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/52878

2698 Marc Alff	2008-08-27
      WL#2110 (SIGNAL)
      WL#2265 (RESIGNAL)
      Bug#11661 (Raising Exceptions from within stored procedures:
                 Support for SIGNAL statement)
[3 Oct 2008 21:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55293

2704 Marc Alff	2008-10-03
      WL#2110 (Stored Procedures: Implement SIGNAL)
      WL#2265 (Stored Procedures: Implement RESIGNAL)
      Bug#11661 (Raising Exceptions from within stored procedures: Support for
                 SIGNAL statement)
      
      Fixed review comments related to memory management.
      
      - Changed all the handle_condition() methods to return a SQL_condition
      as *output*, instead of *input*.
      The logic now find first where the condition should be written to,
      and then the caller writes the extra attributes in place.
      This change removes the need for SQL_condition::deep_copy().
      Also, when a condition is dropped (Internal handlers, warn_list full),
      this saves some CPU cycle.
      
      - Removed SQL_condition::deep_copy()
      
      - Removed sp_rcontext::m_cond_root, and reverted related changes.
      
      - Adjusted the code that raises a condition according to the new paradigm.
[14 Oct 2008 22:53] Eric Brunson
Committed to 6.0 review branch?  That's great for when it's released in 2012, but this feature request has been open for three and a half years.  I don't suppose there's any chance of getting it back ported to 5.1, is there?
[3 Mar 2009 19:07] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090303190023-o9njyvs6vxc3xw3r) (version source revid:marc.alff@sun.com-20081003213516-swzy6dto1i3tbhmc) (merge vers: 6.0.8-alpha) (pib:6)
[4 Mar 2009 18:31] Paul Dubois
Noted in 6.0.11 changelog.

MySQL now implements the SQL standard SIGNAL and RESIGNAL statements.

Documentation for SIGNAL/RESIGNAL:

http://dev.mysql.com/doc/refman/6.0/en/signal-resignal.html
[12 Nov 2009 19:48] Paul Dubois
Noted in 5.5.0 changelog.
[12 Jul 2011 8:55] 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.
[12 Jul 2011 9:01] Roland Bouman
Rudi, this bug is closed, beacause it has been fixed. However, I think you mean a different bug: http://bugs.mysql.com/bug.php?id=11660 - i.e. grabbing the error text. 

Although that bug is not yet fixed you can already declard a proper handler for unique key violations. Just declare an appropriate handler (using either the error code or sql state). You want to catch this:

" Error: 1169 SQLSTATE: 23000 (ER_DUP_UNIQUE)

Message: Can't write, because of unique constraint, to table '%s'"

(this will not give you the info about which key or unique constraint is violated though)