处理程序中的MySQL回滚 [英] 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;

但是以这种方式，当我调用此存储过程时，如果发生任何错误，则该存储过程会成功，并且我不知道实际的问题是什么.我希望客户端(php)记录错误以便对其进行故障排除.所以我以这种方式修改:

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;

因此，现在存储过程回滚，然后抛出处理程序拦截的异常.太酷了，但有时MESSAGE_TEXT超过128个字符，在这种情况下，我得到:

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:

错误代码:1648.条件项目"MESSAGE_TEXT"的数据过长

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

推荐答案

肯尼(Kenney)建议，答案是:

As suggested by Kenney the answer is:

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

这篇关于处理程序中的MySQL回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！