MySQL rollback in handler

Question

I have a stored procedure where I'd like to rollback in case anything goes wrong. In order to do that I'm using a EXIT HANDLER like this:

DECLARE EXIT HANDLER FOR sqlexception 
    begin
        ROLLBACK;
    end;

But in this way, when I call this stored procedure, in case of any errors, the stored procedure succeed and I don't know what was the actual problem. I want the client (php) to log the error in order to troubleshoot it. So I modify in this way:

DECLARE EXIT HANDLER FOR sqlexception 
    begin
        get diagnostics condition 1
              @p1 = MESSAGE_TEXT;
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @p1;
    end;

So now the stored procedure rollback and than throw the exeption that the handler intercepted. That's cool but sometimes the MESSAGE_TEXT is more than 128 chars and in such cases I get:

Error Code: 1648. Data too long for condition item 'MESSAGE_TEXT'

Of course this solution is not acceptable:

DECLARE EXIT HANDLER FOR sqlexception 
    begin
        get diagnostics condition 1
              @p1 = MESSAGE_TEXT;
        ROLLBACK;
        SET @p1=SUBSTRING(@p1,1,128);
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @p1;
    end;

Is there any way to intercept any exception, rollback and then throw the same exception to the client? Thanks very much for your help

Utoaztecan

Where do you BEGIN the transaction? Also in a stored procedure, or in PHP? – Schwab

In the stored procedure. Of course also the commit is inside the stored procedure – Utoaztecan

Ok thanks. Perhaps RESIGNAL is what you're looking for? It should pass on the original error. – Schwab

It did the trick. Thanks so much, I missed that function on the docs. – Utoaztecan

Answer 1

As suggested by Kenney the answer is:

DECLARE EXIT HANDLER FOR sqlexception 
    begin
        ROLLBACK;
        RESIGNAL;
    end;

Utoaztecan

Answer 2

I use this template and it works for me. When exceptions are encountered, transaction flow immediately jumps to DECLARE EXIT HANDLER FOR SQLEXCEPTION. The last statement to be executed by MySQL engine would be ROLLBACK. After that, transaction flow moves out of the tblock

Handle the exceptions gracefully by returning the error codes and message as recordset (not throwing an error to PHP), which your PHP code can easily manipulate and display.

DELIMITER $$
CREATE PROCEDURE `ProcName`( <parameters here> )
BEGIN

    START TRANSACTION;

    tblock: BEGIN # start: transaction block

        /* catch any exceptions, then rollback */
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1
                @state = RETURNED_SQLSTATE, 
                @rtc    = MYSQL_ERRNO,
                @rmg    = MESSAGE_TEXT; -- MySQL 5.6 > : comment diagnostics for lower versions
            ROLLBACK;
        END;

        /* table transactions here */

        COMMIT;

    END tblock; # end: transaction block

    SELECT  @rtc AS retcode,
            @rmg AS retmsg,
            'some ret value' AS retval;

END$$
DELIMITER ;

In SQL, as standard, 0 is success and non-zero (usually greater than zero) values suggest error. You may want to follow such and make it a practice.

Alienee