| 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: | |
| Category: | MySQL Server | Severity: | S4 (Feature request) |
| Version: | 5.0.7 | OS: | Any (NA) |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[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]
Valeriy 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]
Valeriy Kravchuk
This feature will be implemented in 5.2, almost surely.
[4 Jul 2006 6:42]
Valeriy 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)
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.