问题描述
我有一个存储过程,如果有任何问题,我想回滚.为了做到这一点,我使用了这样的退出处理程序:
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:
当然不能接受这种解决方案:
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回滚的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!