Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25712

Foreign key error 1761 not caught by stored routine exception handler

    XMLWordPrintable

    Details

      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'
      

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.