Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.1.48, 10.2.38, 10.3.29, 10.4.19, 10.5.10, 10.6.0, 10.2, 10.3, 10.4, 10.5
Description
Error code 1761 "Foreign key constraint ... would lead to a duplicate entry" can't be caught within a stored routines exception handler.
How to reproduce:
DROP TABLE IF EXISTS t2;
|
DROP TABLE IF EXISTS t1;
|
|
|
CREATE TABLE t1 ( id serial, f int, msg varchar(100), index(f));
|
|
|
CREATE TABLE t2 ( id serial, f int, unique(f), constraint `f` foreign key (f) references t1 (f) ON UPDATE CASCADE);
|
|
|
insert into t1 values(1, 23, 'abc');
|
insert into t1 values(2, 42, 'xyz');
|
|
|
INSERT INTO t2 VALUES(1, 23);
|
INSERT INTO t2 VALUES(2, 42);
|
|
|
DROP PROCEDURE IF EXISTS p1;
|
|
|
DELIMITER //
|
|
|
CREATE PROCEDURE p1()
|
BEGIN
|
DECLARE exit HANDLER FOR SQLEXCEPTION
|
BEGIN
|
SELECT 42;
|
END;
|
UPDATE t1 SET f=23 WHERE f=42;
|
END;
|
|
|
//
|
|
|
DELIMITER ;
|
|
|
CALL p1();
|
Expected result, due to the error handler kicking in:
+----+
|
| 42 |
|
+----+
|
| 42 |
|
+----+
|
1 row in set (0.00 sec)
|
Actual result:
ERROR 1761 (23000): Foreign key constraint for table 't1', record '2' would lead to a duplicate entry in table 't2', key 'f'
|