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

Self-referencing table produces "Got error 193" from InnoDB on cascading DELETE

Details

    Description

      CREATE TABLE t (c1 INT KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t (c1) ON DELETE CASCADE) ENGINE=InnoDB;
      		 
      INSERT INTO t VALUES (0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
      		 
      DELETE FROM t;

      Leads to:

      11.5.0 e4afa610539ae01164485554e2de839bea9de816 (Debug)

      		 
      11.5.0-dbg>CREATE TABLE t (c1 INT KEY,c2 INT,FOREIGN KEY(c2) REFERENCES t (c1) ON DELETE CASCADE) ENGINE=InnoDB;
      		 
      Query OK, 0 rows affected (0.012 sec)
      		 
       
      		 
      11.5.0-dbg>INSERT INTO t VALUES (0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6),(8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14);
      		 
      Query OK, 16 rows affected (0.004 sec)
      		 
      Records: 16  Duplicates: 0  Warnings: 0
      		 
       
      		 
      11.5.0-dbg>DELETE FROM t;
      		 
      ERROR 1296 (HY000): Got error 193 '`test`.`t`, CONSTRAINT `t_ibfk_1` FOREIGN KEY (`c2`) REFERENCES `t` (`c1`) ON DELETE CASCADE' from InnoDB

      11.5.0 e4afa610539ae01164485554e2de839bea9de816 (Debug)

      		 
      2024-06-15 14:57:23 4 [ERROR] InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 15. Please drop excessive foreign constraints and try again

      If the "max depth of 15" self-referencing error is not considered to be a bug, then we can improve the CLI error.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15199 Triangular FKs - Cascade delete causes broken referential integrity

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22361 Cross-engine foreign keys support

          • Major - Major loss of function.
          • Open

          Activity

            marko Marko Mäkelä added a comment -

            The reason for the limit is that FOREIGN KEY constraints are being enforced using procedural recursion. There had been an attempt to replace this with an explicit heap-allocated recursion stack, but in MDEV-15199 that change was reverted due to correctness regressions. According to my commit message from back then, one iteration of the recursion would consume more than 8 KiB of stack (in 9 stack frames) in a non-debug AMD64 build.

            I think that this needs to be revisited as part of implementing FOREIGN KEY support across all storage engines, in MDEV-22361.

            marko Marko Mäkelä added a comment - The reason for the limit is that FOREIGN KEY constraints are being enforced using procedural recursion. There had been an attempt to replace this with an explicit heap-allocated recursion stack, but in MDEV-15199 that change was reverted due to correctness regressions. According to my commit message from back then , one iteration of the recursion would consume more than 8 KiB of stack (in 9 stack frames) in a non-debug AMD64 build. I think that this needs to be revisited as part of implementing FOREIGN KEY support across all storage engines, in MDEV-22361 .

            People

              marko Marko Mäkelä
              Roel Roel Van de Paar
              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.