MySQL Forums
Forum List  »  Stored Procedures

Error handler not being triggered for multi-table updates
Posted by: Mark Ogilvie
Date: August 16, 2015 08:28PM

I have some stored procs that contain error handlers, but I have found that the handlers are not being activated for duplicate key errors that are caused by multi-table update statements. The same error handlers in the same procs are working for insert statements, and single-table updates.

Does anybody know why this would be happening?

I'm using MySQL 5.6.19 on AWS.

Below is a script that reproduces the behaviour.

Thanks
Mark O.
mark.ogilvie@crichq.com

-- ------------------------------------------------
drop table if exists test.t1;
CREATE TABLE test.t1 (s1 INT, PRIMARY KEY (s1));
Insert into test.t1 (s1) Values (1);
Insert into test.t1 (s1) Values (2);

drop table if exists test.t2;
create table test.t2 (s1 int, s2 int);
Insert into test.t2 (s1, s2) Values (1,2);

drop procedure if exists handlerdemo;
CREATE PROCEDURE handlerdemo ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' select 'hello';

-- This statement triggers the error handler
-- update test.t1 set s1 = 1 where s1 = 2;

-- This doesn't trigger the error handler
update
test.t1
inner join
test.t2
on t1.s1 = t2.s1
set t1.s1 = t2.s2
;
END;

CALL handlerdemo();

Options: ReplyQuote


Subject
Views
Written By
Posted
Error handler not being triggered for multi-table updates
2357
August 16, 2015 08:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.