Search code examples
phpmysqlhandlersqlexception

MySQL rollback in handler


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


Solution

  • As suggested by Kenney the answer is:

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