Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL exception handler access exception being handled

I'm trying to rollback on an error, but still let the client receive the error. This might actually be impossible, unless there is a way to access the error in an exception handler.

It's possible to "throw" from an exception, i.e. it's possible to raise a signal:

CREATE PROCEDURE p ()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'An error occurred';
  END;
  DROP TABLE no_such_table;
END;

But this sample code from the MySQL doc looks horrible, because it literally swallows all errors and jams them into one.

SHOW ERRORS seems relevant, but I don't see any way to work with it programmatically, e.g. SELECT Code FROM (SHOW ERRORS); is not possible.

Is this possible? Is there a better practice that I'm missing entirely?

like image 526
djechlin Avatar asked Sep 17 '13 19:09

djechlin


People also ask

What to do if an exception happens in MySQL?

MySQL provides a handler to handle the exceptions in the stored procedures. You can handle these exceptions by declaring a handler using the MySQL DECLARE ... HANDLER Statement.

What is Sqlexception in MySQL?

For SQLEXCEPTION conditions, the stored program terminates at the statement that raised the condition, as if there were an EXIT handler. If the program was called by another stored program, the calling program handles the condition using the handler selection rules applied to its own handlers.

How does MySQL handle exceptions in Java?

When such an exception occurs, an object of type SQLException will be passed to the catch clause. Gets the error number associated with the exception. Gets the JDBC driver's error message for an error, handled by the driver or gets the Oracle error number and message for a database error.

What is the correct syntax to declare handler for the error in MySQL?

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET got_error = 1; In the above example, a handler means that if an error occurs then set the value of the got_error variable to 10 and continues the execution.


1 Answers

Looks like RESIGNAL is what you are looking for.

RESIGNAL makes it possible to both handle an error and return the error information. Otherwise, by executing an SQL statement within the handler, information that caused the handler's activation is destroyed. RESIGNAL also can make some procedures shorter if a given handler can handle part of a situation, then pass the condition “up the line” to another handler.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`resig` $$
CREATE PROCEDURE `test`.`resig` ()
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  SELECT 'I executed something before throwing the error' as `this_works`;
  RESIGNAL;
END;

SELECT foo FROM bar WHERE baz = 0;

END $$

DELIMITER ;


mysql> call resig();
+------------------------------------------------+
| this_works                                     |
+------------------------------------------------+
| I executed something before throwing the error |
+------------------------------------------------+
1 row in set (0.00 sec)

ERROR 1054 (42S22): Unknown column 'foo' in 'field list'

mysql>
like image 178
Michael - sqlbot Avatar answered Sep 28 '22 09:09

Michael - sqlbot