mysql handler 回滚,处理程序中的MySQL回滚

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

解决方案

As suggested by Kenney the answer is:

DECLARE EXIT HANDLER FOR sqlexception

begin

ROLLBACK;

RESIGNAL;

end;