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

InnoDB uses different constants for FK cascade error message in SQL vs error log

    XMLWordPrintable

    Details

      Description

      --source include/have_innodb.inc
       
      CREATE TABLE t1 (
          a INT, b INT, KEY(b),
          CONSTRAINT FOREIGN KEY (a) REFERENCES t1 (b) ON DELETE CASCADE
      ) ENGINE=InnoDB;
      INSERT INTO t1 (a,b) VALUES
      (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
      (0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,1),(1,0);
      --error ER_GET_ERRMSG
      DELETE FROM t1 WHERE b = 1;
      SHOW WARNINGS;
       
      DROP TABLE t1;
      

      The message returned to the query uses DICT_FK_MAX_RECURSIVE_LOAD (20):

      10.2 5d57e04b

      DELETE FROM t1 WHERE b = 1;
      ERROR HY000: Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
      SHOW WARNINGS;
      Level	Code	Message
      Warning	152	InnoDB: Cannot delete/update rows with cascading foreign key constraints that exceed max depth of 20. Please drop extra constraints and try again
      Error	1296	Got error 193 '`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t1` (`b`) ON DELETE CASCADE' from InnoDB
      

      The message written in the error log uses FK_MAX_CASCADE_DEL (15):

      2022-01-23  2:38:51 140072887355136 [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
      

      The latter seems correct.

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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